HTML, Excel, Word, SEOОсновы Excel ⇒ Операции с формулами массивов в Excel 2007

ОСНОВЫ EXCEL

Учебник Excel
Перемещение по листу и выделение ячеек
Ввод данных
Создание формул
Знакомство с функциями
Имена ячеек и диапазонов
Массивы
Форматирование. Назначение и удаление форматов
Форматирование чисел и текста
Маленькие секреты форматирования
Заканчиваем пользовательский формат
Выравнивание содержимого ячеек
Изменение шрифта
Использование границ и заливки
Условное форматирование и объединение ячеек
Установка параметров для редактирования
Использование мыши при вводе и редактировании
Заполнение рядов с помощью мыши
Вырезание и вставка ячеек
Функции. Синтаксис функций
Ввод функций
Математические функции
Текстовые функции
Функции для работы с элементами строк
Логические функции
Excel 2007
Интерфейс Excel 2007
Проверка вводимых данных
Условное форматирование
Сортировка и фильтрация
Связанные таблицы
Работа с диаграммами
Что такое сводные таблицы
Как создать сводную таблицу
Форматирование сводной таблицы
Анализ данных сводной таблицы
Создание сводной диаграммы
Вопрос-Ответ
Работа с датами
Вопросы по датам
Ошибки Excel
Защита данных Excel
Шаблоны Excel
Сортировка в Excel
Операции с текстом
Форматирование диаграмм
Сохранение диаграмм
Консолидация данных
Импорт данных в Excel
Поиск данных в таблицах
Операции с формулами массивов
Экспорт данных из Excel
Создание колонтитулов в Excel

https://androidprogrammi.ru/programmi/interfeys/klaviatura скачать по ссылке

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


 

Нажимаем ОК.


 

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


 
 

В начало страницы



В начало страницы