Операции с формулами массивов в Excel 2007

Для решения данной задачи создадим пример из двух таблиц - Руководители и Компании.

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

В таблице Руководители в ячейку G2 (первая запись таблицы) вводим функцию ВПР (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

Искомое_значение - делаем ссылку на ячейку, в которой содержится название компании в таблице Руководители (которое идентично названию компании в первом столбце таблицы Компании).

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

Номер_столбца - указываем порядковый номер столбца в таблице Компании, данные которого, мы хотим видеть в таблице Руководители

Интервальный_просмотр - может иметь только два значения:

• А) ИСТИНА - поиск в таблице будет проводиться по приблизительному совпадению;
• Б) ЛОЖЬ - поиск в таблице будет проводиться по точному совпадению.

Нажимаем кнопку ОК. С помощью функции Автозаполнение копируем функцию на все строки таблицы.

Вставляем функцию ВПР нужное количество раз, изменяя только номер столбца, который должен отображаться в таблице Руководители. В результате мы получим вот такую таблицу: Как в Экселе перевернуть таблицу из столбика в строчку? Транспонировать таблицу (т.е. развернуть таблицу поменяв местами столбцы и строки) можно двумя способами. 1. С помощью функции ТРАНСП. Она позволяет сохранять связь между таблицами. Таким образом, меняя данные в исходной таблице - они автоматически будут изменены и в транспонированной. Выделяем диапазон, в который будет транспонирована таблица. Диапазон нужно выделять такого же размера, каким будет перевернутая таблица, т.е. если у вас имеется таблица в два столбца и три строчки, то соответственно нужно выделить три столбца и две строчки. Вводим функцию ТРАНСП (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

В поле Массив укажите диапазон ячеек, который нужно транспонировать. Нажмите ОК. В ячейке будет отображаться ошибка формулы #ЗНАЧ!, нажимаем F2, а затем CTRL+SHIFT+ENTER. Благодаря этим действиям формула преобразовалась в формулу массива и весь выделенный диапазон заполнился данными, а в строке формул вы увидите фигурные скобки.

Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.

2. С помощью опции Специальная вставка - Транспонировать. Данный способ транспонирует (переворачивает) таблицу без создания связей с исходной таблицей.

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

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

С этой целью можно использовать формулу массива и промежуточные итоги.

Формула массива.

Предположим у нас есть таблица со следующими данными:

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

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

В нем мы кликаем дважды по функции СУММ и начинаем вводить аргументы:

А) открываем скобку, выделяем диапазон ячеек, которые содержать названия магазинов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;

Б) ставим знак умножить "*", открываем скобку и выделяем диапазон ячеек, который содержит марки телефонов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;

В) ставим знак умножить "*", открываем скобку и выделяем диапазон ячеек, который содержит модель телефонов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;

Г) ставим знак умножить "*", выделяем диапазон, который содержит выручку, закрываем скобку;

Д) нажимаем CTRL+SHIFT+ENTER - формула заключается в фигурные скобки, что и делает ее формулой массива.

Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.

В результате у нас просуммирована выручка по телефонам Samsung Е2121, которые проданы в Магазине №1

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

Функция Промежуточные итоги.

Ее можно использовать для вычислений данных отобранных с помощью Автофильтра. В конце таблицы в столбцах, в которых необходимо провести вычисления, вводим функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).

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

В поле Ссылка1 - выделяем диапазон ячеек, значения которых должны вычисляться.

Нажимаем ОК.

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

Рекомендуем в повседневной деятельности: