Ссылки и массивы в excel: Функции ссылок и массивов Excel

Содержание

Функции ссылки и поиска (справка)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

Важно: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.

Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.

Функция	Описание
АДРЕС	Возвращает ссылку на отдельную ячейку листа в виде текста.
ОБЛАСТИ	Возвращает количество областей в ссылке.
ВЫБОР	Выбирает значение из списка значений.
Функция CHOOSECOLS	Возвращает указанные столбцы из массива
Функция CHOOSEROWS	Возвращает указанные строки из массива.
СТОЛБЕЦ	Возвращает номер столбца, на который указывает ссылка.
ЧИСЛСТОЛБ	Возвращает количество столбцов в ссылке.
Функция DROP	Исключает указанное количество строк или столбцов из начала или конца массива
Функция EXPAND	Развертывание или заполнение массива до указанных измерений строк и столбцов
Функция ФИЛЬТР	Фильтрует диапазон данных на основе условий, которые вы определяете
Ф. ТЕКСТ	Возвращает формулу в заданной ссылке в виде текста.
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ	Возвращает данные, хранящиеся в отчете сводной таблицы.
ГПР	Выполняет поиск в первой строке массива и возвращает значение указанной ячейки.
Функция HSTACK	Добавляет массивы по горизонтали и последовательно, чтобы вернуть больший массив.
ГИПЕРССЫЛКА	Создает ссылку, открывающую документ, который находится на сервере сети, в интрасети или в Интернете.
ИНДЕКС	Использует индекс для выбора значения из ссылки или массива.
ДВССЫЛ	Возвращает ссылку, заданную текстовым значением.
ПРОСМОТР	Ищет значения в векторе или массиве.
ПОИСКПОЗ	Ищет значения в ссылке или массиве.
СМЕЩ	Возвращает смещение ссылки относительно заданной ссылки.
СТРОКА	Возвращает номер строки, определяемой ссылкой.
ЧСТРОК	Возвращает количество строк в ссылке.
ДРВ	Получает данные реального времени из программы, поддерживающей автоматизацию COM.
Функция СОРТ	Сортирует содержимое диапазона или массива
Функция СОРТПО	Сортирует содержимое диапазона или массива на основе значений в соответствующем диапазоне или массиве
Функция TAKE	Возвращает указанное число смежных строк или столбцов из начала или конца массива.
Функция TOCOL	Возвращает массив в одном столбце
Функция TOROW	Возвращает массив в одной строке
ТРАНСП	Возвращает транспонированный массив.
Функция УНИК	Возвращает список уникальных значений в списке или диапазоне
Функция VSTACK	Добавляет массивы по вертикали и последовательно, чтобы получить больший массив.
ВПР	Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце.
Функция WRAPCOLS	Создает оболочку для указанной строки или столбца значений по столбцам после указанного числа элементов.
Функция WRAPROWS	Заключает предоставленную строку или столбец значений по строкам после указанного числа элементов
Функция ПРОСМОТРX	Выполняет поиск по диапазону или массиву и возвращает элемент, соответствующий первому обнаружению совпадения. Если совпадение отсутствует, функция ПРОСМОТРX может вернуть ближайшее (приблизительное) совпадение.
Функция ПОИСКПОЗX	Возвращает относительную позицию элемента в массиве или диапазоне ячеек.

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Функции Excel (по категориям)

Функции Excel (по алфавиту)

Функции Excel для работы со ссылками и массивами

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 23. 05.2016

В этом примере Вы узнаете всё о функциях Excel для работы со ссылками и массивами, такими как ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС и ВЫБОР.

Содержание

ВПР
ГПР
ПОИСКПОЗ
ИНДЕКС
ВЫБОР

ВПР

Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

Вставляем функцию ВПР:
=ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)
=VLOOKUP(A2,$E$4:$G$7,3,FALSE)
Пояснение:
- Функция ВПР ищет значение ID (104) в крайнем левом столбце диапазона $E$4:$G$7 и возвращает значение из третьего столбца той же строки (так как третий аргумент функции имеет значение 3).
- Четвёртый аргумент функции равен ЛОЖЬ (FALSE) – это значит, что либо будет найдено точное совпадение, либо будет показано сообщение об ошибке #Н/Д (#N/A).
Потащите мышью, чтобы скопировать функцию ВПР из ячейки B2 вниз по столбцу до ячейки B11.Пояснение: Когда мы копируем функцию ВПР вниз, абсолютная ссылка $E$4:$G$7 остаётся неизменной, в то время как относительная ссылка A2 изменяется на A3, A4, A5 и так далее.

ГПР

Похожим образом работает и функция ГПР (HLOOKUP):

ПОИСКПОЗ

Функция ПОИСКПОЗ (MATCH) возвращает позицию искомого значения в заданном диапазоне:

Пояснение:

Слово Yellow занимает третью позицию в диапазоне E4:E7.
Третий аргумент функции не обязателен. Если для этого аргумента ввести значение 0 (ноль), то функция возвратит позицию элемента, точно совпадающего с искомым значением (A2). Если же точное совпадение найдено не будет, то функция вернет ошибку #Н/Д (#N/A).

ИНДЕКС

Функция ИНДЕКС (INDEX) возвращает заданное значение из двухмерного или одномерного диапазона.

Пояснение: Значение 92 находится на пересечении строки 3 и столбца 2 в диапазоне E4:F7.

Пояснение: Значение 97 находится на 3 месте в диапазоне E4:E7.

ВЫБОР

Функция ВЫБОР (CHOOSE) выбирает значение из списка по заданному номеру позиции.

Пояснение: Слово Boat находится в позиции 3.

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Транспонирование и связывание данных с источником в Excel: 4 метода

Когда вы копируете и вставляете ячейки в Excel, вы можете вставлять их как ссылки или транспонировать. Excel не позволяет делать и то, и другое одновременно. К сожалению, часто приходится связывать и транспонировать. Но есть три способа сделать это: сделать это вручную, используя формулу массива {=ТРАНСП()} или инструменты профессора Excel.

Содержание

Проблема: кнопка «Вставить ссылку» неактивна
Способ 1: Используйте функцию СМЕЩЕНИЯ
Способ 2: Формула массива «ТРАНСПОЗИРОВАТЬ»
Способ 3: Вставить как ссылку и транспонировать вручную
Способ 4: Инструменты Professor Excel
- Также интересно:

Проблема: кнопка «Вставить ссылку» неактивна

Когда вы устанавливаете галочку на «Транспонировать», кнопка «Вставить ссылку» становится серой.

Вы скопировали диапазон ячеек. Теперь вы хотите вставить их. Но вместо того, чтобы просто вставлять их, вы также хотите вставлять ссылки и транспонировать (заменять строки и столбцы) их.

Проблема: как только вы устанавливаете галочку на «Tranpose», «Вставить ссылку» становится серым.

Метод 1: Используйте функцию СМЕЩ

Обходной путь: Используйте формулу СМЕЩЕНИЯ для вставки ссылок и одновременного их транспонирования

Функция СМЕЩ является мощной, но, к сожалению, особенно для новичков, не очень проста в использовании. Грубо говоря, вы определяете базовую ячейку и указываете Excel, какое значение, считая от базовой ячейки, нужно вернуть.

Сначала: Подготовьте числа 0-2 (максимальное количество рядов – 1) сверху и 0-1 слева. Мы используем это как ссылку. Конечно, есть более элегантные способы (например, с помощью ROW() или COLUMN()), но это самый быстрый и простой способ. Следующее число соответствует картинке выше:

В нашем случае базовая ячейка — это левая верхняя угловая ячейка диапазона, который мы хотим транспонировать и связать. Эта базовая ячейка не должна изменяться, когда мы копируем и вставляем формулу, чтобы вставлять знаки $.
Вторая часть формулы OFFSET определяет, на сколько строк ваша целевая ячейка отстоит от базовой ячейки. Мы хотим, чтобы это число увеличивалось, когда мы тянем формулу вправо. Вот почему мы не добавляем знак $ перед столбцом («F»). Но при копировании формулы первая строка должна быть зафиксирована знаком $.
Аналогично для столбцов: когда мы раскрываем формулу, мы хотим, чтобы индекс столбца увеличивался. Вот почему мы фиксируем столбец, но не строку.

Формула в приведенном выше примере выглядит следующим образом:

 =СМЕЩ($B$2,F$1,$E2)

Метод 2: Формула массива «ТРАНСП»

Шаги для вставки в виде ссылок и транспонирования в Excel на одновременно

С формулой массива вам не нужно перетаскивать ячейки вручную. Несмотря на то преимущество, что не нужно ничего делать вручную, этот способ немного сложнее. Кроме того, он имеет недостатки всех формул массива: их размер не (легко) изменить. Поэтому, если диапазон ячеек ваших исходных данных изменяется, вам обычно приходится заново настраивать формулу массива.

Выполните следующие действия, чтобы установить формулу массива для вставки ячеек, связанных с их источниками, и их транспонирования (номера соответствуют изображению справа):

Скопируйте ячейки, которые вы хотите транспонировать и связать .
Выберите левую верхнюю ячейку области, в которую вы хотите их вставить, и откройте специальное окно вставки, нажав Ctrl + Alt + v.
Выберите «Форматы».
Выберите «Транспонировать» и подтвердите нажатием «ОК». Теперь вместо значений транспонируется просто исходное форматирование. Для этого шага есть две причины: узнать точный диапазон транспонированных ячеек и в то же время уже иметь форматы исходных ячеек.
Выберите всю область вставки (или убедитесь, что она по-прежнему выделена после вставки форматов).
Введите следующую формулу:

  =ТРАНСП('Исходный диапазон')

и нажмите Ctrl + Shift + Enter. Если вы нажмете только Enter (без Ctrl + Shift), Excel не распознает вашу формулу как формулу массива. Фигурные скобки будут созданы автоматически.

Метод 3: Вставить как ссылку и транспонировать вручную

Давайте рассмотрим очевидный метод: выполнение вручную. Если ваш диапазон сотовой связи не слишком велик, это часто самое быстрое решение. Конечно, выполнение действий вручную является источником возможных ошибок.

Скопируйте свои данные и вставьте их с помощью «Специальной вставки», одновременно нажав Ctrl + Alt + v на клавиатуре.
В левом нижнем углу специального окна вставки есть кнопка «Вставить ссылку». Нажмите на нее, и вы увидите, что Excel вставляет данные в виде ссылок.
Теперь — и это раздражает — вам нужно перетащить ячейки на их новые места, чтобы строки и столбцы поменялись местами.

Вы хотите повысить производительность в Excel ?

Получите ленту профессора Excel!

Узнать больше

Загрузить бесплатную пробную версию

Добавьте более 120 замечательных функций в Excel!

Метод 4: Инструменты профессора Excel

Инструменты профессора Excel: Вставить как ссылку и транспонировать двумя щелчками мыши

Поскольку все предыдущие методы имеют некоторые серьезные недостатки, вставка в виде ссылки и транспонирование данных были одной из первых функций, которые мы включили в наша надстройка Excel:

Выберите свои данные и нажмите «Копировать» в левой части ленты «Профессор Excel».
Выберите целевую ячейку и нажмите «Вставить ссылку и транспонировать» рядом с кнопкой копирования.

Эта функция работает для всей книги, если исходная и целевая книги открыты одновременно.

Эта функция включена в нашу надстройку Excel «Professor Excel Tools»

(без регистрации, загрузка начинается напрямую)

Опубликовано 4 февраля 2016 г.

Отнесено к категории Возможности, формулы и функции Excel С тегами копирование, ссылка, вставка, специальная вставка, транспонирование

Хенрик Шиффнер — независимый бизнес-консультант и разработчик программного обеспечения. Живет и работает в Гамбурге, Германия. Помимо того, что он энтузиаст Excel, он любит фотографию и спорт.

Посмотреть все сообщения Хенрика Шиффнера.

Объединение гиперссылки с формулой массива

Джаскин

Новый член

12 марта 2017 г.

Описанное выше также работает. Однако я пытаюсь каким-то образом объединить формулу массива с формулой гиперссылки, в результате чего получится более короткий список имен отделов, который при нажатии переходит к имени листа, заполненному в другом столбце.

На листе под названием «L3» есть 2 столбца (1) «Названия отделов» и (2) «Название листа». Столбец имени листа фиксирован, например; № 2, № 3, № 4 и т. д. (Я выбрал это наименование, чтобы использовать «#» на более позднем этапе, чтобы скрыть все листы, которые начинаются с уникального символа — отсюда «#». Столбец имени отдела использует фактическое имя отдела, и если строка, зарезервированная для этого отдела, не используется, то именем отдела по умолчанию становится имя листа (#2, #3 и т. д.), для этого используется формула If Then.0003

.
БлюХорнет
Активный член
12 марта 2017 г.

Не пытаясь следовать всей логике формулы, я понимаю, что вы хотите разработать формулу гиперссылки для перехода к указанным значениям «Отдел», которые предположительно соответствуют отдельным рабочим листам. Как насчет этого:
=ГИПЕРССЫЛКА( ПРАВАЯ(ЯЧЕЙКА(«имя файла», Пиво!A1), ДЛИН(ЯЧЕЙКА(«имя файла», Пиво!A1)) — НАЙТИ( «[«, ЯЧЕЙКА(«имя файла», Пиво! A1)) +1) & «!a1», ВПРАВО(ЯЧЕЙКА(«имя файла», Пиво!A1), ДЛСТР(ЯЧЕЙКА(«имя файла», Пиво!A1)) — НАЙТИ( «]», ЯЧЕЙКА(«имя файла» , Пиво!А1))))
Я использовал ярлыки для перехода к листу «Пиво», но логика должна вести вас и к другим листам. И, очевидно, если у вас уже есть ячейки с «Пиво», «Вино», «Газированные напитки» и т. д., вы можете использовать их как вторую часть функции ГИПЕРССЫЛКИ, «понятные имена».
Большое спасибо, что нашли время ответить. К сожалению, ваше решение не совсем то, что мне нужно. Я пытаюсь выполнить 2 задачи в 1. Создайте из основного списка новый список, исключающий определенные значения (например, значения, начинающиеся с #), и в то же время создайте гиперссылку на лист, связанный с «Дружественным Имя» в результате нового списка.
Я буду продолжать работать, потому что уверен, что должен быть способ сделать это. Я могу добиться этого, сначала создав новый список, а затем в соседней ячейке создав гиперссылку — но я упрямый, предпочитаю, чтобы все это заключалось в одну формулу