Ссылки и массивы

• АДРЕС(ADRESS) - возвращает адрес ячейки в виде текста, на основании указанных номера строки и столбца. Так же можно указать имя листа и стиль ссылок(R1C1 или А1).
• ВПР(VLOOKUP) - Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце. Читать подробнее про ВПР: Как найти значение в другой таблице или сила ВПР
• ГПР(HLOOKUP) - Ищет значение в первой строке массива и выдает значение из ячейки в найденном столбце и указанной строке. Более подробно можно ознакомиться в статье про ВПР(VLOOKUP): Как найти значение в другой таблице или сила ВПР: Как искать значение по строке, а не столбцу?.
  Т.к. функции практически одинаковые - все нюансы у них тоже идентичны. Разница между ними лишь в том, что ВПР ищет сверху вниз, а ГПР - слева направо.
• ОБЛАСТИ(AREAS) - возвращает количество областей в указанном диапазоне. Областью считается прямоугольный непрерывный диапазон ячеек. Например, такая запись выдаст 3:
  =ОБЛАСТИ((A1:A10;C5:C13;E11:G11))
  =AREAS((A1:A10,C5:C13,E11:G11))
  Обращаю внимание, что если указывается несколько диапазонов, то они должны быть дополнительно заключены в скобки, иначе функция вернет ошибку.
• ВЫБОР(CHOOSE) - возвращает значение из заданного списка значений по индексу. Первым аргументом указывается индекс(целое число), а далее идет перечисление значений по порядку. Например, для определения имени месяца по его номеру, записанному в ячейке, можно использовать такую формулу:
  =ВЫБОР(A1;"Январь"; "Февраль"; "Март"; "Апрель"; "Май"; "Июнь"; "Июль"; "Август"; "Сентябрь"; "Октябрь"; "Ноябрь"; "Декабрь")
  =CHOOSE(A1,"Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", "Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь")
• ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(GETPIVOTDATA) - Возвращает данные, хранящиеся в сводной таблице на основании ссылки на сводную таблицу и указания полей сводной, из которых это значение необходимо получить:
  =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Продажа";$A$3;"Группа";"Бакалея")
  =GETPIVOTDATA("Продажа",$A$3,"Группа","Бакалея")
  Где:
  • "Продажа" - наименование поля из области значений. Именно в этом поле располагается числовое значение для получения
  • $A$3 - ссылка на ячейку сводной таблицы. Если указан диапазон, включающий несколько сводных таблиц - в качестве сводной таблицы для извлечения значений будет взята сводная таблица, созданная последней
  • "Группа" - наименование поля, которое помещено в область строк
  • "Бакалея" - конкретное значение из указанного поля "Группа", сумму для которого необходимо извлечь

  Функция применяется исключительно для полей значений(те поля, в которых применяется одна из функций агрегирования: сумма, количество, среднее и т.д.).
  Если в текущем представлении сводной таблицы указанное поле отсутствует(поле есть в наборе данных, но не помещено в отчет или скрыто фильтром) - функция вернет ошибку #ССЫЛКА!(#REF!). Например, применительно к приведенной выше функции, если скрыть фильтром строку со значением "Бакалея" - функция вернет ошибку, т.к. текущее представление сводной таблицы не отображает строку со значением "Бакалея".

• ГИПЕРССЫЛКА(HYPERLINK) - Создает активную ссылку на файл или папку(в том числе сетевые), а так же на страницу в Интернет(читать подробнее).
• ИНДЕКС(INDEX) - возвращает значение из диапазона, на пересечении указанных строки и столбца. Чаще всего применяется в связке с функцией ПОИСКПОЗ(MATCH) вместо ВПР, когда искомое значение находится правее столбца возвращаемых значений. Подробнее можно ознакомиться в статье: Как найти значение в другой таблице или сила ВПР: решение при помощи ПОИСКПОЗ)
• ДВССЫЛ(INDIRECT) - создает реальную ссылку на ячейку или диапазон ячеек из текстового значения. Может пригодиться, когда на основании значения ячейки надо сделать ссылку. Например, если в столбце А записаны имена листов книги, а в столбце В надо получить ссылки на ячейку F5 каждого указанного листа:
  =ДВССЫЛ("'"&A1&"'!F5")
  =ДВССЫЛ("'"&A1&"'!F5")
• ПРОСМОТР(LOOKUP) - Ищет заданное значение в строке или столбце
• ПОИСКПОЗ(MATCH) - Ищет значение в столбце или в строке. Как правило применяется совместно с другими функциями, вроде ИНДЕКС, ВПР, ГПР. Подробнее можно узнать из статьи про ВПР: Как найти значение в другой таблице или сила ВПР: решение при помощи ПОИСКПОЗ)
• СМЕЩ(OFFSET) - Возвращает ссылку на заданную ячейку или диапазон ячеек, смещенную на указанное количество строк и столбцов. Так же можно задать новый размер возвращаемой ссылки: задать новое количество строк и столбцов. Т.е. из ячейки A1 можно сделать ссылку на диапазон C1:D10, применив формулу СМЕЩ:
  =СМЕЩ(A1;0;2;10;2)
  =OFFSET(A1,0,2,10,2)
  Чаще всего применяется в составе других формул или для создания Динамических именованных диапазонов.
• СТОЛБЕЦ(COLUMN) - возвращает номер столбца для указанной ячейки. Если указан диапазон ячеек - функция вернет номер столбца верхней левой ячейки.
• СТРОКА(ROW) - возвращает номер строки для указанной ячейки. Если указан диапазон ячеек - функция вернет номер строки верхней левой ячейки.
• ЧИСЛСТОЛБ(COLUMNS) - возвращает количество столбцов в указанном диапазоне ячеек. В основном применяется в составе других функций, в которых необходимо подсчитать общее количество столбцов. Например, для получения последнего столбца в функции ВПР(VLOOKUP), если столбцов много:
  =ВПР($A2;Лист1!$A$1:$C$4;ЧИСЛСТОЛБ($A$1:$C$4);0)
  =VLOOKUP($A2,Лист1!$A$1:$C$4,COLUMNS($A$1:$C$4),0)
• ЧСТРОК(ROWS) - возвращает количество строк в указанном диапазоне ячеек. Как и ЧИСЛСТОЛБ(COLUMNS) чаще всего применяется в составе других функций.
• ДРВ(RTD) - Извлекает данные реального времени из программ, поддерживающих автоматизацию COM.
• ТРАНСП(TRANSPOSE) - "переворачивает" указанную таблицу, делая строки столбцами, а столбцы строками. Иначе говоря - транспонирует. На примере диапазона A1:C10:
  • выделяем диапазон ячеек(D1:M3), равный по количеству ячеек исходному диапазону значений(A1:C10), которые необходимо транспонировать
  • вписываем функцию и в качестве аргумента указываем ссылку на исходный диапазон значений: =ТРАНСП(A1:C10)
  • завершаем ввод функции сочетанием клавиш Ctrl+Shift+Enter. Это обязательное условие для версий Excel до 2021(и без подписки офис 365)

  В диапазоне D1:M3 получим транспонированную таблицу A1:C10. При написании функции следует учитывать, что число строк в диапазоне функции(D1:M3) должно быть равно числу столбцов в исходном диапазоне(A1:C10), а число столбцов - числу строк. Если указать меньше - не все значения будут транспонированы. Если больше - то все лишние ячейки будут заполнены значениями #Н/Д(#N/A).