Как сделать сводную таблицу из нескольких листов/диапазонов excel

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel

H&F разобрался, как предпринимателю перестать бояться формул Excel и начать считать всё, что хочется.

Excel — не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel-гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О’кей, иногда этих кликов 15.)

Импорт курса валют

В Excel можно настроить постоянно обновляющийся курс валют.

— Выберите в меню вкладку «Данные».

— Нажмите на кнопку «Из веба».

— В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

— Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся чёрно-жёлтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта (картинка 1).

— Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».

— Курс появится в ячейках на вашем листе.

— Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).

— В появившемся окне выберите частоту обновления курса и нажмите «ОК».

Супертайный лист

Допустим, вы хотите скрыть часть листов в Excel от других пользователей, работающих над книгой. Если сделать это классическим способом — кликнуть правой кнопкой по ярлычку листа и нажать на «Скрыть» (картинка 1), то имя скрытого листа всё равно будет видно другому человеку. Чтобы сделать его абсолютно невидимым, нужно действовать так:

— Слева у вас появится вытянутое окно (картинка 2).

— В верхней части окна выберите номер листа, который хотите скрыть.

— В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.

Запрет на изменения задним числом

Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».

— Нажмите на кнопку «Проверка данных». Появится таблица.

— В выпадающем списке «Тип данных» выбираем «Другой».

— В графе «Формула» пишем =А2=СЕГОДНЯ().

— Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).

— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).

— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

Запрет на ввод дублей

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

— Выделяем ячейки А1:А10, на которые будет распространяться запрет.

— Во вкладке «Данные» нажимаем кнопку «Проверка данных».

— Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).

— В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)

Или просто используйте Windows Update

Если вы не играете в компьютерные игры, вы всегда можете просто получить драйверы через Центр обновления Windows. Ничего не устанавливайте с NVIDIA, и Windows автоматически установит драйверы для вашего графического процессора. Тем не менее, драйверы не будут самыми последними для последних игр на ПК, и вы не получите такие утилиты, как панель управления NVIDIA.

Наличие последних драйверов важно для геймеров, но средний пользователь ПК может обойтись с драйверами из Центра обновления Windows

Самоучитель Excel с примерами для пользователей среднего уровня

​Нажмите ОК.​ и суммарные продажи​ продажи имеет смысл​ Итоги по/ Количество.​ Списке полей у​ реализации Товара в​ Вам нужна информация,​ параметров.​ формул.​В марте продано двуспальных​ измененными данными будет​ настройках:​5 полезных правил и​ таблиц в Excel​Копирование, перемещение и удаление​«. В следующих частях​South​Теперь Сводная таблица показывает​

Раздел 10: Подбор параметра, поиск решения и сценарии

exceltable.com>

​ имен) откорректируем Имя​

• Как в excel найти значения в таблице
• Excel получить данные сводной таблицы в excel
• Как сравнить две таблицы в excel на совпадения на разных листах
• Как объединить две таблицы в excel в одну с разными данными
• Excel автоматическое добавление строк в таблицу
• Как в таблице excel добавить столбец
• Таблица ганта в excel
• Сводные таблицы в excel для чайников видео
• Как удалить строку в таблице excel
• Работа в таблице excel для начинающих
• Как в excel сделать сводную таблицу из нескольких файлов
• Функция получить данные сводной таблицы в excel

Обзор

Временные файлы и программы

Если файл был загружен с помощью программы Torrent, то его можно найти в папке «Мои документы» или в папке «Загрузки» или самостоятельно задать место хранения скачанного файла, зайдя в настройки программы.

Практически всех начинающих пользователей интересует также вопрос о месте хранения временных файлов. Оно будет зависеть от их характеристики. Например, временные файлы любых программ автоматически сохраняются на том жестком диске, где находится операционная система: диск С — папка «Documents and Settings» — папка «Имя пользователя» — папка «Local Settings» — папка «Temp». Это при наличии установленной программы Windows XP. Для Windows 7 и Windows Vista поиск будет выглядеть немного иначе: диск C – папка «Users» — папка «Имя пользователя» — «Local Settings» — «Temp».

Временные файлы браузера легко найти, пройдя по следующей цепочке: диск C:\users\Имя пользователя\Local Settings\Application Data\Название браузера. Если же файл так и не был обнаружен, но в памяти осталась хотя бы несколько слов из его названия, то можно воспользоваться обычным поиском системы Windows через меню «Пуск».

Как в Excel использовать сводную таблицу вместо формул

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

На рис. 103.1 показан набор данных, который содержит оценки студентов по тесту. Цель состоит в вычислении среднего балла для всех студентов, а также среднего балла для каждого пола.

Рис. 103.1. Нужно найти самый лучший способ вычислить средний балл по тесту для парней и девушек

Вставка промежуточных итогов

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

1. Выберите любую ячейку в столбце С.
2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке Операция — Среднее, а в поле Добавить итоги по установите флажок Оценка.

Результат добавления промежуточных итогов показан на рис. 103.2

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

Вот эти формулы: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B13) =ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(1;B15:B28) =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B28) Формула в ячейке В30 вычисляет общее среднее и использует диапазон, который включают в себя другие две формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейках В14 и В29 . Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует ячейки, которые содержат другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Рис. 103.2. Excel добавляет промежуточные итоги автоматически

Использование формул

Другой способ вычисления средних значений заключается в использовании формул. Формула для расчета среднего для всех студентов простая: =СРЗНАЧ(B2:B27) .

Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул: =СРЗНАЧЕСЛИ(C2:C27;”Женский”;B2:B27) =СРЗНАЧЕСЛИ(C2:C27;”Мужской”;B2:B27)

Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы: =СУММЕСЛИ(C2:C27;”Женский”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Женский”) =СУММЕСЛИ(C2:C27;”Мужской”;B2:B27)/СЧЕТЕСЛИ(C2:C27;”Мужской”)

Использование сводной таблицы

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

1. Выберите любую ячейку из диапазона данных и выполните команду Вставка ► Таблицы ► Сводная таблица для открытия диалогового окна Создание сводной таблицы.
2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем листе в качестве местоположения. Ячейка Е1 будет неплохим выбором.
3. Нажмите кнопку ОК, и Excel отобразит список полей сводной таблицы.
4. В списке полей перетащите пункт Пол в раздел Названия строк, в самый низ.
5. Перетащите пункт Оценка в раздел Значения. Excel создаст сводную таблицу, но отобразит функцию СУММ, а не СРЗНАЧ.
6. Чтобы изменить используемую итоговую функцию, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите Итоги по ► Среднее в контекстном меню (рис. 103.3).

Рис. 103.3. Эта сводная таблица вычисляет средние значения без использования формул

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

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

Удаление

Как сделать сводную таблицу из нескольких таблиц

Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.

Порядок создания сводной таблицы из нескольких листов такой же.

Создадим отчет с помощью мастера сводных таблиц:

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
3. Следующий этап – «создать поля». «Далее».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

flick•er1

Генератор примеров (массивы, таблицы, отчеты).

Сводная таблица из нескольких листов в excel как сделать

Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Для решения этой задачи нам понадобиться надстройка ЁXCEL .

Итак, приступим к решению. Мы имеем на входе 3 таблицы, расположенные на разных листах рабочей книги:

Важно:

• Количество столбцов во всех таблицах должно быть одинаково;
• Кроме таблиц на листах не должно быть никакой информации.

Начинаем манипуляции. Переходим в главном меню во вкладку ЁXCEL и нажимаем кнопку «Таблицы», в выпавшем меню выбираем команду «Объединить таблицы»:

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

Осталось совсем немного. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

В открывшемся диалоговом окне устанавливаем переключатели в положения «Отчет сводной таблицы» и «Новый лист», нажимаем «ОК»:

Программа создаст в книге новый лист на который выведет макет сводной таблицы

Обратите внимание — программа создаст в сформированной таблице новый столбец с названиями листов из которых были получены данные:

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

Чтобы обновление сводной таблицы происходило автоматически вставьте в модуль каждого листа содержащего таблицы следующий код ( Как вставлять макросы? ):

Важно:

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

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.

Возможные ошибки при использовании этого метода:

• В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
• Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
• В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.

Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
2. Параметры.
3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Как создать сводную таблицу?

Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций – самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива Вашей компании.

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

• Какие данные использовать для создания новой сводной таблицы?
• Куда поместить сводную таблицу?

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы

Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

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

Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson (Торговый представитель) в область Row Labels (Строки):

Становится интересней! Наша сводная таблица начинает обретать форму…

Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.

Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.

Template #39: Museum YouTube Channel Art

Как сделать таблицу в Excel по-простому

Итак, нам понадобятся: компьютер, Excel и 5 минут времени. Для начала вам нужно включить компьютер и перейти в программу Эксель (90% дела будет сделано).

Если нет компьютера или программы, то сорри, миссия провалена.

Если вы с успехом прошли предыдущие испытания, то вы сможете лицезреть прекрасное, а именно интерфейс программы Эксель, вот так он выглядит:

Здесь есть строки и столбцы (в принципе как и у любой нормальной таблицы) — это номер один, что вам нужно знать про Эксель.

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

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

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

Чтобы сделать таблицу в Excel выполним несколько простых шагов:

Шаг 1. Введём названия столбцов и строк и заполним таблицу данными:

Шаг 2. Зададим границы таблицы:

Вот и всё, таблица готова, поздравляю вас с хорошей работой, теперь можно и отдохнуть! Или нет?

Внимание! Сейчас вылетит фишка!

Для того чтобы быстро форматировать границы таблицы можно воспользоваться следующим манёвром:

Выделяем одну из ячеек внутри таблицы (в первом столбце — это важно).
Нажимаем комбинацию клавиш CTRL+A (таблица должна выделиться)
Нажимаем решетку (см. скрин выше) и выбираем «Все границы». Кстати, я думаю вы же делаете табличку не просто так (для красоты), а для работы

Это значит вам необходимо хоть как-то научиться манипулировать данными внутри неё

Кстати, я думаю вы же делаете табличку не просто так (для красоты), а для работы. Это значит вам необходимо хоть как-то научиться манипулировать данными внутри неё.

Поэтому покажу пару самых часто используемых приёмов.

Приём первый — фильтры в таблицах Эксель

Фильтры штука очень полезная. Фильтры в Excel нужны для того чтобы сортировать данные и/или быстро просматривать только нужные данные (фильтруя/убирая из поля зрения не нужные)

Чтобы воспользоваться этим чудо-инструментом, нужно выделить всю таблицу (например используя CTRL+A), затем вверху справа выбрать «Сортировка и фильтр» и в выпадающем меню выбрать «Фильтр».

Получается вот такая красота, покликайте на эти штуки и узнаете на что они способны!

Приём второй — автосумма в Excel

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

Ба-бам, рядом появится еще одна ячейка с суммой.

Магия вне Хогвартса удалась!

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

Вангую (т.е. предсказываю), в процессе работы с Excel таблицей, вам понадобиться (хоть раз за всю карьеру) добавить в неё несколько строк и столбцов.

Поэтому! Давайте сразу расскажу как добавлять/убавлять строки/столбцы в эту самую таблицу. Делается это элементарнейшим образом.

Выделяем строку/столбец, перед которыми нужно добавить новую строку или столбец и жмем кнопку «Вставить» (как показано на скрине), затем выбираем то что мы хотим вставить.

Этот же манёвр можно сделать более элегантно и после выделения, например, строки, нажмите «CTRL + ПЛЮС».

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

Почему почти? – спросите вы. Да потому что гуру должен владеть несколькими способами достижения результата.

Хотите стать гуру, тогда читайте дальше.

Операции со сводными таблицами

Редактирование сводной таблицы – такая же простая задача, как и ее создание. Давайте посмотрим, как на практике воплощаются в жизнь те идеи, которые директор предложил выше.

Давайте осуществим замену выручки на прибыль.

10

С помощью простого перетаскивания можно поменять местами товары и области.

11

В нашем случае мы воспользовались областью фильтров, куда поместили поле «Менеджер».

12

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

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

Corporate YouTube Banner Template

Форматирование сводных таблиц в Excel

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

Велик соблазн сделать привычные в такой ситуации действия и просто выделить всю таблицу (или весь лист) и использовать стандартные кнопки форматирования чисел на панели инструментов, чтобы задать нужный формат. Проблема такого подхода состоит в том, что если Вы когда-либо в будущем измените структуру сводной таблицы (а это случится с вероятностью 99%), то форматирование будет потеряно. Нам же нужен способ сделать его (почти) постоянным.

Во-первых, найдём запись Sum of Amount в области Values (Значения) и кликнем по ней. В появившемся меню выберем пункт Value Field Settings (Параметры полей значений):

Появится диалоговое окно Value Field Settings (Параметры поля значений).

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК, чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…

Откройте вкладку PivotTable Tools: Design (Работа со сводными таблицами: Конструктор):

Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles (Стили сводной таблицы), чтобы увидеть обширную коллекцию встроенных стилей:

Выберите любой подходящий стиль и посмотрите на результат в своей сводной таблице: