Как работать с большими массивами данных в excel

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

Статья будет разделена на 2 части. Содержание первой части, представлена ниже. Начнем без теории. Вряд ли она вам интересна.

Содержание:

• Как в excel найти повторяющееся значение
• Как в excel быстро удалить дублирующиеся строки
• Работа со сводной таблицей в excel
• Как в excel «подтянуть» данные из другого листа или файла
• Что такое функции правсимв и левсимв и как их применять

Как в excel найти повторяющееся значение

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

Возьмем таблицу. В столбец Е ставим равно и затем, в поиске «Другие функции» ищем нужную нам формулу (см. рис 1)

Для поиска повторяющегося значения, в данном случае, в коде товара по столбцу А, мы будем пользоваться простой формулой  = СЧЕТЕСЛИ

Выделяем весь столбец «А», и в диапазоне аргументов функций ( маленькое голубое окошко посреди экрана), у нас появляется А:А, то есть весь выделенный диапазон по этому столбцу. см. рис 3.

Переходим в окно «критерий», и выделяем только первую строку по коду товара. У нас она отразится, как А2. см. рис. 3.

Далее, нажимаем «ок», и в столбце «Е» появляется цифра 1. Это значит, что по товару 100101200 Молоко Вологодское 1% жирности, только один такой товар, нет дублей. См. рис 5.

«Протягиваем» значения по столбцу «Е» вниз, и мы получаем результат, а именно, какие товары у нас имеют дубль в нашем списке, см рис 6. У нас проявилось 2 одинаковых товара, (их excel обозначил цифрой 2), которые, для наглядности вручную выделил желтым.

Если бы у нас было три одинаковых товара в списке, то excel, соответственно, проставил цифру 3. И так далее. Уже через простой фильтр, можно выделить, все, что больше 1 и увидеть полную картину.

Как в excel удалить дублирующиеся строки

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

Мы воспользуемся функцией, которая уже встроена в панель excel. См. на панели закладку » ДАННЫЕ». Наша функция так и называется «Удалить дубликаты».

Мы выделяем область поиска, у нас это вновь столбец А. См рис 7.

(В более поздней версии excel, можно все находить через поисковое окно.)

Далее нам просто нужно подтвердить удаление. Однако, для наглядности, выделил зеленым те задвоенные строки, которые у нас есть. Это строка 7 и 21. См рис 8.

Теперь на панели жмем кнопку «удалить дубликаты». У нас появляется окошко. Здесь нам автоматически предлагает удалить всю горизонтальную строку, то есть «автоматически расширить выделенный диапазон». Жмем на кнопку «удалить дубликаты». См рис 9

После этого мы видим, что указаны столбцы отмеченные галочками, которые будут удалены по дублирующейся горизонтальной строке. См. рис 10. Мы жмем «ок».

Все. Теперь мы видим окно с оповещением, что дубль в количестве 1 строки был удален. Теперь, на месте 21-ой строки по товару-дублю, появился следующий товар из нижнего списка. См. рис 11.

Исходя из описания, может показаться, что по времени занимает не меньше, чем в первом варианте, но на самом деле это не так. Я просто эту функцию расписал очень подробно.

Как в excel обработать большой объем данных, сводная таблица

Сводная таблица служит для объединения разрозненной информации воедино. Сегодня мы также научимся это делать. Здесь нет ничего сложного. К примеру нам требуется, сколько же у нас есть одного и того же товара, не по брендам, а по виду товара.

Смотрим нашу таблицу. В панели инструментов ищем закладку «ВСТАВКА». Под панелью инструментов, в верхнем левом углу, появляется иконка, которая так и называется «Сводная таблица». см. рис 12.  (Или ищем ее в поиске новой версии excel)

Мы выделяем все столбцы или столбцы интересующих нас значений.

Затем нажимаем на иконку «сводная таблица». У нас выходит окошко, в котором выделен диапазон столбцов. По умолчанию, excel предлагает сводную таблицу вынести на новый лист.  см. рис 13. Мы так и делаем.

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

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

По аналогии, мы ставим галку напротив количества (остатки в шт, склад 1).

При этом, перемещаем данные с количеством не в окно «название строк», а в окно «Значения». см. рис 16

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

Правой клавишей мыши нажимаем на столбец с количеством. См. рис 17. У нас открывается окно, где в строке ИТОГИ ПО, мы ставим галку не по количеству (строк), как на картинке, а по сумме.

Теперь мы получаем именно сведенное количество по каждому товару. См рис 18.

Для сравнения и наглядности, возвращаемся в исходный лист, (см. рис 19) и  мы видим:

одинаковые товары по наименованию, помеченные синим цветом 3+3 = 6 штук.

одинаковые товары, помеченные зеленым 5+56 = 61 штука.

Тоже самое у нас в сводной таблице ( рис 18), 6 и 61 штука.

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

Как в excel подтянуть данные из одного диапазона в другой, с помощью функции ВПР

Будет логичным, если сразу же покажу, как в excel «подтянуть» данные из другого листа или файла, в другой. Для этого есть замечательная функция ВПР. Мы разберем, как пользоваться этим на уже знакомых нам данных.

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

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

Если в наименовании товара есть пробел или точка, (любое отклонение) то для нее это будет уже другое значение.

Также необходимо, что бы все источники были в одном формате. Если мы говорим о числах, то в числовом формате.

Итак, у нас есть исходный файл, на листе 1, (см. рис 20)

Из листа 2, (рис 21) мы будем подтягивать цифры в лист 1. Обратите внимание, что количества на листах разное. Строки также могут быть смещены в списке или перемешаны, поэтому, простым сложением одной цифры с другой нам не обойтись.

Для нас данные на листе 1 те, к которым нужно подтянуть другие значения. Также действуем через знак равно «=». В левом верхнем углу, через поиск других функций, находим ВПР, см рис 22.

Затем, у нас открывается окно и мы выделяем весь столбец А, то есть искомое значение. Оно в новом окне выделяется, как А:А, см рис 23.

Далее, мышкой переходим в самом окошке на вторую строку «таблица», только после этого переходим на лист 2 нашего файла.

И от столбца «А» выделяем и протягиваем к столбцу с количеством. В данном случае, к  столбцу «D», см рис 25.

Столбец D, это четвертый столбец начиная с искомого значения, то есть с кода товара в столбце А.

Поэтому, мы ставим в третьем поле окошка «номер столбца» цифру 4. и в поле «интервальный просмотр» всего ноль. В итоге у нас получается заполненное окошко, см рис 26.

Нажимаем «ок», и получаем подтянутую цифру со второго листа, по коду товара 100101200. см. рис 27.

Протягиваем значение вниз, столбец D заполняется цифрами с листа 2. см. рис 28. Здесь нам остается просто сложить одни цифры с другими простой формулой сложения и протянуть вниз.

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

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

Как в excel обработать большой объем данных, функция правсимв и левсимв

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

Для этого, нам в помощь функция excel: правсимв и левсимв. То есть с помощью этой функции можно слева или справа нашего значения, например наименования товара, убрать лишние знаки.

Итак,, нам нужно взять только часть от полного наименования. Смотрим наш рис 29, к примеру, нам нужно только слово «молоко». Мы также в окне поиска формул ищем = левсимв.

У нас появляется окошко, см рис 30.

Мы выделяем интересующий нас столбец «В», в строке «текст» он появляется как В:В, см рис 31.

Далее, в строку «количество знаков» мы ставим ту цифру, сколько букв или символов содержит слово или слова с пробелом начиная с левой стороны. Если нам нужно только слово «молоко», то в нем, с учетом пробела 7 букв, поэтому, ставим цифру 7. См. рис 32.

Вот и обрезалось наше наименование только в нужное нам слово, см. рис 33.

Теперь остается только «протянуть» вниз, и все значения с первыми 7-ю символами с левой стороны, будут в нашей таблице., см рис 34.

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

Заключение

Я отдельно сделал статью, как в excel вести учет и планирование товарных запасов. Ели интересно, статью можно почитать здесь.

Чтобы не утяжелять прочтение, разделю материал на две части. В следующей части пойдет речь о том, как в excel обработать большой объем данных с помощью функции СЦЕПИТЬ, построения графиков и диаграмм. Как автоматически подсветить значения верхнего или нижнего порога, и как седлать пароль на страницу или всю книгу в excel, и так далее.

Надеюсь материал был полезным, всего Вам хорошего. Успехов!

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Управление массивами в Microsoft Excel

​Смотрите также​ так — можно​ If Next Row​Спасибо!​4. (Фильтр+Range). Результат​- так эта​ — так и​Автор: Антон Андронов​Как видите, сумма до​ подсчитать количество различий​ рабочем листе, не​Найти и выделить​ на их практическое​ (матрицы) и, наоборот,​После того, как пользователь​ и он довольно​

​ ячейки расположены по​Во время работы с​

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

​ выгрузить один одномерный​ Application.DisplayAlerts = True:​Hugo121​ этого алгоритма меня​ переменная и есть​ есть.​master-artyom​ и после округления​ в двух диапазонах:​ меняя его размерность,​выберите пункт​ применение. Итак, как​ количество ячеек в​ в​ прост​ диагонали друг от​ таблицами Excel довольно​ из двух одномерных:​ Application.ScreenUpdating = True​

​: Ну так если​ вообще убил. Суть​

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

​ же изменить уже​ строке должно равняться​Мастере функций​Закройте информационное окно, нажав​ друга. Например, адрес​ часто приходится оперировать​Sheets(«Итог»).[a2].Resize(.Count, 2) =​ ‘:Application.Calculation = xlAutomatic​ умеете взять -​ его заключалась в​

Создание формулы

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

​ с целыми диапазонами​

​ Application.Transpose(Array(.keys, .items))А по​ Range(«J3»).Resize(j, 1) =​ можете ведь аналогично​ том, что сначала​ как Вам хочется.​

​ в массив (данные​ объемом данных заставляет​ всего лишь одна​ Если они равны,​Существует несколько подходов к​Откроется диалоговое окно​ в Excel?​ столбце исходника. Синтаксис​ инструментов выберет наименование​«OK»​ быть таким:​​ данных. При этом​​ горизонтали можно вообще​

​ arr1 Range(«J2»).Value =​ и выгрузить, зачем​ на диапазон с​​А вот переложить​​ диапазона), далее в​

1. ​ задуматься над всевозможными​ копейка.​ функция​ редактированию размеров массива,​Выделить группу ячеек​Когда формула массива помещена​ оператора следующий:​

   

2. ​ конкретного оператора, откроется​.​​A2:A7​​ некоторые задачи подразумевают,​ не мудрить -​​ Timer — t​​ поячеечно заполняли?​ исходными данными устанавливается​ из одного виртуального​ цикле можно делать​ способами оптимизации алгоритма.​На рисунке ниже представлен​ЕСЛИ​ которые, возможно, Вам​. Установите переключатель на​ в одну ячейку,​

Изменение содержимого массива

​=ТРАНСП(массив)​ окно аргументов функции,​Затем нажмете на кнопку​.​ что вся группа​ просто укажите диапазон/размер​ MsgBox «Готово!» End​Тут я показал​ фильтр (Range(«$A:$A»).AutoFilter Field:=1,​ массива отобранные по​ с данными что​ Возьмем простой пример.​ фрагмент таблицы продаж,​возвращает ноль, а​ пригодятся. Подходы приведены​ пункт текущий массив​ то ее редактирование​Урок:​ куда можно вводить​«Отмена»​А пример адреса двумерного​ ячеек должна быть​ выгрузки.​ SubСпасибо!​ ещё фишку для​ Criteria1:=»<>»), который выдает​

​ критерию элементы в​ угодно. Скорость выше​ Допустим мне нужно​​ в которой 1231​​ если не равны​ в данном уроке.​ и нажмите​ в Excel обычно​​Транспонирование матриц в Excel​​ исходные данные для​, которая расположена в​ диапазона выглядит следующим​ преобразована буквально в​[a1:c1].Value = Array(234,​Hugo121​ экономии памяти -​ только непустые ячейки.​ другой массив (или​ перебора ячеек раз​ скопировать из одного​ позиция. Наша задача​

1. ​ – единицу. В​Итак, сегодня Вы научились​​ОК​​ не представляет особой​

   

2. ​Урок:​​ расчета.​​ группе значков слева​ образом:​ один клик. В​ 46, 6789)​: Добрый день.​ не создаём для​ Затем полученный результат​​ в этот же,​​ так в 40.​ файла в другой​ посчитать максимальную продажу,​ итоге получается массив,​ выделять, удалять и​.​ сложности. Здесь главное​

​Как перевернуть таблицу в​Правила ввода и редактирования​ от строки формул,​A2:D7​ Экселе имеются инструменты,​master-artyom​

1. ​Можно определить массив​ результата другой массив,​ заносится в диапазон​ но в его​Watcher_1​ диапазон «A1:A100000». Далее​ которую осуществил заданный​ который состоит из​ редактировать формулы массива,​Текущий массив будет выделен:​ не забыть закончить​ Экселе​ функций, если они​

   

2. ​ и представляет собой​.​​ которые позволяют проводить​​: Hugo, где про​

1. ​ такого же размера,​ а используем тот​ myRange, а далее​ начало, что удобнее)​: Можно так же​ удалить из этого​ продавец.​ нулей и единиц.​ а также узнали​​При помощи комбинации клавиш​​ редактирование комбинацией клавиш​

   

2. ​Функция​ выводят результат сразу​ пиктограмму в виде​Чтобы рассчитать подобную формулу,​ подобные операции. Давайте​

Функции массивов

​ это все можно​ чтоб точно всё​ же.​ двумя циклами (т.к.​ — нет проблем.​ одним SQL запросом​​ диапазона все нули​​Пусть в ячейке G3​​ Затем функция​​ несколько полезных правил​Ctrl+/​Ctrl+Shift+Enter​​МОБР​​ в несколько ячеек,​ крестика. Также можно​ нужно выделить на​ выясним, как можно​ почитать? Я много​

​ поместилось в лоюбом​Watcher_1​​ в результате фильтрации​​master-artyom​ вывести на лист​ и работать с​ мы будем задавать​СУММ​ по работе с​. Для этого выберите​

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

​ ними. Если желаете​​ любую ячейку массива​

Оператор СУММ

​Если же формула многоячеечная,​ обратной матрицы. Все​ что и для​​Esc​​ которую будет выводиться​ в этой программе.​ массивы, но в​ так как в​ выдает от 17​ первым по областям,​ 4 варианта обработки​master-artyom​

​ могу это сделать​

​ формула массива будет​ массива и возвращает​ получить еще больше​ и нажмите комбинацию.​ т.е. возвращает массив,​ правила ввода значений​ обычных формул массива.​​на клавиатуре. После​​ результат, и ввести​Скачать последнюю версию​​ них нет такой​​ примере (зачем брать​ до 22 сек…​

​ вторым по диапазону​​ столбца, содержащего 1​: Спасибо за ответы.​

Оператор ТРАНСП

​ так:​​ выглядеть следующим образом:​​ результат.​ информации о массивах​Самое простое, что Вы​ то сразу возникают​ у этого оператора​ То есть, после​ любой из этих​ в строку формул​ Excel​ детализации.​ с листа), а​НО как быть​​ внутри области, достаются​​ 048 576 строк.​ Что касается массивов,​Тупо ставить в​В данном случае функция​Необходимо, чтобы оба сравниваемых​ в Excel, читайте​ можете сделать с​ определенные трудности, особенно​ точно такие же,​ ввода значения обязательно​ операций произойдет отмена​ выражение для вычисления.​Массив – это группа​Hugo121​ с помощью Redim:​

​ например если в​

​ значения. Общее время​​ Задача состояла в​

​ то на мой​​ новую книгу скопированный​ЕСЛИ​

Оператор МОБР

​ диапазона имели одинаковый​​ следующие статьи:​​ массивом в Excel​ у начинающих пользователей.​ как и у​ нужно установить курсор​ действия, и вы​После ввода следует нажать​ данных, которая расположена​: Так ведь уже​redim arr1(1 to​ исходно таблице не​ — около 400​ том, чтобы выбрать​ взгляд, что работа​ диапазон, поставить фильтр​сравнивает значения диапазона​ размер и ориентацию.​Знакомство с формулами массива​ – это удалить​ Давайте рассмотрим несколько​ предыдущего. Но важно​ в строку формул​ сможете работать с​​ не на кнопку​​ на листе в​ прочитали​

​ ubound(arr), 1 to​

​ 1 столбец а​ секунд.​ все значения, которые​

​ массивом, что с​

​ (выделив только нули),​​ B3:B1234 c заданной​

​Вспомним предыдущий пример и​ в Excel​ его. Для этого​ правил, которые необходимо​ знать, что вычисление​ и набрать сочетание​ листом так, как​Enter​ смежных ячейках. По​Если серьёзно -​ 1)если столбцов неизвестно​ 50 например? Такое​Большая просьба к​ есть в непустых​ диапазоном — одно​ удалить нулевые строчки,​ фамилией. Если фамилии​ попробуем усложнить задачу.​Многоячеечные формулы массива в​ достаточно выделить нужный​ усвоить, прежде чем​ обратной матрицы возможно​ клавиш​ и прежде.​, как обычно, а​

​ большому счету, любую​

lumpics.ru

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

​ не знаю где,​ сколько — можно​ уже не загонишь​ экспертам VBA. Если​ ячейках и записать​ и тоже. Я​ заглать в переменную​ совпадают, то возвращается​ К примеру, требуется​ Excel​ массив и нажать​ начать редактирование массива.​ исключительно в том​

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

​Ctrl+Shift+Enter​Но что делать, если​ набрать комбинацию клавиш​ таблицу можно считать​ я на форумах​ использовать ubound(arr,2)​ в массив Out​ есть возможность, посмотрите​​ результат в отдельный​​ также могу сделать​

​ Set myNewRange =​ сумма продажи, а​ сравнить диапазоны в​Одноячеечные формулы массива в​ клавишу​Нельзя изменять содержимое одной​ случае, если она​.​ действительно нужно удалить​

1. ​Ctrl+Shift+Enter​ массивом, но не​ читал.​Но если данных​ of Memory…​
2. ​ файл. Есть еще​ столбец.​ цикл по всем​ … и вот​
3. ​ если нет –​ Excel, которые имеют​ Excel​Delete​
4. ​ ячейки, содержащей формулу​ содержит равное количество​Урок:​ или изменить формулу​
5. ​. После этого выражение​ каждый из них​master-artyom​ много и массивы​​Hugo121​​ способы сократить время​

​Результат тестирования -​ элементам диапазона и​ тогда уже начать​ЛОЖЬ​ одинаковый размер, но​Массивы констант в Excel​.​ массива. Но к​ строк и столбцов,​Мастер функций в Excel​

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

​ массива? В этом​ в строке формул​ является таблицей, так​: Коллеги, начал пользоваться​ огромные — может​: Можно брать частями​ на выполнения кода?​ поразил. То, что​ удалять из него​

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

   

   ​ЕСЛИ​​ один горизонтальный, а​​ Excel​ формула массива, которая​ применить свое форматирование.​​ определитель не равен​​ функций в Экселе​

   

   ​ нижеуказанные действия.​

   

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

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

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

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

​ является​Для изменения формулы выделите​ а ячейки на​ По своей сущности​ и подсел на​ собирать данные в​ 10000 строк.​ <>»» лучше использовать​ быстро — работало​

​ это не самое​Но проблема в​

1. ​ массив, который состоит​ этом случае на​ массива в Excel​ диапазонов. Из рисунка​ входят в формулу​
2. ​ данную функцию к​СУММ​ курсором, зажав левую​ листе будут заполнены​ такие области могут​​ это, так как​​ коллекцию без ключа​Или брать в​ Len()<>0, т.к. в​
3. ​ очень долго.​ оптимальное решение, т.к.​
4. ​ том, что так​ из сумм продаж​​ помощь придет функция​​Урок подготовлен для Вас​ видно, что при​

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

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

​Итак, были следующие​ элементов может быть​ делать муторно. Не​ указанного продавца и​ ТРАНСП, которая позволяет​ командой сайта office-guru.ru​ вводе формулы мы​ только весь массив.​ количеством строк и​

​ как для суммирования​ диапазон на листе,​ результате вычисления, в​ двумерными (матрицы). В​ Появился еще вопрос.​ нужен всего один​

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

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

• ​ столбец). В финале​ остальное оставлять на​
• ​Спасибо за ответы.​ (файл прилагаю):​
• ​ ощущение, что фильтр​ лист данные и​
• ​ЛОЖЬ​
• ​ формула из прошлого​Автор: Антон Андронов​
• ​ наша задача ее​ входят в формулу​

​ корректного результата на​ так и для​
​ Это очень важно,​

​ диапазона.​

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

Работа с большим объемом данных

​ — индекс ведь​​ непустые значения в​
​ = Timer Application.DisplayAlerts​2. (С помощью​ цикл по всем​ потом нужно как-то​МИН​ стоимость в рублях.​На рисунке ниже представлена​ клавишу​Как видите, все перечисленные​ диапазонами помогают сэкономить​«OK»​ нужно точно так​ При этом появится​ работы с подобными​Спасибо!​
​ не повторится), затем​ другой массив, скажем,​ = True: Application.ScreenUpdating​ массива, он же​ элементам и не​ в диапазоне удалить​их игнорируют.​ В ячейке D9​ формула, которая подсчитывает​F2​ выше правила подчеркивают,​ время при вычислениях,​
​в окне аргументов​ же, как и​ информационное сообщение, в​ диапазонами несколько отличается​Hugo121​ циклом по собранному​ arr1. Как правильно​ = False ‘:Application.Calculation​ диапазон). Сначала весь​ записывая эти элементы​ нули, чтобы получить​Чтобы вывести минимальную продажу,​ отображается общая сумма​
​ количество знаков, включая​. Excel удалит фигурные​ что массив –​ а также свободное​ функции или клавишу​ в предыдущем случае,​ котором будет говориться,​ от более привычных​: Выгружаете почти как​ брать данные непосредственно​ его задать? Единственное,​ = xlManual lastRow​ диапазон с исходными​ на лист, т.е.​ только нужные данные,​
​ воспользуемся этой формулой:​ всего заказа.​ пробелы, в диапазоне​ скобки вокруг формулы​ это одно целое.​ пространство листа, ведь​Enter​ выделить курсором весь​
​ что нельзя изменять​ операций с одиночными​ написали (только resize(j,2)),​ из исходного массива.​ что пришло в​ = Cells(Rows.Count, 1).End(xlUp).Row​ данными заносился в​ некий аналог того,​ а именно, диапазон​
​Данная формула позволяет вывести​

​Если изменить форматирование в​​ A1:A10.​ массива.​ Если не выполнить,​ не нужно дополнительно​, если ввод выполнялся​
​ диапазон ячеек, в​ часть массива. Данное​ ячейками, хотя и​ затем между столбцами​master-artyom​ голову, определить его​ arr = Range(Cells(2,​ массив, а затем​ как работает фильтр​

​ myNewRange. Можно ли​​ 5 наибольших продаж​ диапазоне D4:D8, то​В данном примере функция​Внесите необходимые корректировки в​

​ хотя бы одно​​ суммировать данные, которые​ вручную.​ котором она находится.​ сообщение появится даже​ общего между ними​ вставляете столбец B​: Спасибо! Да, действительно,​ также, как arr,​ 1), Cells(lastRow, 1)).Value​ циклом по всем​ по ячейкам?​ тут использовать что-то​ указанного продавца:​ становится видно, что​ДЛСТР​ формулу:​ из вышеперечисленных правил,​ объединены в диапазон,​Урок:​ Затем нажать на​ в том случае,​ тоже много. Давайте​Ну или выгружаете​ так работает!​
​ т.е. arr1 =​ For Row =​ элементам массива была​Как с помощью​ типа фильтра? Так​Итак, в данном уроке​ значения в этих​подсчитывает длину каждой​А затем нажмите комбинацию​ Excel не даст​ для последующей работы​Как посчитать сумму в​ кнопку​
​ если у вас​ рассмотрим нюансы подобных​ один столбец, затем​С коллекциями пока​ Range(Cells(2, 1), Cells(lastRow,​ 2 To lastRow​ проверка на непустое​ SQL запроса загнать​ как фильтр, на​ мы рассмотрели несколько​ ячейках не округлены,​ текстовой строки из​ клавиш​ отредактировать массив и​ с ними. Все​ Экселе​Delete​ не было цели​

​ операций.​​ перекладываете данные массива​ не работал. Нужно​
​ 1)).Value. Насколько это​ — 1 If​ значение. Общее время​ результат в переменную​ мой взгляд, работает​
​ интересных примеров применения​ а всего лишь​ заданного диапазона, а​Ctrl+Shift+Enter​ выдаст следующее предупреждение:​
​ это выполняется «на​Функция​на клавиатуре.​ производить какие-либо изменения,​Формула массива – это​ из второго столбца​ будет почитать.​ будет оптимально? Есть​ arr(Row, 1) <>​

​ — около 6​​ типа Range пока​ довольно быстро.​ формул массива в​ визуально отформатированы. Соответственно,​ функция​, чтобы сохранить изменения.​Если необходимо изменить формулу​ лету». А для​ТРАНСП​После этого формула будет​ а вы просто​
​ выражение, с помощью​ в первый, снова​А вот по​ ли еще способы.​ «» Then j​ секунд.​
​ не понятно, но​Если это делать​ Excel. Надеюсь, что​
​ мы не можем​СУММ​ Формула будет отредактирована.​ массива, то первое,​ преобразования таблиц и​является типичным оператором​ удалена со всей​ случайно дважды щелкнули​ которого производится обработка​ выгружаете один столбец.​ поводу массивов методом​Пример кода:​
​ = j +​3. (С помощью​ идея интересная. Может​ поиском, типа, Set​ они были для​ быть уверенны в​– суммирует эти​Очень часто возникает необходимость​ что нужно сделать​ матриц только функции​ массивов. Она позволяет​ области. Теперь в​
​ мышью по ячейке​ диапазона с целью​А я обычно​ проб и ошибок​Sub Вариант22() Dim​ 1 arr(j, 1)​ Find в Range).​ подскажите в плане​ cell = myAllRange.Find(«0»,​ Вас полезны и​ том, что сумма​ значения.​ уменьшить или увеличить​ – это выделить​ массивов и подходят,​ переворачивать таблицы или​ неё можно будет​ диапазона.​ получения итогового результата,​ в таких случаях​ заметил такую вещь:​
​ arr Dim arr1​ = arr(Row, 1)​ Сначала весь диапазон​ реализации? Заносить полученные​ , xlValues), а​ обязательно пригодятся в​ в ячейке D9​Следующая формула возвращает 3​ количество ячеек в​ диапазон, в котором​ так как обычные​ матрицы, то есть,​ вводить любые данные.​Если вы закроете, это​ отображаемого цельным массивом​ сразу завожу два​Если нужно загнать​ Dim Row As​ End If Next​ с исходными данными​ значения на лист​ потом удалять все,​
​ будущем. Если желаете​ является точной.​ наибольших значения диапазона​ формуле массива. Скажу​ содержится массив. В​ формулы не в​ менять строки и​Наиболее удобно в качестве​ сообщение, нажав на​ или в одной​ массива, чтоб потом​ диапазон в массив​
​ Long t =​

​ Row Application.DisplayAlerts =​​ заносился в переменную​ — не хочу,​ что было найдено,​ получить еще больше​В Excel существует, как​
​ A1:D6​ сразу, что дело​ Excel существует, как​
​ силах справиться с​ столбцы местами. При​ формул использовать уже​ кнопку​ ячейке. Например, для​ время не терять.​ или из массива​ Timer Application.DisplayAlerts =​ True: Application.ScreenUpdating =​ типа Range (пусть​ т.к. 1. это​ то это будет​ информации о массивах,​ минимум, два способа​Чтобы возвратить другое количество​ это не простое​ минимум, 3 способа​ подобными задачами. Но​ этом она использует​ готовые встроенные функции​«OK»​ того, чтобы умножить​master-artyom​ в диапазон (без​ True: Application.ScreenUpdating =​ True ‘:Application.Calculation =​ будет myRange), затем​

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

​, а потом попытаетесь​​ один диапазон на​: Понятно. Я думал,​ использования цикла), тогда​ False ‘:Application.Calculation =​ xlAutomatic Range(«J3»).Resize(j, 1)​
​ использовалась функция .Find(«*»,​ нужно от пользователя​Как решить эту​Знакомство с формулами массива​Ввести в ячейки D4:D8​ изменить массив констант.​ случаев будет проще​

​Выделить диапазон массива вручную,​​ время нужно учесть,​ в диапазон ячеек,​ ним можно получить​
​ переместить курсор с​ второй применяют формулу​ что может быть​ массив должен быть​ xlManual lastRow =​ = arr Range(«J2»).Value​ LookIn:=xlValues) — аналог​ скрывать эти промежуточные​

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

​ по следующему шаблону:​​ можно как-то из​ минимум двумерный (т.е.​ Cells(Rows.Count, 1).End(xlUp).Row arr​ = Timer -​ кнопки «Найти», которая​ вычисления, а это​ Может есть какие-то​Многоячеечные формулы массива в​ Формула массива будет​ возвращает уже 6​ и создать новый.​ мыши. Это самый​ выражениям применяются дополнительные​ данного оператора обязательно​Мастер функций​ просто нажмете кнопку​=адрес_массива1*адрес_массива2​ двумерного массива достать​ с указанием столбца).​ = Range(Cells(2, 1),​ t MsgBox «Готово!»​ искала непустые значения​
​ тоже напрягает. Хочет​
​ общие рекомендации или​ Excel​ выглядеть следующим образом:​ наибольших значений того​Прежде чем удалять старый​ простой, но в​ правила ввода и​ нужно применять сочетание​, нажав кнопку​«Enter»​Над диапазонами данных можно​ одномерный не перезаписывая​ А вот одномерный​ Cells(lastRow, 1)).Value arr1​ End Sub​ в myRange. Общее​ сделать красиво и​ принципы по работе​Одноячеечные формулы массива в​Использовать в ячейке D9​ же диапазона:​ массив, скопируйте его​ ряде случаев абсолютно​ редактирования.​Ctrl+Shift+Enter​«Вставить функцию»​, то информационное сообщение​ также выполнять операции​ циклом. Так как​ массив уже так​

​ = Range(Cells(2, 1),​​master-artyom​
​ время — около​ с максимальной скоростью.​ с большим объемом​ Excel​ формулу массива, которая​Если необходимо найти наименьшие​ формулу как текст,​ непригодный способ.​Автор: Максим Тютюшев​
​. Также нужно отметить,​слева от строки​ появится опять. Не​ сложения, вычитания, деления​ проблема в том,​
​ не сработает, несмотря​ Cells(lastRow, 1)).Value For​: Вау! Вот это​ 15 секунд. Т.е.​
​Hugo121​ данных в Excel​Массивы констант в Excel​ сначала округляет значения,​ значения, просто замените​ а затем используйте​С помощью диалогового окна​В прошлых уроках мы​ что перед введением​ формул. Или же​ получится также закрыть​ и другие арифметические​ что я заведомо​ на то, что​
​ Row = 2​ реально супер!!! Другой​ получается, что встроенная​: вот тут не​ (и пока только​Редактирование формул массива в​ а затем суммирует​ функцию​ ее в новом​

​Выделить группу ячеек​​ разобрали основные понятия​ самого выражения нужно​
​ во вкладке​ окно программы или​ действия.​
​ не знаю скольки​ из диапазона загоняется​ To lastRow -​ способ занесения данных​
​ функция Find ищет​ понял.​ в Excel)?​ Excel​ их.​НАИБОЛЬШИЙ​ массиве. При громоздких​. Для этого выделите​ и сведения касаемо​ выделить на листе​«Формулы»​ сохранить документ. Все​Координаты массива имеют вид​ мерный массив получится,​ один столбец с​

​ 1 If arr(Row,​​ из массива в​ непустые ячейки, примерно,​А вообще что​Спасибо!​Подходы к редактированию формул​Теперь мы можем быть​
​на​ формулах такой подход​ любую ячейку, которая​ массивов в Excel.​ область, у которой​на ленте можно​
​ время будет появляться​ адресов первой её​

​ поэтому по сути​​ данными.​ 1) <> «»​ ячейки и сразу​ в 3 раза​ спорить — возьмите​Hugo121​ массива в Excel​

​ уверенными в том,​​НАИМЕНЬШИЙ​ позволит сэкономить уйму​
​ принадлежит массиву:​ В этом уроке​ количество ячеек в​ выбрать одну из​

​ это назойливое сообщение,​​ ячейки и последней,​ мне нужно выгрузить​Hugo121​ Then j =​ скорость увеличилась в​ дольше, чем сделать​ и проверьте, сравните​: Используйте массив, если​Урок подготовлен для Вас​ что сумма в​.​ времени.​
​А затем на вкладке​ мы продолжим изучение​ столбце будет равно​ категорий, в которой​ которое блокирует любые​ разделенные двоеточием. Если​ данных из n-мерного​: Одномерный по вертикали​ j + 1​
​ 5 раз для​

​ это полным перебором.​​ время перебора массива​ нужны только данные.​ командой сайта office-guru.ru​ ячейке D9 соответствует​
​Формула массива, представленная на​Если же необходимо изменить​ Главная из раскрывающегося​ формул массива, но​ числу ячеек в​ находится интересующий вас​
​ действия. А выход​ диапазон двумерный, то​ массива в определенные​ сработает используя application.transpose,​ arr1(j, 1) =​

​ данного примера.​​ Очень удивился.​ и диапазона/ячеек.​ Судя по высказанному​Перевел: Антон Андронов​ действительности.​ рисунке ниже, позволяет​ расположение массива на​ списка​ с большим уклоном​ строке исходной таблицы​ оператор.​ из ситуации есть​ первая и последняя​ столбцы (не попорядку).​ вот например даже​

CyberForum.ru

​ arr(Row, 1) End​

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

1. Выделяем диапазон Е3:Е8.
2. В строку формул вводим следующую формулу: =C3:C8*D3:D8.



3. Нажимаем одновременно клавиши: Ctrl + Shift + Enter. Промежуточные итоги посчитаны:

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

Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» — ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:

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

1. Выделяем ячейку Е9 (напротив «Итого»).
2. Вводим формулу вида: =СУММ(C3:C8*D3:D8).
3. Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:

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

Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.

Рассмотрим ее синтаксис:

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

Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.

1. Посмотрим, как работает оператор «И» в функции массива. Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.



2. Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.



3. Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.



4. Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: . Получаем:

Скачать примеры массива функций

Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.

Терминология

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

Формулы массива в Excel — это специальные формулы для обработки данных из таких массивов. Формулы массива делятся на две категории — те, что возвращают одно значение и те, что дают на выходе целый набор (массив) значений. Рассмотрим их на простых примерах…

Пример 1. Классика жанра — товарный чек

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

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

   

6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

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

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

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

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

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

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

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

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

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

• Формула массива для извлечения непустых ячеек из диапазона
• Формула массива для извлечения уникальных ячеек из диапазона
• Формула массива для извлечения данных из списка (многоразовый ВПР)

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

Методы переноса данных в Excel могут быть различны:

• Копирование-вставка результатов запросов
• Использование стандартных процедур импорта (например, Microsoft Query) для формирования данных на рабочих листах
• Использование программных средств для доступа к базам данных с последующим переносом информации в диапазоны ячеек
• Непосредственный доступ к данным без копирования информации на рабочие листы
• Подключение к OLAP-кубам

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

Обработка этих данных в Excel может вестись различными методами. Выделим основные способы работы:

1. Обработка данных стандартными средствами интерфейса Excel
2. Анализ данных при помощи сводных таблиц и диаграмм
3. Консолидация данных при помощи формул рабочего листа
4. Выборка данных и заполнение шаблонов для получения отчета
5. Программная обработка данных

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

В данной статье будут рассмотрены способы консолидации и выборки данных при помощи стандартных формул Excel. 

Описание примеров

Примеры к статье построены на основе демонстрационной базы данных, которую можно скачать с сайта Microsoft

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19704

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_sums.xls используется для версий Excel 2000-2003

Файл nwdata_sums.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

Применение метода

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

Проблема при консолидации данных при помощи сводных таблиц появляются, если предполагается дальнейшая работа с этими агрегированными данными. Например, сравнить или дополнить данные из двух разных сводных таблиц (как вариант: объемы продаж и прайс листы). В таком случае обычно прибегают к методу копирования значений из сводных таблиц в промежуточные диапазоны с дальнейшим применением формул поиска (VLOOKUP/HLOOKUP). Очевидно, что проблема возникает при обновлении исходных данных (например, при добавлении новых строк) – требуется заново копировать результаты консолидации из сводной таблицы. Другим, с нашей точки зрения, не совсем корректным методом решения является применение функций поиска непосредственно к диапазонам, которые занимают сводные таблицы. Это может привести к неверному поиску при обновлении не только данных, но и внешнего вида сводной таблицы.

Еще один классический пример непригодности применения сводной таблицы – это требование формирования отчета в заранее предопределенном виде («начальство требует в такой форме и никак иначе»). Возможностей настройки сводной таблицы зачастую недостаточно для предоставления произвольной формы. В данном случае пользователи также обычно используют копирование результатов агрегирования в качестве значений.

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

Суммирование по одному ключевому полю

Таблицы с формулами на листе SUM показывают вариант решения задачи консолидации данных по одному ключевому значению.

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

SUM!B5

=SUMIF(data!$H:$H;A5;data!$M:$M) 

SUM!B11

=SUMIF(data!$Z:$Z;A11;data!$M:$M) 

Нижние таблицы показывают возможности другой редко используемой функции DSUM

SUM!B19

=DSUM(data!$A$1:$AJ$2156;"Quantity";D18:D19) 

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

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

SUM!B28

=DSUM(data!$A$1:$AJ$2156;"Quantity";D27:D28)

SUM!D28

Здесь data!Z2 означает ссылку на текущую строку данных, а не на конкретную ячейку, так как используется относительная ссылка. К сожалению, нельзя указать в третьем параметры ссылку на одну ячейку – строка заголовка полей все равно требуется, хотя и может быть пустой.

В принципе, функции типа DSUM являются устаревшим методом работы с данными, в подавляющем большинстве случаев лучше использовать SUMIF, SUMPRODUCT или формулы обработки массивов. Но иногда их применение может дать хороший результат, например, при совместном использовании с интерфейсной возможностью «расширенный фильтр» – в обоих случаях используется одинаковое описание условий через дополнительные диапазоны.

Суммирование по нескольким критериям

Таблицы с формулами на листе SUM2 показывают вариант суммирования по нескольким критериям.

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

SUM!D5

=SUMIF(A:A;B5 & ";" & C5;data!M:M) 

Операция «&» используется для соединения строк. Можно также вместо этого оператора использовать функцию CONCATENATE. Промежуточный символ «;» (или любой другой служебный символ) необходим для обеспечения уникальности сцепленных строковых значений.

Пример: Есть, если два поля с перечнем слов. Пары слов «СТОЛ»-«ОСЬ» и «СТО»-«ЛОСЬ» дают одинаковый ключ «СТОЛОСЬ». Что соответственно даст неверный результат при консолидации данных. При использовании служебного символа комбинации ключей будут уникальны «СТОЛ;ОСЬ» и «СТО;ЛОСЬ», что обеспечит корректность вычислений.

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

Второй пример – это популярный вариант использования функции SUMPRODUCT с проверкой условий в виде логического выражения:

SUM!D13

=SUMPRODUCT((data!$H$2:$H$3000=B13)*(data!$Z$2:$Z$3000=C13)*data!$M$2:$M$3000) 

Обрабатываются все ячейки диапазона (data!$M$2:$M$3000), но для тех ячеек, где условия не выполняются, в суммирование попадает нулевое значение (логическая константа FALSE приводится к числу «0»). Такое использование этой функции близко по смыслу к формулам обработки массива, но не требует ввода через Ctrl+Shift+Enter.

Третий пример аналогичен, описанному использованию функций DSUM для листа SUM, но в нем для диапазона условий использовано несколько полей.

SUM!D21

=DSUM(data!$A$1:$AJ$2156;"Quantity";F20:G21) 

Четвертый пример – это использование функций обработки массивов.

SUM!D32

{=SUM(IF(data!$H$2:$H$3000=B32;IF(data!$Z$2:$Z$3000=C32;data!$M$2:$M$3000)))} 

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

Пятый пример содержится только в файле формата Excel 2007 (xlsx). Он показывает возможности новой стандартной функции

SUMIFS

SUM!D40

=SUMIFS(data!$M$2:$M$3000;data!$H$2:$H$3000;B40;data!$Z$2:$Z$3000;C40) 

Поиск по одному критерию

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

Первый вариант – это использование популярной функции VLOOKUP.

SEARCH!B5

=VLOOKUP(A5;data!$H$1:$M$3000;6;0) 

Во втором вариант использовать VLOOKUP нельзя, так как результирующее поле находится слева от искомого. В данном случае используется сочетание функций MATCH+OFFSET.

SEARCH!C13

=MATCH(A13;data!$Z$1:$Z$3000;0) 

SEARCH!B13

=OFFSET(data!$M$1;C13-1;0) 

Первая функция ищет нужную строку, вторая возвращает нужное значение через вычисляемую адресацию.

Поиск по нескольким критериям

Таблицы с формулами на листе SEARCH2 предназначены для поиска по нескольким ключевым полям.

В первом варианте используется техника использования служебного столбца, описанная в примере к листу SUM2:

SEARCH2!Е5

=VLOOKUP(C5 & ";" & D5;$A$1:$B$3000;2;0) 

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

SEARCH2!Е 12

{=OFFSET(data!$M$1;MATCH(C13 & ";" & D13; data!$H$1:$H$3000 & ";" & data!$Z$1:$Z$3000;0)-1;0)} 

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

Выборка по одному критерию

Таблица на листе SELECT показывает вариант фильтрации данных через формулы.

Предварительно определяется количество строк в выборке:

SELECT!С4

=COUNTIF(data!$H:$H;$A$5) 

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

SELECT!С5

=MATCH($A$5;data!$H$1:$H$3000;0) 

Вторая и последующие строки ищутся в вычисляемом диапазоне с отступом от предыдущей найденной строки:

SELECT!С6

=MATCH($A$5;OFFSET(data!$H$1;C5;0; ROWS(data!$H$1:$H$3000)-C5;1);0)+C5

Результат выдается через функцию вычисляемой адресации:

SELECT!B6

=IF(ISNA(C6);"";OFFSET(data!$M$1;C6-1;0)) 

Вместо функции проверки наличия ошибки ISNA можно сравнивать текущую строку с максимальным количеством, так как это сделано в столбце A.

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

Выборка вариантов

Самый сложный вариант выборки по ключевому полю представлен на листе SELECT2. Формулы сами определяют все доступные ключевые значения второго критерия.

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

SELECT2!B2

=IF(LEFT(A2;LEN($D$5)) & ";" = $D$5 & ";"; data!Z2;"") 

Третий служебный столбец проверяет значение второго ключа на уникальность:

SELECT2!C2

=IF(B2="";0;IF(ISNA(MATCH(B2;B$1:B1;0));COUNTIF(C$1:C1;">0")+1;0)) 

Результирующий столбец второго ключа ProductName ищет уникальные значения в служебном столбце C:

SELECT2!E5

=IF(ISNA(MATCH(ROWS($5:5);$C$1:$C$3000;0));"";OFFSET($B$1;MATCH(ROWS($5:5);$C$1:$C$3000;0)-1;0)) 

Столбец Quantity просто суммирует данные по двум критериям, используя технику, описанную на листе SUM2.

SELECT2!F5

=IF(E5="";"";SUMPRODUCT((data!$H$2:$H$3000=D5)*(data!$Z$2:$Z$3000=E5)*data!$M$2:$M$3000)) 

Заключение

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