Операции с формулами массивов в Excel 2007
| Автор: | Индык Игорь Викторович |
| e-mail: | exelentc@yandex.ru |
У меня есть две таблицы - одна с руководителя компаний и их контактными данными, а другая с самими компаниями и с их данными как юридических лиц. Можно ли в Эксель автоматически совместить эти две таблицы, что бы не заниматься копипаст по каждой копании?
Вы можете воспользоваться функцией ВПР.
Для решения данной задачи создадим пример из двух таблиц - Руководители и Компании.
Таблицу, которая имеет уникальные, не повторяющиеся значения (в нашем примере таблица Компании) необходимо предварительно отформатировать. В качестве первого столбца должно быть значение, которое является идентификатором и значение которого будет искомым в основной таблице (в данном случае Сокращенное название компании). Значение обязательно должно быть отформатировано по возрастанию!
В таблице Руководители в ячейку G2 (первая запись таблицы) вводим функцию ВПР (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).
Искомое_значение - делаем ссылку на ячейку, в которой содержится название компании в таблице Руководители (которое идентично названию компании в первом столбце таблицы Компании).
Таблица - выделяем полностью таблицу Компании и нажимаем кнопку F4 или же проставляем в ручную значки $, что бы ссылка стала абсолютной.
Номер_столбца - указываем порядковый номер столбца в таблице Компании, данные которого, мы хотим видеть в таблице Руководители
Интервальный_просмотр - может иметь только два значения:
- А) ИСТИНА - поиск в таблице будет проводиться по приблизительному совпадению;
- Б) ЛОЖЬ - поиск в таблице будет проводиться по точному совпадению.
Нажимаем кнопку ОК. С помощью функции Автозаполнение копируем функцию на все строки таблицы.
Вставляем функцию ВПР нужное количество раз, изменяя только номер столбца, который должен отображаться в таблице Руководители. В результате мы получим вот такую таблицу:
Как в Экселе перевернуть таблицу из столбика в строчку?
Транспонировать таблицу (т.е. развернуть таблицу поменяв местами столбцы и строки) можно двумя способами.
1. С помощью функции ТРАНСП. Она позволяет сохранять связь между таблицами. Таким образом, меняя данные в исходной таблице - они автоматически будут изменены и в транспонированной.
Выделяем диапазон, в который будет транспонирована таблица. Диапазон нужно выделять такого же размера, каким будет перевернутая таблица, т.е. если у вас имеется таблица в два столбца и три строчки, то соответственно нужно выделить три столбца и две строчки.
Вводим функцию ТРАНСП (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).
В поле Массив укажите диапазон ячеек, который нужно транспонировать. Нажмите ОК. В ячейке будет отображаться ошибка формулы #ЗНАЧ!, нажимаем F2, а затем CTRL+SHIFT+ENTER. Благодаря этим действиям формула преобразовалась в формулу массива и весь выделенный диапазон заполнился данными, а в строке формул вы увидите фигурные скобки.
Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.
2. С помощью опции Специальная вставка - Транспонировать. Данный способ транспонирует (переворачивает) таблицу без создания связей с исходной таблицей.
Выделяем таблицу, которую нужно перевернуть и копируем ее. Установив курсор в ячейку, с которой должна начинаться транспонированная таблица, на вкладке Главная группы Буфер обмена, нажимаем на стрелочку на кнопке Вставить и из предложенного меню выбираем Транспонировать.
Можно ли сделать в Экселе выборочное суммирование? Т.е. мне нужно, что бы из таблицы, например, где есть заказы по клиентам можно было выбрать клиента, а мне показывало бы сумму заказа.
С этой целью можно использовать формулу массива и промежуточные итоги.
1. Формула массива.
Предположим у нас есть таблица со следующими данными:
Нам нужно, что бы суммировало выручку по каждому магазину в разрезе выбранной марки и модели телефона. Для этого предварительно создаем таблицу с условиями, в корой указываем все переменные данные, кроме суммы, т.к. она у нас будет рассчитываться автоматически.
Теперь становимся в ячейку, в которой будет отображен результат вычислений, вводим равно и пишем сумм. У нас появляется окно подстановки формул.
В нем мы кликаем дважды по функции СУММ и начинаем вводить аргументы:
А) открываем скобку, выделяем диапазон ячеек, которые содержать названия магазинов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;
Б) ставим знак умножить "*", открываем скобку и выделяем диапазон ячеек, который содержит марки телефонов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;
В) ставим знак умножить "*", открываем скобку и выделяем диапазон ячеек, который содержит модель телефонов, устанавливаем курсор в строку формул, вводим знак "=" и кликаем на ячейку с данным условием, закрываем скобку;
Г) ставим знак умножить "*", выделяем диапазон, который содержит выручку, закрываем скобку;
Д) нажимаем CTRL+SHIFT+ENTER - формула заключается в фигурные скобки, что и делает ее формулой массива.
Внимание! Фигурные скобки нужно проставлять исключительно путем нажатия клавиш CTRL+SHIFT+ENTER, если их поставить вручную, формула не будет работать.
В результате у нас просуммирована выручка по телефонам Samsung Е2121, которые проданы в Магазине №1
Количество условий может быть разным, но если не все условия заданы, то суммирование не будет проводиться. В случае работы с большими таблицами и сложными аргументами, в качестве условий можно установить поле со списком, которое будет содержать все необходимые аргументы.
2. Функция Промежуточные итоги.
Ее можно использовать для вычислений данных отобранных с помощью Автофильтра. В конце таблицы в столбцах, в которых необходимо провести вычисления, вводим функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (вкладка Формулы группа Библиотека функций кнопка Вставить функцию или кнопка Ссылки и массивы).
В поле Номер_фунции - необходимо указать, порядковый номер функции, которая будет проводить вычисление итоговых значений. Номера функций в MS Excel уже назначен по умолчанию, вам нужно только выбрать нужный.
Номер_функции Функция 1 СРЗНАЧ 2 СЧЁТ 3 СЧЁТЗ 4 МАКС 5 МИН 6 ПРОИЗВЕД 7 СТАНДОТКЛОН 8 СТАНДОТКЛОНП 9 СУММ 10 ДИСП 11 ДИСПР
В поле Ссылка1 - выделяем диапазон ячеек, значения которых должны вычисляться.
Нажимаем ОК.
Теперь, при выборе значений с помощью Фильтра, у вас будет автоматически подсчитываться значения по выбранным фунциям.
