Как выбрать каждый n-й столбец в Excel?

Exceltip

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

Что такое массив?

В Excel матрица — это диапазон ячеек. Вот несколько примеров. Таблицы в Excel могут быть двумерными или одномерными. Одномерные таблицы делятся на горизонтальные и вертикальные.

Формулы массивов — это обычные формулы Excel с AMOUNT, MAX, ACCOUNT (сумма, максимум, счет). В качестве входных данных используется массив или набор массивов (а не один массив). На этом принципе основано множество формул, поэтому они так мощны.

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

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

Результатом в данном случае является адрес ячейки с наименьшим значением в диапазоне. Как вы можете видеть, результат также изменяется при обновлении данных в диапазоне. Вы также можете получить аналогичный эффект, используя обычную формулу =ADDRESS(MIN(A1:A9);A1:A9;0);1). Но мы только начали изучать это, и позже вы увидите, что некоторые вещи можно сделать только с помощью формулы массива или более традиционных формул.

Части формул массивов в Excel

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

(Прежде чем продолжить, убедитесь, что при вводе матричных формул вы набираете Ctrl +Shift +Enter, а не обычный Enter, как в случае с обычными формулами).

Массив констант в формулах массивов

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

Горизонтальная матрица констант

Горизонтальный массив констант вводиться как последовательность чисел, разделенных точкой с запятой (;), заключенных в фигурные скобки. Например: <1;2;3;4;5>. Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

Набор констант для вертикального расположения

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: <1:2:3:4:5>.

Операторы массива в формулах массивов

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Операторы массива I

Оператор AND возвращает TRUE, когда все условия выражения возвращают TRUE. В следующем примере показано использование оператора AND (*) между массивами:

Матричный оператор ИЛИ

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

Что такое диапазон массива?

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

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

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

Сортировка по формуле массива

Предположим, у вас есть набор данных в ячейках D2:D10, и вы хотите отсортировать их в порядке возрастания. Вы, наверное, уже догадались, что нам нужна функция LOWEST(), которая возвращает n-ое наименьшее значение и заданный диапазон. Нам также нужен диапазон, в котором будет производиться расчет.

Для одной ячейки LOWEST задается формулой =LOWEST(D2:D10;1). Эта формула возвращает наименьшее значение между D2 и D10. Однако мы должны скопировать эту функцию для всех ячеек и изменить второй аргумент для сортировки. Сначала выделим диапазон, в котором мы хотим видеть список, затем введем формулу в первую ячейку и нажмем Ctrl + Shift + Enter. Будет создан отсортированный список с формулой, скопированной на весь диапазон.

Поиск уникального значения, удовлетворяющего определенным условиям

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Вот где обнаруживается истинная мощь формул массивов. Ели бы мы использовали обычные формулы, нам понадобилось бы столько же строк, сколько менеджеров, если не больше. Однако мы можем сделать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем здесь – это сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращает номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью функции СМЕЩ возвращаем имя из этой строки.

Объединение данных более чем по одному условию

Мы также можем использовать формулу массива, чтобы найти сумму продаж менеджера с максимальными продажами. Функция IF возвращает массив с индивидуальными суммами продаж менеджера, соответствующего менеджеру с максимальными продажами, иначе 0. Затем мы используем функцию SUM, чтобы сложить все эти значения массива.

Еще один пример консолидации данных по условиям

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

Мы можем достичь этого с помощью формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0)). Так что же мы здесь сделали? Мы проверили три условия – первые два были скомбинированы с помощью оператора И (*) и третье было добавлено с помощью оператора ИЛИ (+). В результате получилась структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из четвертого столбца, когда оно принимает значение ИСТИНА и 0, если ЛОЖЬ. Результат, конечно, представляет собой сумму продаж отвечающим всем трем, указанным выше, условиям.

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

Суммирование каждой 2-й, 3-й. N-й ячейки

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

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

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

Это становится скучным, если количество городов в таблице превышает пару десятков, и вы легко можете ввести формулу неправильно. Так что же делать? Есть способы получше.

Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию

Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :

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

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

Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки

Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:

Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:

• Функция ROW выдает номер строки для каждой ячейки в диапазоне B2:B15.
• Функция MOD вычисляет остаток от каждого числа ряда, деленного на 2.
• Функция IF проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратный 2), то выводит содержимое следующей ячейки, иначе — ничего.
• Наконец, функция SUM суммирует набор значений, выведенных IF, т.е. складывает все 2 числа в диапазоне.
• Эта формула должна быть введена как формула массива, т.е. после ее ввода нажмите Ctrl+Alt+Enter вместо Enter. Вам не нужно набирать скобки на клавиатуре, они будут добавлены в формулу автоматически.

Для ввода, отладки и общего понимания таких формул можно использовать следующий прием: если выделить фрагмент сложной формулы и нажать F9, Excel вычислит выделенную формулу прямо в строке формул и отобразит результат. В следующем примере, если выделить функцию СТРОКА(B2:B15) и нажать F9, мы увидим количество строк для каждой ячейки нашего диапазона:

Выберите фрагмент ESTAT(B2:B15;2) и нажмите F9, чтобы увидеть массив результатов функции ESTAT. остатков от деления номеров рядов на 2:

Наконец, если вы выделите фрагмент IF(FROST(FROST(B2:B15);2)=0;B2:B15) и нажмете F9, вы увидите, что на самом деле добавляет функция SUM в нашу формулу:

Значение FALSE в этом случае интерпретируется Excel как ноль, поэтому в итоге мы получаем сумму всех остальных чисел в нашем столбце.

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

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

Способ 3. Функция БДСУММ и таблица с условием

Формулы массива из предыдущего способа — штука красивая, но имеют слабое место — быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой — функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно — создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) — не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :

Способ 4. Суммирование каждой 2-й, 3-й. N-й строки

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

Благодаря тому, что SUMPRODUCT автоматически преобразует свои аргументы в массивы, вам даже не нужно нажимать Ctrl+Shift+Enter.

Разбить столбец по количеству символов

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

Разделите колонку пополам, оставив первые 6 символов и перенеся остальные в следующую колонку.

Например, процедуры отличаются от формул, которые можно трактовать как «за» и «против» в зависимости от ситуации. Здесь формулы отличаются друг от друга:

• Исследование по столбцам не сохраняет исходный столбец
• Это одноразовая процедура, в отличие от формул, которые пересчитываются при обновлении данных
• Практичность — одна процедура создает сразу несколько столбцов
• Направление исследования — только правая сторона
• Если в правой стороне есть данные, они могут быть перезаписаны, если операция не отменена и не создано один или несколько пустых столбцов.

Сумма последних n столбцов

=СУММ ( ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные ) — (n — 1 )) : ИНДЕКС( данные ; 0 ; СТОЛБЕЦ( данные )))

При подсчете последних n столбцов таблицы данных (т.е. последние 3 столбца, последние 4 столбца и т.д.) можно использовать формулу, основанную на функции ИНДЕКС.

В показанном примере формула находится в К5:

где «данные» является именованный диапазон С5: H8.

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

Чтобы создать ссылку на «последние n столбцы» в таблице, мы делим ссылку на две части, соединенных оператором диапазона. Для того, чтобы получить ссылку на левый столбец, мы используем:

Данные состоят из 6 столбцов, а K4 содержит 3 столбца, поэтому это упрощает расчет:

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

Который рассчитывает ссылку на столбец 6 названного диапазона «данные», так как функция СТОЛБЕЦ рассчитывает 6:

Вместе эти две функции INDEX вычисляют ссылки на столбцы с 4 по 6 данных (т.е. F5: H8), которые могут быть собраны в массив значений в функции SUM:

1. Первый способ — самый простой и примитивный заключается в том, чтобы выделить ненужные столбцы или строчки (сквозным выделением) и нажатием правой кнопки мыши в контекстном меню выбрать действие «Скрыть».
   Показать скрытые строки или столбцы можно выделив нужный диапазон и выбрав в контекстном меню действие «Показать».
2. Второй способ немного сложнее. Данный способ заключается в группировке строк или столбцов при помощи опции «Группировать» во вкладке «Данные» меню быстрого доступа. Эта опция позволяет разбить информацию на группы и открывать или прятать строки(столбцы) нажатием(кликом) по крестику. Преимущество этого метода в том, что можно создавать несколько уровней группировки и отображать при необходимость данные только определенного уровня. То есть информация открывается ступенчато в виде «древа».

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

• Необходимо выделить столбцы или строки сквозным выделением (для каждой группы отдельно);
• Кликнуть по пиктограмме «Группировка» в разделе «Данные». Информация будет сгруппирована автоматически.

Если Вам недостаточно автоматической группировки, можете использовать опцию «Создание структуры…».

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

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