На главную Напишите нам! Напишите нам!
14-11-2012
колонтитул в word 2007, 0000002111
 

Операции с формулами массивов в 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 - выделяем диапазон ячеек, значения которых должны вычисляться.

Нажимаем ОК.

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

колонтитулы в word 2007, 000000211111

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

Скачать - инструкцию по созданию загрузочной флешки = 50,02мин. 20.04.2014г.
Скачать - правила получение аттестата Webmoney  (30.61 MB) = 7. 5 мин. 11.01.2013г.
Скачать ссылки на каталоги - для регистрации сайта (69.5 MB) = 9. 7 мин. 01.10.2013г.
Скачать - wifi router настройка модема (41.72 MB) = 7 мин. 46 сек. 26.01.2013г.
Скачать - TDSSKiller диагностика, лечение систем (24,71 MB) = 6,02 мин. 01.12.2012г.
Скачать - пример готового проэкта в Lazarus (91,59 MB) = 1,31мин. 08.04.2013г.
Скачать - правила установки MySQL в Lazarus (20,17 MB) = 4,55 мин. 25.02.2013г.
Класс!
 
Яндекс.Метрика
Копирование возможно при указании прямой индексируемой гиперссылки
0000001