Excel выбрать из массива

5 основных функции для работы с массивами

Доброго времени суток друзья!

Сегодня я бы хотел затронуть тему массивов, так как частенько возникает ситуация, когда нужно найти значение в большом массиве данных. Для этих целей в Excel существует целый раздел функции для работы с массивами, которые помогают нам выполнять работы с массивами, но сейчас мы рассмотрим самые распространенные.

Обратите внимание, на эти функции, так как работа с огромными массивами данных, одна из самых распространенных и правильное использование этих функции позволит вам значительно упростить и облегчить работу с таблицами Excel.

Ну, что же, изучим необходимые функции для работы с массивами:

1. Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван»)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

=ПОИСКПОЗ(B3;B2:B5;0)

С примера вы можете видеть что слово «Стол» занимает 2 позицию в указанном диапазоне. Замечу, что третий аргумент в функции не является обязательным. При введенном значении 0, функция вернет ту позицию элемента массива, которое точно совпадает со значением, которое мы ищем. В случае, когда точное совпадение отсутствует, функция выдаст ошибку #Н/Д (#N/A).

4. Функция ГПР (HLOOKUP)

Ищет значение в указанном диапазоне и возвращает значение ячейки, которая находится в указанной строке того же столбца: =ГПР(C1;$B$1:$E$2;1;ЛОЖЬ). Как видите с примера, функция ГПР ищет в указанном диапазоне $B$1:$E$2 (знаком $ я указал абсолютную ссылку) и согласно условию возвращает искомое значение из первой строки, а аргумент «ЛОЖЬ» означает, что-либо, будет найдено нужное значение, либо мы получим ошибку #Н/Д.

5. Функция ВПР (VLOOKUP)

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

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Как видим, формула идентична предыдущей функции ГПР и так же ищет указанный номер «B4» в диапазоне $B$2:$C$5 со знаком $ (это сделано для создания абсолютной ссылки, что бы при копировании формулы на диапазон, аргумент не будет изменен), в третьем столбце, так как аргумент функции равен 2. Ну и четвёртый аргумент равен значению «ЛОЖЬ», это означает, что-либо будет найдено совпадение значений, либо будет получено сообщение об ошибке #Н/Д. Теперь при необходимости, мы копируем формулу, и она перенесёт все правильные аргументы по всему диапазону вычислений. Это возможно стало из-за абсолютной ссылки на массив значений, а вот первый аргумент на B4, при копировании, должен измениться на B5 и так далее.

А на этом у меня всё! Я очень надеюсь, что описание 5 основных функций для работы с массивами вам стали ближе и понятнее. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

Excel выбрать из массива

Доброго времени суток друзья!

Обратите внимание, на эти функции, так как работа с огромными массивами данных, одна из самых распространенных и правильное использование этих функциипозволит вам значительно упростить и облегчить работу с таблицами Excel.

Ну, что же, изучим необходимые функции для работы с массивами:

1. Функция ВЫБОР (CHOOSE)

Позволит вам выбрать значение из общего списка по указанному номеру позиции:

=ВЫБОР(2;»Стул»;»Стол»;»Шкаф»;»Диван«)

2. Функция ИНДЕКС (INDEX)

Эта функция возвращает указанное значение из одно- или двумерного диапазона:

=ИНДЕКС(A1:C6;4;3)

Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки № 4 и столбика № 3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:

Читать еще: Двссыл в excel примеры

=ИНДЕКС(С1:С6;2)

3. Функция ПОИСКПОЗ (MATCH)

Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:

=ПОИСКПОЗ(B3;B2:B5;0)

4. Функция ГПР (HLOOKUP)

5. Функция ВПР (VLOOKUP)

=ВПР(B4;$B$2:$C$5;2;ЛОЖЬ)

Массивы Excel

Умение работать с массивами является заключительным этапом изучения функций Excel, т.к. они целиком раскрывают их возможности и обеспечивают гибкость расчетов.

Содержание статьи:

Определение массива

Чтобы понять, что такое массив, необходимо вспомнить, что такое переменная.

Переменная — область памяти, за которой закреплено определенной имя, например «x = 5» или «Имя = “Андрей”». Переменная всегда содержит только одно значение, т.е. не может быть разделена на более мелкие части. Данное определение больше подходит для переменных в языках программирования. Чтобы не отходить от темы Excel, переопределим его.

Каждый лист Excel является таблицей, за которой закреплена область в памяти компьютера. Таблица состоит из ячеек, которые имеют свой уникальный адрес. Сама ячейка не может быть разделена на более мелкие части, поэтому ее можно назвать переменной. Массив содержит набор переменных и имеет имя. То, что называют в Excel диапазоном, по своей сути является массивом: строка листа, столбец листа, количество ячеек >1, все это массивы данных. НО! Чтобы не вносить путаницу в определения функций и т.п. данные понятия необходимо разделять, т.к. приложение по-разному обрабатывает диапазоны и массивы.

Чтобы дать программе понять, что формула содержит массив, нужно после ввода данных в строку формул одновременно нажать клавиши клавиатуры Ctrl + Shift + Enter. Все ее содержимое заключится в фигурные скобки <>.

Далее будет подробнее описана работа с массивами.

Измерения массива

Массивы могут содержать несколько измерений вплоть до измерений в несколько десятков и даже больше, но хорошо это или плохо, в формулах Excel используются максимум 2 измерения, поэтому массив в Excel может быть:

Одномерным – 1 измерение;
Двумерным – 2 измерения.

Одномерный массив состоит из одного ряда значений. Это может быть строка или столбец.

Двумерный массив состоит из столбцов и из строк, т.е. представляется собой таблицу.

На рисунке ниже представлены оба вида массивов. Обратите внимание, что разные измерения имеют разные разделители («;» — для столбцов и «:» — строк).

Массив констант

Константа это та же переменная, только не меняющая значение. Если значение переменной можно поменять в любое время, то константа задается один раз и больше не меняется. Наверное, самая известная константа – число Пи.

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

Читать еще: Excel инкремент ячейки

Представьте, что Вам необходимо использовать в расчетах большой массив, состоящий из сотни констант, и использовать его нужно многократно. Набивать константы каждый раз для каждой формулы ручками – дело «неблагодарное». Поэтому, создайте синоним массива с помощью функции присвоения имен, расположенной на вкладке «Формулы» -> раздел «Определенные имена» -> кнопка «Диспетчер имен». В появившемся окне нажмите на кнопку «Создать», после чего появиться следующая форма:

Имя – имя диапазона;
Область – место, где данное имя будет доступно;
Примечание – комментарий. Текст, введенный здесь, будет высвечиваться при выборе имени массива из определенной для него области;
Диапазон – сам массив в виде ссылки на диапазон либо массив констант «=<…>».

После заполнения формы, нажмите «OK».

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

О том, как их применять рассказывается дальше.

Операции с массивами

Перейдем, наконец, к примерам использования массивов.

Название авто	Литраж бака, л	Расход, л на 100 км
Авто1	50	6
Авто2	60	7
Авто3	70	10
Авто4	80	12

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

При стандартных вычислениях формула составлялась бы так: =B2/C2*100. Затем ее необходимо протянуть. Мы получили готовый результат.

Повторим расчет, только с использованием массивов.

Сначала выделяем ячейки, в которых необходимо произвести расчет. Далее записываем в строку формул: =B2:B5/C2:C5*100, где “B2:B5” диапазон всех ячеек для литража, “C2:C5” диапазон всех ячеек для расхода. На данный момент это именно диапазоны, чтобы они превратились в массивы, нажмите одновременно клавиши Ctrl + Shift + Enter. Формула автоматически будет заключена в фигурные скобки, а расчет появиться во всех предварительно выделенных ячейках. Результат тот же, что и в первом варианте.

Следовательно, уже навязывается вопрос о смысле использования массивов.

В данном конкретном примере, что использовать – разницы нет, если только Вы не хотите защитить ячейки от случайного или намеренно изменения. Поясним. Когда к какому-то диапазону применяется массив, то затем никакой элемент этого массива не может быть отдельно изменен либо удален. Если попробовать произвести эти действия, приложение Excel выдаст ошибку. Чтобы избежать ее, выделите весь диапазон, к которому применен массив, а затем измените строку формул либо удалите ее полностью, после подтвердите изменения для всех элементов нажатием Ctrl + Shift + Enter.

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

Используя стандартные формулы, подсчет производиться двумя действиями:

Рассчитать километраж для каждого авто;
Просуммировать все имеющиеся результаты.

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

Выберите ячейку, в которую хотели бы записать результат. В нее впишите уже применяемую формулу, но в качестве аргумента функции СУММ. Подтвердите использование массива нажатие Ctrl + Shift + Enter . Должно получиться следующее: <=СУММ(B2:B5/C2:C5*100)>.

Согласитесь, что в данном примере плюсы массивов куда более заметные.

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

Применим массив, который мы создавали в начале урока: Имя_диапазона.

Данный массив является одномерным и имеет размерность 5, т.к. содержит пять элементов. Перенесем значения массива в ячейки книги. Для этого выделим диапазон A1:E1, в строку формул введем имя массива и нажмем Ctrl + Shift + Enter. Получим результат:

В данном случае программа поочередно будет вытаскивать значения из массива для каждой ячейки. Если ячеек окажется больше, чем элементов есть в массиве, то для последних ячеек будет выдана ошибка #Н/Д, т.е. элемент не найден. Если ячеек, наоборот окажется меньше, то массив будет задействован частично, а именно в количестве заданных ячеек.

Теперь поступим по-другому. Выделите вертикальный диапазон A1:A5 и введите в него массив. Во все ячейки диапазона загрузиться только первый элемент массива. Это произошло от того, что созданный нами массив является горизонтальным, а не вертикальным.

Для создания горизонтального массива его элементы разделяются «;», для создания вертикального используется «:». Для создания двумерных массивов используются оба символа. Имейте в виду, иногда, где и какой символ использовать, задается настройками, чтобы проверить, какие разделители использовать именно Вам, поступите следующим образом:

Заполните числами несколько ячеек подряд по горизонтали и вертикали;
Создайте функцию СУММ;
Для первого аргумента функции укажите горизонтальный диапазон, для второго вертикальный;
Нажмите на кнопку «вставить функцию» рядом со строкой формул;
В окне аргументов функции посмотрите предварительные результаты для каждого аргумента. Те разделите, которые использует программа, необходимо использовать и Вам.

Рассмотрим еще один пример операций с массивами, а именно совместное использование горизонтальных, вертикальных и двумерных массивов.

Для начала создадим одномерный вертикальный массив = <1: 1: 1>и умножим его на 5, после чего узнаем сумму произведений его элементов.

Здесь все просто, программа умножила каждый элемент массива на 5, затем их просуммировала.

Теперь умножим тот же диапазон на горизонтальный массив =<5; 5>.

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

Снова изменим условия и из вертикального массива сделаем двумерный массив =<1; 1: 1; 1: 1; 1>. Перемножим на =<5; 5>. И вот оно! Результат не поменялся. Почему? Потому что в случае использования двумерных массивов их элементы сопоставляются другим элементам массива по их порядковым номерам в равных измерениях.

А если размерность одного массива в определенном измерении будет превышать размерность другого массива в том же измерении, то элементам первого будет сопоставлена ошибка #Н/Д, т.к. для них отсутствуют сопоставляемые элементы второго, в результате чего, вся формула может вернуть данную ошибку.

Принцип сопоставления элементов массива друг другу важно понять с самого начала, чтобы не получить «неожиданного» результата при расчетах в дальнейшем.

Имейте в виду, что к массивам можно применять все формулы, которые применяются в стандартных ситуациях, включая логические. Например, в случае, описанном выше, когда перемножаются массивы с разными размерностями в одинаковых измерениях, чтобы избежать возврата ошибки, необходимо изменить формулу – <=СУММ(ЕСЛИОШИБКА(A1:C3*E1:F1;0))>.

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

Добрый день, уважаемые подписчики и читатели блога! Сегодня чуть больше поговорим о массивах и практике их применения в Excel.

Задам несколько целей — нужно в расчётах таблицы использовать формулы массива и комбинировать их с обычными формулами, подробнее рассмотреть плюсы и минусы использования массивов.

Как обычно мы действуем? Вводим формулу — количество * цену, плюсуем доставку, enter, протягиваем за маркер автозаполнения вниз, получаем результат. Способ классический, рабочий.

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

Первый нюанс, когда работаем с формулой массива нельзя нажимать Enter! Нажимаем Ctrl+Shift+Enter! Что это даст в итоге.

К обычной формуле добавятся фигурные скобки в самом начале и конце. Это говорит о том, что применён массив. То есть — если таблица большая, проще выделить весь столбец и использовать массив, чтобы не протягивать маркером до конца таблицы.

Комбинации формул массива и обычных формул

Усложняем задачу — нужно подвести итог (СУММ) по всему столбцу «ИТОГО», высчитать максимальную сумму заказа и узнать среднюю сумму заказа без копеек.

Аналогично воспользуемся функцией СУММ, только передадим ей опять массив в виде вычислений. Не забываем про Ctrl+Shift+Enter!

Взглянем в строчку формул.

Посмотрим, что в этому случае делает программа Excel — пройдём по шагам вычислений. На вкладке «Формулы» нажмём кнопку «Вычислить формулу».

В окне вычислений видно, что теперь программа извлекает значения из каждой ячейки массива, затем производит математическое действие и выводит результат. Минус — на такое извлечение, если данных много, можно потратить много времени.

Аналогично подсчитаем максимальную стоимость покупки. Используем формулу МАКС и передадим ей массив.

Настала очередь средней суммы покупки. Для округления будем использовать ЦЕЛОЕ (чтобы результат был без копеек) и СРЗНАЧ.

Готово. В этом случае даже получилось «экономнее» нежели в обычных формулах, так как для промежуточного итога нет своего столбца!

Как видно их примеров — у массивов есть свои плюсы и свои минусы. Не пробуйте удалять массив (если он содержится в диапазоне ячеек). Изменить только одну ячейку массива нельзя! Плюс — улучшаем безопасность, минус — приходится переделывать весь массив, если что-то пошло не так.

Если остались вопросы — посмотрите новое видео. Ну и по традиции — вам может понравиться статья про функцию ПЛТ. Всем удачи!