Lost your password?

Главная » Функции Excel »

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

  • АДРЕС(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).


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти