Гиперссылка (hyperlink) поисковые функции

ГИПЕРССЫЛКА (HYPERLINK) Поисковые функции

Создает гиперссылку в ячейке.

Пример использования

ГИПЕРССЫЛКА("http://www.google.com/"; "Google")

Синтаксис

ГИПЕРССЫЛКА(ссылка; [link_label])

· ссылка – полный URL-адрес, заключенный в кавычки, или адрес ячейки, содержащей URL-адрес.

· Разрешены только некоторые типы протоколов: http://, https://, mailto:, aim:, ftp://, gopher://, telnet:// и news://. Остальные виды ссылок запрещены к использованию. При использовании ссылки с другим протоколом текст_ссылки будет отображаться в ячейке, но не будет выделяться как гипертекст.

· Если тип протокола не указан, предполагается, что это http://, и ссылка дополняется соответствующим образом.

· текст_ссылки – [ НЕОБЯЗАТЕЛЬНО – ссылка по умолчанию ] – заключенный в кавычки текст, отображаемый в ячейке в виде гиперссылки, или ссылка на ячейку, содержащую такой текст.

· Если текст_ссылки является ссылкой на пустую ячейку, ссылка будет отображаться в виде гипертекстового URL-адреса, если он допустим, или в виде обычного текста в противном случае.

· Если текст_ссылки является пустой строкой (""), ячейка будет отображаться как пустая. В этому случае ссылка все равно остается действующей. Для активации ссылки нужно щелкнуть по ячейке или перейти в нее.

· Примечания

· В случае, если ссылка (или текст_ссылки, если он указан) не заключены в кавычки, будет возвращено сообщение об ошибке.

· Google Таблицы автоматически преобразуют большинство известных типов URL-адресов при вводе их в ячейку, без обращения к этой функции.

МАКС (MAX) Статистические функции

Возвращает максимальное значение в наборе чисел.

Пример использования

МАКС(A2:A100; B2:B100; 4; 26)

МАКС(1; 2; 3; 4; 5; C6:C20)

Синтаксис

МАКС(значение1; [значение2;...])

· значение1 – первое значение или диапазон для вычисления максимального значения.

· значение2 – [ НЕОБЯЗАТЕЛЬНО ] – дополнительные значения или диапазоны для вычисления максимального значения.

· Примечания

· Несмотря на то, что по спецификации функция МАКС не принимает более 30 аргументов, Google Таблицы позволяют передавать ей произвольное количество аргументов.

· Каждое из передаваемых функции значений должно быть числом или диапазоном, содержащим числа. При передаче в функцию МАКС любых текстовых аргументов будет возвращено сообщение об ошибке #ЗНАЧЕН!. Для обработки текстовых значений используйте функцию МАКСА.

ДАТА (DATE)

Преобразует введенные год, месяц и день в дату.

Пример использования

ДАТА(1969; 7; 20)

ДАТА(A2; B2; C2)

Синтаксис

ДАТА(год; месяц; день)

· год – компонент года в дате.

· месяц – компонент месяца в дате.

· день – компонент дня в дате.

· Примечания

· В качестве входных данных для функции ДАТА должны использоваться числа. При передаче в функцию строки или ссылки на ячейку, содержащую строку, будет возвращена ошибка #ЗНАЧЕН!.

· ДАТА автоматически пересчитывает числовые данные, выходящие за пределы допустимых значений для дней и месяцев. Например, ДАТА(1969; 13; 1) вернет значение 1/1/1970, потому что в функцию передается неправильный номер месяца (13). Аналогичным образом, ДАТА(1969; 1; 32) вернет дату 2/1/1969, поскольку в январе не существует 32-го дня.

· ДАТА автоматически усекает десятичную часть передаваемых в нее параметров. Так, месяц 12,75 будет интерпретироваться как 12.

· Примеры

ДАТА принимает в качестве входных данных как строчные параметры, так и ссылки на ячейки. Кроме того, она автоматически пересчитывает данные, выходящие за рамки допустимых значений дней и месяцев.

Список доступных команд

Перечень команд, которые можно добавить в панель быстрого доступа и ленту меню. Добавления команды в список текущих команд настраиваемого объекта можно выполнить несколькими способами:

▪выберите команду одним нажатием левой кнопкой мыши и нажмите кнопку [Добавить]

▪выполните двойное нажатие левой кнопкой мыши по ее имени в списке

▪удерживая левую кнопку мыши перетащите выбранную команду в список справа в соответствующую группу

Поддерживается быстрый поиск команд по первой букве, для этого одним нажатием левой кнопкой мыши выберите любую команду в списке и с клавиатуры введите букву, на которую начинается название искомой команды.

Тип кнопки

Задает внешний вид кнопки в ленте меню. Для панели быстрого доступа данная настройка не работает. Кнопка в ленте меню может принимать три вида:

▪ Большая. Иконка выбранной команды в ленте меню будет показана с размерами 32х32px. Под иконкой добавляется ее название.

▪ Без текста. Иконка выбранной команды в ленте меню будет показана с размерами 16х16px без текстовой подписи. По умолчанию установлен для всех добавляемых команд.

▪ С текстом. Иконка выбранной команды в ленте меню будет показана с размерами 16х16px. Справа от иконки добавляется ее название.

По умолчанию все команды, добавляемые в ленту меню, будут показываться в виде маленьких иконок без текстовой подписи. Изменение типа кнопки выполняется после ее добавления. Чтобы изменить тип кнопки в списке текущих команд ленты меню (список справа) выберите команду и задайте ей требуемый вид.

Добавить

Добавляет команду, выбранную в общем списке команд, в список команд редактируемой вкладки ленты меню или панели быстрого доступа. Для добавления команды в список текущих команд следует выбрать группу, в которую будет добавлена команда.

Удалить

Удаляет выбранную команду, группу или вкладку из списка текущих команд ленты меню или панели быстрого доступа.

Переименовать

Позволяет переименовать пользовательскую группу или вкладку ленты меню. Одно нажатие левой кнопкой мыши по выделенному имени пользовательской вкладки или группы делает имя активным для редактирования.

По умолчанию

Выполняет общий сброс пользовательских изменений для ленты меню и панели быстрого доступа до настроек по умолчанию.

Настраиваемый элемент

В выпадающем списке в правом верхнем углу диалогового окна выбирается объект для настройки: Лента меню или Панель быстрого доступа. В зависимости от выбранного объекта в список ниже загружаются соответствующие команды. Если настраивается лента меню, то показывается иерархия Вкладка-Группа-Команда. Если настраивается панель быстрого доступа, то загружается список команд, которые расположены на ней.

Список текущих команд

В списке, расположенном справа, показываются текущие команды редактируемого объекта (Ленты меню или Панели быстрого доступа). Список текущих команд Панели быстрого доступа не имеет иерархии и содержит только команды. Список команд Ленты меню показывается в виде дерева объектов и имеет структуру Вкладка-Группа-Команда. Добавление команды в ленту меню возможно только в том случае, если на вкладке имеется хотя бы одна группа. Перемещение вкладок, групп и команд производится при помощи нажатой левой кнопки мыши.

Добавить вкладку

Добавляет новую вкладку в ленту меню с именем NewPage№. Добавление выполняется в конец списка. Автоматически при добавлении новой вкладки в ней создается новая группа с именем NewToolBar. Для изменения имени вкладки выберите вкладку и нажмите кнопку [Переименовать] или выполните одно нажатие левой кнопкой мыши по выделенному имени вкладки.

Добавить группу

Добавляет новую группу (или панель) с именем NewToolBar в редактируемую вкладку. Добавление группы происходит в конец списка текущей вкладки. Удерживая левую кнопку мыши можно перемещать группы с одной вкладки на другую изменяя порядок следования.

Показывать вкладку

Позволяет запретить показ выбранной вкладки в ленте меню. При этом она не удаляется и в любой момент можно включить ее видимость.

Примечание: вкладки и группы, которые добавлены в программе по умолчанию, нельзя изменить или удалить, но можно выключить видимость всей вкладки при помощи команды Показывать вкладку.

26. Понятие относительная адресация ячеек.

Для вычислений в формулах используют различные виды адресации.

Адрес ячейки, изменяющийся при копировании формулы называется относительным. Он имеет вид В1, А3. По умолчанию программа Excel рассматривает адреса ячеек как относительные.
Относительная адресация ячеек – обычное явление при вычислениях по формулам. При копировании такой формулы вправо или влево будет изменяться заголовок столбца в имени ячейки, а при копировании вверх или вниз – номер строки.

27. Привести примеры с использованием функций ВПР, МАКС, МИН.

ВПР (VLOOKUP)

Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.

Примеры использования

ВПР(10003; A2:B26; 2; FALSE)

Синтаксис

ВПР(запрос; диапазон; индекс; [отсортировано])

· запрос – критерий, по которому выполняется поиск (например, 42, кошка или I24).

· диапазон – диапазон, в первом столбце которого будет производиться поиск по запросу.

· индекс – номер столбца (от начала диапазона), из которого нужно взять искомое значение.

· Если индекс не попадает в интервал от 1 до числа столбцов в диапазоне, возвращается ошибка #VALUE!.

· отсортировано – по умолчанию [ ИСТИНА ]. Указывает, отсортированы ли данные в столбце, в котором производится поиск (первом столбце из указанного диапазона). Как правило, рекомендуемое значение – ЛОЖЬ.

· Если для параметра отсортировано указать значение ЛОЖЬ (рекомендуется), возвращается только точное совпадение. Если таких совпадений несколько, возвращается значение для первого из них. Если точных совпадений нет, возвращается ошибка #Н/Д.

· Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА, возвращается значение, ближайшее к запрошенному (меньшее либо равное). Если все значения в столбце поиска больше указанного, возвращается ошибка #Н/Д.

· Примечания

· Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА, а первый столбец диапазона не отсортирован, функция может вернуть неверное значение. Если результаты ВПР вызывают сомнение, убедитесь, что для последнего параметра указано значение ЛОЖЬ. Такая конфигурация подходит для большинства случаев. Если же данные в столбце отсортированы и вы хотите оптимизировать поиск, укажите значение ИСТИНА.

· При поиске чисел или дат убедитесь, что первый столбец в диапазоне не отсортирован по текстовым значениям. К примеру, правильно отсортированные числа должны располагаться в порядке (1; 2; 10; 100). Если их отсортировать как текст, порядок будет иным: (1; 10; 100; 2). При неверном типе сортировки функция может вернуть неправильное значение.

· Запросы на основе регулярных выражений НЕ поддерживаются. В этих случаях нужно использовать функцию ЗАПРОС (QUERY).

· ВПР работает быстрее, если данные в диапазоне отсортированы и для параметра отсортировано указано значение ИСТИНА.

· Также для поиска можно использовать шаблоны с подстановочными знаками. Знаки "?" и "*" подставляются в запрос. При этом знак вопроса обозначает один символ, а звездочка – набор символов. Если вы хотите найти вопросительный знак или звездочку в тексте, введите перед искомым символом тильду (~), чтобы указать, что это не подстановочный знак. А если нужно найти тильду, введите ещё одну.

Похожие функции

ЗАПРОС (QUERY): Выполняет запросы на базе языка запросов API визуализации Google.

ГПР (HLOOKUP): Производит поиск по первой строке диапазона и возвращает значение из найденной ячейки.

Примеры

ВПР ищет в первом столбце номер студента и возвращает соответствующую оценку.

МАКС (MAX) Статистические функции

Возвращает максимальное значение в наборе чисел.

Пример использования

МАКС(A2:A100; B2:B100; 4; 26)

МАКС(1; 2; 3; 4; 5; C6:C20)

Синтаксис

МАКС(значение1; [значение2;...])

· значение1 – первое значение или диапазон для вычисления максимального значения.

· значение2 – [ НЕОБЯЗАТЕЛЬНО ] – дополнительные значения или диапазоны для вычисления максимального значения.

· Примечания

· Несмотря на то, что по спецификации функция МАКС не принимает более 30 аргументов, Google Таблицы позволяют передавать ей произвольное количество аргументов.

· Каждое из передаваемых функции значений должно быть числом или диапазоном, содержащим числа. При передаче в функцию МАКС любых текстовых аргументов будет возвращено сообщение об ошибке #ЗНАЧЕН!. Для обработки текстовых значений используйте функцию МАКСА.

МИН (MIN)

Возвращает минимальное значение в наборе чисел.

Пример использования

МИН(A2:A100; B2:B100; 4; 26)

МИН(1; 2; 3; 4; 5; C6:C20)

Синтаксис

МИН(значение1; [значение2;...])

· значение1 – первое значение или диапазон для вычисления минимального значения.

· значение2 – [ НЕОБЯЗАТЕЛЬНО ] – дополнительные значения или диапазоны для вычисления минимального значения.

· Примечания

· Несмотря на то, что по спецификации функция МИН не принимает более 30 аргументов, Google Таблицы позволяют передавать ей произвольное количество аргументов.

· Каждое из передаваемых функции значений должно быть числом или диапазоном, содержащим числа. При передаче в функцию МИН любых текстовых аргументов будет возвращено сообщение об ошибке #ЗНАЧЕН!. Для обработки текстовых значений используйте функцию МИНА.

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: = ВПР (ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

• ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
• диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
• номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
• интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
• ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
• ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

Важно не путать, что номер столбца указывается не по индексу на листе, а по порядку в указанном диапазоне.

Пример использования:

На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.
В первой таблице приведены категории товара и расположение каждой категории.
Во второй категории имеется список всех товаров с указанием цен.
Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(" ";C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и? (один любой символ). Например, для искомого значения "*" & "иван" & "*" могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;"Два":1;"Один"};2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

Так как функция аналогична функции ВПР, за исключением того, что использует горизонтальные таблицы вместо вертикальных, то описания и примеры использования подходят и для ГПР с учетом упомянутых различий.

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: = СТРОКА ([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

= СТРОКА (D4) – результат 4.
= СТРОКА () – функция вернет номер строки, в которой она расположена.

Функция СТОЛБЕЦ

Возвращает номер столбца ячейки, указанной ссылкой.

Синтаксис: = СТОЛБЕЦ ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.

Пример использования:

= СТОЛБЕЦ (C4) – формула вернет значение 3.
= СТОЛБЕЦ () – функция возвращает номер столбца, в котором расположена.

Функция АДРЕС

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: = АДРЕС (строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

• Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
• Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
• тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
• 1 – значение по умолчанию, когда закреплены все индексы;
• 2 – закрепление индекса строки;
• 3 – закрепление индекса столбца;
• 4 – адрес без закреплений.
• стиль_ссылки – необязательный аргумент. Логическое значение:
• ИСТИНА – формат ссылок «A1»;
• ЛОЖЬ – формат ссылок «R1C1».
• имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

= АДРЕС (1;1) – возвращает $A$1.
= АДРЕС (1;1;4) – возвращает A1.
= АДРЕС (1;1;4;ЛОЖЬ) – результат R[1]C[1].
= АДРЕС (1;1;4;ЛОЖЬ;"Лист1") – результат выполнения функции Лист1!R[1]C[1].

Функция ДВССЫЛ

Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.

Синтаксис: = ДВССЫЛ (адрес_ссылки; [стиль_ссылки]), где

• адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, "C3", "R3C3" или "D8:D9".
• стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
• ИСТИНА – стиль A1. Является значением по умолчанию;
• ЛОЖЬ – стиль R1C1.

Пример использования:

= ДВССЫЛ ("a3") – возвращает ссылку на ячейку A3.
= ДВССЫЛ ("r3c3") – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
= ДВССЫЛ ("r3c3"; ЛОЖЬ) – возвращает ссылку на ячейку C3.
= ДВССЫЛ (АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:

Функция ЧСТРОК

Возвращает число строк в указанном диапазоне или массиве.

Синтаксис: = ЧСТРОК (ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.

Пример использования:
= ЧСТРОК (D1:D8) – функция возвращает результат 8.
= ЧСТРОК ({1:2:3:4:5}) – функция определят, что в массиве 5 строк.

Функция ЧИСЛСТОЛБ

Возвращает число столбцов в указанном диапазоне или массиве.
Синтаксис: = ЧИСЛСТОЛБ (ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
Пример использования:
= ЧИСЛСТОЛБ (A5:D5) – результат функции 4.
= ЧИСЛСТОЛБ ({1;2;3;4;5}) – функция определят, что в массиве 5 столбцов.

Функция СМЕЩ

Смещает ссылку на диапазон на указанное количество строк и столбцов, а также позволяет изменить его размерность.
Функция не передвигает и не изменяет самих ячеек, а только подменяет саму ссылку.

Синтаксис: = СМЕЩ (ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]), где

• ссылка – обязательный аргумент. Ссылка на ячейку либо диапазон, относительно которого необходимо произвести смещение.
• смещ_по_строкам – обязательный аргумент. Отрицательное либо положительное число, указывающее, на какое количество строк необходимо сместить диапазон.
• смещ_по_столбцам – обязательный аргумент. Отрицательное либо положительное число, указывающее, на какое количество столбцов необходимо сместить диапазон.
• высота – необязательный аргумент. Натуральное число (кроме нуля), определяющее количество строк в новом (смещенном) диапазоне.
• ширина – необязательный аргумент. Натуральное число (кроме нуля), определяющее количество столбцов в новом диапазоне.

Если в результате смещения диапазон стал выходить на пределы листа, то функция возвращает ошибку #ССЫЛКА!.

Пример использования:

Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.

Теперь сместим диапазон на один столбец влево, т.е. на -1.

Снова изменим ссылку, а именно расширим до 4 столбцов. После этого возвращаемая ссылка станет A3:D12. Результат на изображении.

Функция ПОИСКПОЗ

Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

Синтаксис: = ПОИСКПОЗ (искомое_значение; массив; [тип_сопоставления]), где:

• искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
• Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
• тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
• 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
• 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
• -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

Пример использования:
= ПОИСКПОЗ ("Г"; {"а";"б";"в";"г";"д"}) – функция возвращает результат 4. При этом регистр не учитывается.
= ПОИСКПОЗ ("е"; {"а";"б";"в";"г";"д"}; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
= ПОИСКПОЗ ("е"; {"а";"б";"в";"г";"д"}; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.
= ПОИСКПОЗ ("в"; {"д";"г";"в";"б";"а"}; -1) – результат 3.
= ПОИСКПОЗ ("д"; {"а";"б";"в";"г";"д"}; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

Для текстовых значений функция допускает использование подстановочных символов «*» и «?».

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: = ВПР (ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

• ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
• диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
• номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
• интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
• ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
• ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

Важно не путать, что номер столбца указывается не по индексу на листе, а по порядку в указанном диапазоне.

Пример использования:

На изображении приведено 3 таблицы. Первая и вторая таблицы располагают исходными данными. Третья таблица собрана из первых двух.
В первой таблице приведены категории товара и расположение каждой категории.
Во второй категории имеется список всех товаров с указанием цен.
Третья таблица содержать часть товаров для которых необходимо определить цену и расположение.

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(" ";C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и? (один любой символ). Например, для искомого значения "*" & "иван" & "*" могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;"Два":1;"Один"};2;ЛОЖЬ) – результат выполнения строка «Два».

Функция ГПР

Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.

Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

Так как функция аналогична функции ВПР, за исключением того, что использует горизонтальные таблицы вместо вертикальных, то описания и примеры использования подходят и для ГПР с учетом упомянутых различий.

TIMEVALUE Функции даты

Возвращает долю указанного времени в 24 часах.

Пример использования

TIMEVALUE("2:15 PM")

TIMEVALUE("14:15:30")

TIMEVALUE("11:59:59.50 PM")

Синтаксис

TIMEVALUE(строка_времени)

· строка_с_временем – строка, содержащая время.

· Примечания

· Значение параметра строка_времени должно быть заключено в кавычки. Время следует записывать в 12-часовом или 24-часовом формате (например, "2:15 PM" или "14:15").

· Возвращает число от 0 (включительно) до 1 (исключительно). Ноль соответствует 12:00:00 AM, а 0,9999884259 – 11:59:59 PM. Используйте доли секунды (например, 11:59:59.50 PM), если необходимо дальнейшее приближение к 1.0.

· В параметре строка_времени дни недели и даты игнорируются.

 

ДЕНЬ (DAY)

Возвращает день, на который выпадает заданная дата, в числовом формате.

Пример использования

ДЕНЬ(ДАТА(1969; 7; 20))

ДЕНЬ(A2)

ДЕНЬ(40909)

ДЕНЬ("7/20/1969")

Синтаксис

ДЕНЬ(дата)

· дата – дата, для которой определяется день. В качестве этого параметра используется ссылка на ячейку с датой, функция, возвращающая тип данных "дата", или число.

· Примечания

· Убедитесь в том, что параметр, передаваемый в функцию, является: а) ссылкой на ячейку, содержащую дату, б) функцией, которая возвращает значение даты (ДАТА, ДАТАЗНАЧ или TO_DATE), в) порядковым номером даты в формате, возвращаемом функцией Ч. Дата и время в Google Таблицах представлены в числовом формате. Преобразование в такой формат происходит автоматически при вводе данных в ячейку. Функции принимают в качестве параметров даты только литеральные данные в числовом формате.

· ДЕНЬ не преобразует даты в числовом формате автоматически, как это происходит в Google Таблицах при вводе даты в ячейку. Поэтому ДЕНЬ(10/10/2000) интерпретируется как ДЕНЬ(0,005), т. е. в качестве параметра используется частное от деления 10 на 10 и затем на 2000.

· ДЕНЬ возвращает порядковый номер дня в указанном месяце. В общем случае не используется для определения компонента дня, поскольку это значение легко можно получить из самой даты.

 

ГОД (YEAR)

Возвращает год, указанный в заданной дате.

Пример использования

ГОД(ДАТА(1969; 7; 20))

ГОД(A2)

ГОД(40909)

Синтаксис

ГОД(дата)

· дата – дата, с которой начинается расчет. В качестве этого параметра используется ссылка на ячейку с датой, функция, возвращающая тип данных "дата", или число.

· Примечания

· Убедитесь в том, что параметр, передаваемый в функцию, является: а) ссылкой на ячейку, содержащую дату, б) функцией, которая возвращает значение даты (ДАТА, ДАТАЗНАЧ или TO_DATE), в) порядковым номером даты в формате, возвращаемом функцией Ч. Дата и время в Google Таблицах представлены в числовом формате. Преобразование в такой формат происходит автоматически при вводе данных в ячейку. Функции принимают в качестве параметров даты только литеральные данные в числовом формате.

· ГОД не преобразует даты в числовом формате автоматически, как это происходит в Google Таблицах при вводе даты в ячейку. Поэтому запись ГОД(10/10/2000) интерпретируется как ГОД(0,005), т. е. в качестве параметра используется частное от деления 10 на 10 и затем на 2000.

Защита книги, листа, ячеек.

Чтобы защитить структуру книги, выполните следующие действия:

1. На вкладке Рецензирование в разделе Изменения нажмите кнопку Защитить книгу.

ПРИМЕЧАНИЕ: Если вы используете Excel 2010 или более ранние версии, можно также установить флажок окна, если вы хотите запретить пользователям перемещение, закрытие, скрытие и отображение окон книги, а также изменение их размера. Параметр Окна недоступен в Excel 2013 и более поздних версиях.

2. Введите пароль в поле Пароль.

3. Нажмите кнопку ОК и снова введите пароль, чтобы подтвердить его.

ПРИМЕЧАНИЕ: Запишите пароли и сохраните их в безопасном месте. Если вы их потеряете, мы не сможем помочь с их восстановлением.

Защита листа

Решите, какие действия пользователи должны выполнять на листе (например, вставка и удаление столбцов или строк, изменение объектов, сортировка или использование автофильтра). Кроме того, вы также можете указать пароль, чтобы заблокировать лист. Пароль не позволит другим пользователям снять с листа защиту — его потребуется ввести для ее отключения.

Ниже приведены инструкции по защите листа.

1. На вкладке Рецензирование нажмите кнопку Защитить лист.

2. В списке Разрешить всем пользователям этого листа установите флажки для элементов, изменение которых должно быть доступно пользователям.

 

ГИПЕРССЫЛКА (HYPERLINK) Поисковые функции

Создает гиперссылку в ячейке.

Пример использования

ГИПЕРССЫЛКА("http://www.google.com/"; "Google")

Синтаксис

ГИПЕРССЫЛКА(ссылка; [link_label])

· ссылка – полный URL-адрес, заключенный в кавычки, или адрес ячейки, содержащей URL-адрес.