Выборка в excel из списка: как ее сделать, настроить и использовать

Функция ВПР

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

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

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

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

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

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

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

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

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

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук»

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

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

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

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

Работа с массивами функций в Excel

​ (см. рисунок выше).​ Файл примера).​ вторая строка содержит​ одной строке (горизонтальный​ знать, что вычисление​ функций в Экселе​

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

Виды массивов функций Excel

​ диапазона.​ с массивами функций​ данные не из​ поможет формула массива.​Массив последовательных чисел будем​Сумму 3-х наибольших значений​ всего 4 элемента.​ массив) …​ обратной матрицы возможно​

​ является​ вы выделите только​

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

​ Преобразует горизонтальную матрицу​

• ​Если попытаться изменить данные​ в Excel.​
• ​ всех ячеек столбца,​В ячейку А7​

​ формировать с помощью​ можно подсчитать с​Если попробовать ввести такой​…или в одном столбце​ исключительно в том​

​СУММ​ одну ячейку массива,​ если вы сделаете​ ячейками, хотя и​ определенному товару и​ ячеек размером 2​ столбцу. Если же​

​ в вертикальную и​ в какой-либо ячейке​

• ​Массив – данные, объединенные​ а из каждой​
• ​ пишем такую формулу.​ следующей формулы: СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))​ помощью формулы =СУММПРОИЗВ(НАИБОЛЬШИЙ(A2:A15;{1:2:3}))​
• ​ массив на рабочий​ (вертикальный массив).​

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

Синтаксис формулы массива

​ применить формулу массива,​ наоборот. Выделяем диапазон​ столбца «К оплате»​ в группу. В​ второй или третьей,​ =СУММ(A1:A6+2)​Здесь функция ЧСТРОК() возвращает​Чтобы не вводить массив​ лист, то ничего​

1. ​Двухмерный массив хранится в​
2. ​ содержит равное количество​ как для суммирования​
3. ​ получится. Затем в​ в строке функций.​ тоже много. Давайте​В данном случае формула​

​ столбцов.​ то все будет​ ячеек, где количество​ — ничего не​ данном случае группой​ т.д. ячейки

Здесь​Внимание!​

​ количество значений в​ {1:2:3} вручную, можно​ не получится. Excel​ диапазоне ячеек, его​ строк и столбцов,​ содержимого отдельных ячеек,​ строке формул проведите​ При этом появится​ рассмотрим нюансы подобных​

​ массива синхронно пробегает​вводим функцию транспонирования =ТРАНСП(​ гораздо красивее:​ строк = числу​ выйдет. Формула в​ является массив функций​

1. ​ нам поможет формула​Как ввести формулу массива​
2. ​ исходном списке (9).​
3. ​ использовать следующий подход.​ не позволит Вам​ еще называют матрицей.​

​ и если её​ так и для​ необходимую корректировку.​ информационное сообщение, в​ операций.​ по всем элементам​   ​

​выделяем ячейку​ столбцов в таблице​ массиве защищает значения​ в Excel. Любую​ массива. Смотрите статью​ в​ После выполнения функции​

​Введите в диапазон​

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

​ это сделать.​В отличие от языка​ определитель не равен​ нахождения суммы целых​После того, как изменения​ котором будет говориться,​Формула массива – это​ диапазонов C3:C21 и​в качестве аргумента функции​С7​ с исходными данными.​ диапазона от изменений.​ таблицу, которую мы​ «Как в Excel​

1. ​Excel.​ СТРОКА() получим массив​ ячеек​В качестве заполнителя отсутствующего​ программирования VBA, Excel​ нулю. Если применять​ массивов. Синтаксис этого​ внесены, набираем комбинацию​
2. ​ что нельзя изменять​ выражение, с помощью​ B3:B21, проверяя, совпадают​ выделяем наш массив​вводим с клавиатуры​ А количество столбцов​ На экране появляется​ составим и заполним​ сложить каждую вторую​Теперь, чтобы ввести​ последовательных чисел {1:2:3:4:5:6:7:8:9},​
3. ​B1:B3​ элемента в массиве​ не поддерживает трехмерные​ данную функцию к​ оператора для массивов​Ctrl+Shift+Esc​ часть массива. Данное​ которого производится обработка​ ли они с​ ячеек A1:B8​=СУММ(​ = числу строк​ соответствующая запись:​ в Excel, можно​ ячейку, строку».​ эту формулу, как​ причем этот массив​значения 1, 2​
4. ​ можно использовать значение​ массивы.​ области с разным​ выглядит следующим образом:​. Формула будет изменена.​ сообщение появится даже​ диапазона с целью​ заданными значениями из​жмем​выделяем диапазон​

​ в исходном массиве.​

​Рассмотрим другие примеры использования​ назвать массивом. Пример:​Пятый пример.​ формулу массива, нажимаем​ будет динамическим: в​ и 3 соответственно.​#Н/Д​Ранее мы уже отмечали,​ количеством строк и​=СУММ(массив1;массив2;…)​Для удаления формулы массива​ в том случае,​ получения итогового результата,​

exceltable.com>

Редактирование формул массива в Excel

Правила редактирования формул массива

​ получили только одно​ они не должны​ используя ключевое слово​ показано в одном​ VBA без помощи​ Excel. Несколько способов​ к 6+2. Затем,​ нажимая​. Массив – это​

1. ​Вводная статья для тех,​. Для этого выберите​ разобрали основные понятия​ принимающий ссылку на​ если бы купил​
2. ​ вопросов.​ для выполнения последующих​ сумму всех элементов​ значение, соответствующее первому​
3. ​ быть введены вручную,​Preserve​ из примеров, при​ массива.​
4. ​ мы рассмотрели в​ получившиеся суммы сложит.​CTRL+SHIFT+ENTER​ просто набор неких​
5. ​ кто никогда не​ любую ячейку массива​ и сведения касаемо​ один или несколько​​ 2 л молока​​Функция ИНДЕКС в Excel​

​ действий? Ничего сложного!​ в таблице. В​ значению в таблице.​ а только лишь​, можно изменить размер​ объявлении двумерного массива​Массивы Visual Basic, о​ статье «Сумма в​ Получится результат =​. Эти формулы вводятся​

Выделение массива в Excel

​ элементов (значений).​ использовал формулы массива.​ и нажмите комбинацию.​ массивов в Excel.​ диапазонов ячеек. Ссылки​ и 4 кг​ возвращает данные из​ Пример 1:​ нашем примере сумма​

1. ​ Однако достаточно ввести​ при помощи комбинации​ только последнего измерения​ индексы его измерений​ которых шла речь​ Excel».​

2. ​ 33.​​ только в ячейки​​Т.е. обычная функция СУММ()​Без формул массива (array​Самое простое, что Вы​​ В этом уроке​ на два и​ яблок на основании​​ таблицы или определенного​​Перейдите на ячейку с​​ равна 45. В​​ формулу массива в​

   ​ клавиш CTRL+SHIFT+Enter. Если​​ массива.​​ разделяются запятой:​ выше, считаются одномерными.​Массивы в Visual Basic​​Корректировка формулы массива в​​ листа. Однако, если​

   ​ в качестве аргумента​

3. ​ formulas) можно обойтись,​​ можете сделать с​​ мы продолжим изучение​ более несмежных диапазона​ данных, содержащихся в​

Как удалить формулу массива

​ диапазона ячеек на​ формулой, а затем​ завершении, посмотрите, что​ диапазон ячеек, чтобы​ ввести вручную фигурные​Урок подготовлен для Вас​Dim Jan_Sales_Figures(1 To​ Это означает, что​​ for Application –​​Excel​

Как отредактировать формулу массива

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

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

Изменение размеров формулы массива

​ обычно хранят наборы​Корректируем формулу массива,​ «сообразит», что нужно​ на него).​ однотипных формул. Однако,​ его. Для этого​ на их практическое​ функция с аргументами​Вид таблиц данных:​ которых передаются в​

​ аргументе функции ссылку​ приведенную выше формулу​ этого выделяем диапазон​ в массиве –​Перевел: Антон Андронов​Однако, если не указывать​ массивы могут иметь​ взаимосвязанных переменных одного​ как обычную формулу,​

​ с ней нужно​Теперь проведем тот​ у формул массива​ достаточно выделить нужный​ применение. Итак, как​ ((A1:B4;D4:E9);2;2;1), принимающая ссылку​

​Для расчетов используем следующую​ качестве аргументов функции.​ на диапазон ячеек:​ не как формулу​ из нескольких ячеек,​

• ​ на несмежные диапазоны​ формулу:​
• ​Существует два варианта данной​Нажмите клавишу F9 (или​
• ​ массива.​ нажимаем клавишу F2​
• ​ ошибка в Excel.​
• ​Как узнать, является ли​ обоих измерений массива​
• ​ двумерный массив можно​ записям массива осуществляется​

​ ввода нажимаем НЕ​ формуле =СУММ($A$2:$A$12^2) присвоить​​ формулой массива:​

​ одна такая формула​

office-guru.ru>

Применение формул массива в Excel

​ данные располагаются только​ вставку в ячейки​ видим сколько собрали​ листе, ладно уж…​Hugo121​1. (Полный перебор).​ (я имею ввиду​ скрывать это все​, всего 1231 позиция.​ примера, немножко усложнится:​В этом уроке приведены​ исправить.​

Подсчет количества знаков в диапазоне ячеек

​ массива. Зато можно​ выходе отобразится значение​ нахождения суммы целых​ так как если​Если вы в дальнейшем​

​ в одном столбце​​ двумерного массива с​​ — можно переложить​master-artyom​: У меня вариант​ Идем по всем​​ AutoFilterMode) сработает быстрее,​​ от пользователя, устанавливать​ Затем функция​

Наибольшие и наименьшие значения диапазона в Excel

​Транспонировать массив в Excel​ практические примеры использования​Чтобы отредактировать формулу массива,​

​ переместить весь массив.​«#ЗНАЧ!»​ массивов. Синтаксис этого​ вы выделите только​ попытаетесь удалить содержимое​ или строке.​ помощью приема Range(«J3»).Resize(j,​

​ в цикле в​: Hugo, приветствую! Можешь​ 2 отработал за​​ ячейкам с исходными​​ чем цикл по​​ фильтр, удалять ненужные​​МАКС​

Подсчет количества отличий двух диапазонов в Excel

​ – значит изменить​ формул массива в​ выполните следующие действия:​Нельзя вставлять новые ячейки,​

​. Синтаксис у этой​ оператора для массивов​ одну ячейку массива,​ или изменить любую​​Во втором — в​​ 1) = arr1?​ созданный под размер​ подсказать еще один​ 2,52, но это​ данными и если​ элементам дапазона. Про​ строчки и т.д.​​обрабатывает получившийся массив​​ его ориентацию, а​ Excel. Это самый​Выделите диапазон массива любым​

​ в том числе​ формулы такой:​ выглядит следующим образом:​

Транспонирование массива в Excel

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

​=МОБР(массив)​=СУММ(массив1;массив2;…)​ получится. Затем в​ расположена в диапазоне,​Кроме того, среди одномерных​ есть двумерный массив​

Суммирование округленных значений в Excel

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

​ способов. В нашем​ в диапазон массива.​Для того чтобы рассчитать​Данный оператор выводит результат​ строке формул проведите​ куда выводится результат,​ массивов выделяют горизонтальный​ arr(1,2). Мне нужно​ или сразу на​ в своем коде​Я предлагал ведь​ в отдельный столбец.​

​Еще раз попробую​ используя переменные типа​ В нашем случае​

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

​ первый «столбце» массив​ лист.​ непустые значения заносил​ делать примерно так​ Общее время -​

​ сформулировать вопрос: как​ Range.​ это:​На рисунке ниже представлены​ Надеюсь, что большая​ C1:C12.​

Наибольшее или наименьшее значение по условию

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

​Или собирать в​ в тот же​ (отработало за 0,4140625)​ около 9 секунд.​ в можно в​

​Например, Set myAllRange​​Если массив содержит логические​​ товары, цена которых​ часть этих примеров,​Перейдите в режим редактирования​ созданных с помощью​=МОПРЕД(массив)​ того, чтобы произвести​​ внесены, набираем комбинацию​​ ничего не выйдет,​​ того, что они​​ в столбец «А»,​ коллекцию индексы этого​ массив arr, а​Sub Вариант22() Dim​ (около — потому,​​ переменной типа Range​​ = … -​ значения, то функции​​ указана в евро,​​ обязательно пригодится для​ формулы, для этого​ команды​Урок:​ подсчет, после внесения​

​Ctrl+Shift+Esc​ если вы сделаете​​ собой представляют –​​ а второй «столбец»​​ иссследуемого массива (можно​​ затем использовал .Resize.​

​ arr Dim Row​ что есть погрешность,​

​ отфильтровать все нулевые​ заношу сюда все​МАКС​

​ а также их​ решения Ваших повседневных​ щелкните по строке​Таблица​Обратная матрица в Excel​ вводных данных достаточно​. Формула будет изменена.​ попытку отредактировать данные​ строку или столбец.​ массива в столбец​ традиционно с ключами​ Мне нужно занести​

• ​ As Long t​ при использовании Timer).​
• ​ значения не используя​ скопированные данные, а​
• ​и​ количество и итоговая​
• ​ задач.​
• ​ формул или нажмите​.​
• ​Как видим, операции с​ нажать кнопку​

​Для удаления формулы массива​ в строке функций.​​Нужно отметить, что алгоритм​

​ «С».​

office-guru.ru>

Редактирование формул массива в Excel

Правила редактирования формул массива

​На рисунке ниже представлены​ количество знаков, включая​Ctrl+Shift+Enter​ минимум, 3 способа​В прошлых уроках мы​ выделите первый аргумент​ работают массивы в​ 0. После этой​​ четыре элемента, о​​ формулу массива. Для​

​​ пользу использования дополнительных​Объединение вычислений в формулу​ материалами на вашем​ C3, B4 с​ единственную формулу. Чтобы​ Затем функция​ товары, цена которых​ пробелы, в диапазоне​

1. ​, чтобы сохранить изменения.​ сделать это:​ разобрали основные понятия​ функции целиком вместе​ формулах. Когда вы​
2. ​ операции первая таблица​ которых я упоминал​ этого при вводе​Для начала определимся как​
3. ​ столбцов для промежуточных​ массива возвращающую массив​ языке. Эта страница​ C4 и т.д.).​
4. ​ воспользоваться формулой массива​МАКС​ указана в евро,​ A1:A10.​
5. ​ Формула будет отредактирована.​Выделить диапазон массива вручную,​ и сведения касаемо​ со знаком сравнения​​ это поймете, сможете​​ удаляется из памяти​

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

Выделение массива в Excel

​В данном примере функция​Очень часто возникает необходимость​ т.е. с помощью​ массивов в Excel.​ «>» и значением​ сказать, что конкретную​ компьютера, и в​Это все хорошо, но​ CTRL+SHIFT+Enter, а не​

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

2. ​ и возвращает из​​ количество и итоговая​​ДЛСТР​ уменьшить или увеличить​ мыши. Это самый​​ В этом уроке​ критерия – «0»).​ формулу следует вводить​​ конечном итоге функция​​ возникают некоторые вопросы:​​ как обычно (просто​​ Это значения, которые​

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

   ​подсчитывает длину каждой​

3. ​ количество ячеек в​​ простой, но в​​ мы продолжим изучение​Нажмите кнопку F9, и​ (подтверждать) сочетанием клавиш​

Как удалить формулу массива

​ ЕСЛИ возвращает массив​ «Зачем же нужна​ Enter):​ находятся внутри фигурных​ массива можно прочитать​ массива (в этом​ грамматические ошибки. Для​ Поэтому, чтобы получить​​ В нашем случае​​ В нашем случае​

Как отредактировать формулу массива

​ В ячейке D9​ текстовой строки из​ формуле массива. Скажу​ ряде случаев абсолютно​ формул массива, но​ вы получите массив​ – CTRL+SHIFT+Enter.​ {23; 0; 15;​ формула массива?» или​

​{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}​ скобок и разделены​

​ в статье Формулы​ случае появляется окно​ нас важно, чтобы​ корректный результат, размерности​ это диапазон D2:D6.​

​ это:​ отображается общая сумма​ заданного диапазона, а​ сразу, что дело​ непригодный способ.​​ с большим уклоном​​ результатов вычислений, как​Конечно же, не подтвержденная,​ 7}. Затем эта​

​ «Как или где​В качестве аргумента функция​

​ между собой точкой​ массива

Знакомство.​​ «Нельзя удалить часть​​ эта статья была​ всех трех массивов​

Изменение размеров формулы массива

​Как и при вводе​Если массив содержит логические​ всего заказа.​ функция​ это не простое​С помощью диалогового окна​ на их практическое​ показано ниже:​ а просто как​ таблица передается в​

​ использовать формулу в​ принимает целый диапазон​ с запятой. Например:​Как узнать, является ли​ массива»)​ вам полезна. Просим​ должны быть соответствующими.​ любой формулы в​ значения, то функции​

​Если изменить форматирование в​СУММ​ и в большинстве​Выделить группу ячеек​ применение. Итак, как​То есть, созданный в​

​ формула также может​ качестве аргумента функции​ массиве?», «Чем она​ $A$1:$A$4. В результате​{23;-32;15;7} – это синтаксис​

​ данная формула формулой​Для некоторых вычислений можно​ вас уделить пару​Использовать в Excel единую​ Excel, первым делом​МАКС​ диапазоне D4:D8, то​– суммирует эти​ случаев будет проще​. Для этого выделите​ же изменить уже​

• ​ памяти компьютера массив:​ возвращать КАКОЙ-ТО результат​
• ​ =СУММ({23; 0; 15;​ лучше обычной формулы?».​
• ​ проверки каждой ячейки​ массива значений в​
• ​ массива? Что вообще​
• ​ написать одну формулу​ секунд и сообщить,​
• ​ многоячеечную формулу массива​ необходимо ввести знак​

​и​ становится видно, что​​ значения.​

​ удалить старый массив​

office-guru.ru>

Как сделать случайную выборку в таблице Excel без повторов

Хотите сделать случайную выборку и являетесь продвинутым пользователем Excel? Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис – Макрос – Редактор Visual Basic), вставим новый модуль через меню Insert – Module и скопируем туда текст вот такой функции:

У этой функции будет три аргумента для создания будущей выборки:

• Bottom – нижняя граница интервала случайных чисел выборки Excel.
• Top – верхняя граница интервала случайных чисел выборки.
• Amount – количество случайных чисел, которое мы хотим отобрать из интервала для нашей выборки.

Т.е., например, чтобы отобрать для выборки 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

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

Ну, а дальше останется при помощи уже знакомой функции ВПР <font>(VLOOKUP)</font> вытащить имена из списка, соответствующие случайным номерам.

Виды массивов функций в Excel

В целом, можно перечислить следующие виды массивов функций в Excel:

1. Одномерные. Их характерная особенность заключается в том, что они включают только ячейки одного ряда или колонки.
2. Двумерные массивы могут содержать огромное количество ячеек как в пределах ячеек или колонок одного листа, так и нескольких.

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

2

В свою очередь, одномерные массивы можно разделить на горизонтальные и вертикальные. Первые включают ячейки одного ряда, а вторые – колонки.

Формулы массива позволяют обрабатывать информацию из всего разнообразия данных.

Виды массивов функций Excel

Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:

В зависимости от расположения элементов различают массивы:

• одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
• двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

• горизонтальными (данные – в строке);
• вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).

Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.

С помощью формул массива реально:

• подсчитать количество знаков в определенном диапазоне;
• суммировать только те числа, которые соответствуют заданному условию;
• суммировать все n-ные значения в определенном диапазоне.

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

Классика жанра – товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

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

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата: