Эксель таблицы примеры: Умные Таблицы Excel – секреты эффективной работы

Содержание

Умные Таблицы Excel – секреты эффективной работы

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

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы». 

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Обычный диапазон данныхОбычный диапазон данных

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Создать таблицу Excel с лентыСоздать таблицу Excel с ленты

Есть горячая клавиша Ctrl+T.

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

Создание таблицы ExcelСоздание таблицы Excel

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

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Вкладка Конструктор для таблицы ExcelВкладка Конструктор для таблицы Excel

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

Таблица в диспетчере именТаблица в диспетчере имен

А также при наборе формулы вручную.

Таблица в подсказке при наборе формулыТаблица в подсказке при наборе формулы

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбор элемента таблицы в формулеВыбор элемента таблицы в формуле

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет[Продажи]

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Ссылка на столбец таблицыСсылка на столбец таблицы

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

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

Свойства Таблиц Excel

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

Заголовки таблицы ExcelЗаголовки таблицы Excel

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Заголовки таблицы всегда на экранеЗаголовки таблицы всегда на экране

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Добавление новых значений в таблицу ExcelДобавление новых значений в таблицу Excel
Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

Добавление нового столбцаДобавление нового столбца

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

Введение формулы в столбец Таблицы ExcelВведение формулы в столбец Таблицы Excel

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

Настройка Таблицы ExcelНастройка Таблицы Excel

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

Стили ТаблицыСтили Таблицы

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

Инструменты Таблицы ExcelИнструменты Таблицы Excel

Однако самое интересное – это создание срезов.

Кнопка создания среза в Таблице ExcelКнопка создания среза в Таблице Excel

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

Выбор столбцов для срезаВыбор столбцов для среза

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Срез Таблицы ExcelСрез Таблицы Excel

Для фильтрации Таблицы следует выбрать интересующую категорию.

Фильтрация Таблицы с помощью срезаФильтрация Таблицы с помощью среза

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Параметры срезаПараметры среза

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Поделиться в социальных сетях:

Сводные таблицы

Когда я начинал читать тренинги по MS Excel то «стер» пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы «Сводные таблицы». Решил я эту проблему просто — создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это «чудо» на очередном тренинге они очень сильно «возбудились». Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для «студента».

Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.

В данной статье я покажу, как можно преобразовать такой «горе-отчет» в массив для анализа с помощью Сводной таблицы.

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_03.zip)Пример 52 Кб1529

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

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_02.zip)Пример 69 Кб1753

На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод — невозможно. Хотя решение существует.

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_07.zip)Пример 290 Кб1598

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

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

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_05.zip)Пример 27 Кб1191

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

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

Файлы для скачивания:
ФайлОписаниеРазмер файла:Скачивания
Скачать этот файл (P_STab_01.zip)Пример 109 Кб2651

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

Вы очень долго «рисовали» сложную таблицу с множеством строк и столбцов и большим содержанием данных, например, таблицу — отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и долей… И когда уже все готово и «раскрашено», вы показываете эту таблицу, например, руководителю, а он говорит: «Все круто, вот только бы добавить сюда еще разрез по Товарным категориям, вообще было бы замечательно».

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

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

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

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

Данные для сводной таблицыДанные для сводной таблицы

Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.

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

Шапка сводной таблицыШапка сводной таблицы

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

Сведение данных с помощью формулыСведение данных с помощью формулы

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.

Рассмотрим, как создать сводную таблицу в Excel.

Создание сводной таблицы в Excel

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

Кнопки построения сводной таблицы на лентеКнопки построения сводной таблицы на ленте

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

Макеты рекомендуемых сводных таблицМакеты рекомендуемых сводных таблиц

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

Диалоговое окно создания сводной таблицыДиалоговое окно создания сводной таблицы

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

Пустая сводная таблицаПустая сводная таблица

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

Панель управления полями сводной таблицыПанель управления полями сводной таблицы

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

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

Сводная таблицаСводная таблица

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Создание сводной таблицы перетаскивание полейСоздание сводной таблицы перетаскивание полей

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

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

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

Фильтрация сводной таблицыФильтрация сводной таблицы

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

Источник данных сводной таблицы Excel

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

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

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

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

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

В целом требований немного, но их следует знать.

Обновление данных в сводной таблице Excel

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

Обновление сводной таблицыОбновление сводной таблицы

или
через команду во вкладке Данные – Обновить все.

Обновить всеОбновить все

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

Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных. 

Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.

Скачать файл с примером.

Поделиться в социальных сетях:

Как сделать таблицу в Excel. Пошаговая инструкция

Автор Влад Каманин На чтение 6 мин.

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

Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.

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

Как работать в Excel с таблицами. Пошаговая инструкция

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

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

1. Выделите область ячеек для создания таблицы

Как сделать таблицу в Excel

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

2. Нажмите кнопку “Таблица” на панели быстрого доступа

Как создать таблицу в Excel

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

Как сделать таблицу в Excel

Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Как сделать таблицу в Excel

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

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

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

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

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

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

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

  • Отображение строки заголовков – включает и отключает заголовки в таблице;
  • Строку итогов – включает и отключает строку с суммой значений в колонках;
  • Чередующиеся строки – подсвечивает цветом чередующиеся строки;
  • Первый столбец – выделяет “жирным” текст в первом столбце с данными;
  • Последний столбец – выделяет “жирным” текст в последнем столбце;
  • Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
  • Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

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

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

</h3><h3>Как добавить строку/колонку в таблице Excel</h3><p>Чтобы добавить строку или колонку в таблице Excel:</p><ul><li>Нажмите правой кнопкой мыши на любой ячейке таблицы, где вы хотите вставить строку или колонку => появится всплывающее окно:</li></ul><p><img769

 

  • Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

Как вставить строку в Excel

  • Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как удалить строку в Excel

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

Как отстортировать таблицу в excel

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

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

Как отфильтровать данные в таблице Excel

  • “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
  • “Фильтр по цвету” так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
  • “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
  • Во всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.

Фильтрация данных в таблице Excel

 

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

Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:

Промежуточные итоги в таблице Excel

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Промежуточные итоги в таблице Excel

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

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

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

  • Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

Закрепить шапку таблицы в Excel

  • Выберите пункт “Закрепить верхнюю строку”:

Закрепить шапку в таблице Excel

  • Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:

Закрепить шапку в таблице Excel

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

Как перевернуть таблицу в Excel

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

  • Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

Как перевернуть таблицу в Excel

  • Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

Как перевернуть таблицу в Excel

  • В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

Как перевернуть таблицу в Excel

  • Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

Как перевернуть таблицу в Excel

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Как работать со сводными таблицами в Excel (с примерами)

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

Чем сводные таблицы полезны

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

Как работать со сводными таблицами в Excel (с примерами)1

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

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

Как работать со сводными таблицами в Excel (с примерами)2

Эта таблица должна быть заполнена, то есть, надо узнать сумму по выручке с нужных регионов по каждой из товарных позиций. Эта задача легко выполняется, если использовать функцию СУММЕСЛИМН. Кроме того, нужно добавить итоги. После этого появится сводный отчет по каждой области. 

Ура, теперь вы довольны и несете отчет начальнику, который окинул его взором и попросил воплотить в жизнь еще ряд идей:

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

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

Создание сводной таблицы Excel

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

Далее нам следует выделить какую-угодно ячейку и открыть вкладку «Вставить». В левой части ленты расположено два пункта «Сводная таблица» и «Рекомендуемые сводные таблицы».

Как работать со сводными таблицами в Excel (с примерами)3

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

Как работать со сводными таблицами в Excel (с примерами)4

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

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

Как работать со сводными таблицами в Excel (с примерами)5

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

Как работать со сводными таблицами в Excel (с примерами)6

Чтобы настроить его, необходимо воспользоваться панелью «Поля сводной таблицы», расположенной в правой части листа.

Как работать со сводными таблицами в Excel (с примерами)7

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

Чтобы удалить поле, достаточно просто снять соответствующий флажок.

Составные компоненты

В состав сводной таблицы входят такие компоненты:

  1. Область значений. Под ней подразумевается главная часть таблицы со значениями. Excel их получает с помощью агрегирования исходных данных тем методом, который выберет пользователь. Как правило, это делается с помощью Суммирования. Этот метод установлен автоматически при условии, что все данные в исходном диапазоне находятся в ячейке, имеющей числовой формат. Если же хотя бы в одной ячейке есть текст или отсутствует какая-либо информация, то автоматически будет осуществляться подсчет количества ячеек. Всего есть около 20 различных видов вычислений. наиболее просто изменить его с помощью правого клика по какой-угодно ячейке необходимого поля сводной таблицы и осуществить выбор метода агрегирования.
  2. Область строк. Собственно, сюда входят названия строк, находящихся в крайнем левом столбце. Это все значения столбца, уникальные в своем роде и которые были выбраны пользователем. Здесь может быть несколько полей. Такая таблица называется многоуровневой. Как правило, здесь находятся любые не количественные данные, такие как названия товаров, регионов и так далее.
  3. Область столбцов. То же самое, что и область строк, только касается столбцов. Сюда могут входить годы, месяцы, а также группы выпускаемой продукции.
  4. Область фильтра. Применяется для того, чтобы показывать лишь определенные значения, соответствующие конкретному признаку. Например, есть возможность отображения данных лишь по конкретной отрасли, за определенный период и так далее. В этом случае необходимо поместить поле фильтрации в область фильтра и выбрать необходимое значение в списке, который раскрывается. 

Именно область фильтра позволяет сделать гибкую настройку среза данных. В теории оно все немного непонятно звучит. Давайте все сделаем на конкретном примере. Давайте сделаем такую же таблицу, как и выше. Для этого необходимо в область «Значения» перетащить поле «Выручка», в область «Строки» – «Область», а в область «Столбцы» – «Товар».

Как работать со сводными таблицами в Excel (с примерами)8

После этого перед нами появится полноценная сводная таблица.

Как работать со сводными таблицами в Excel (с примерами)9

И что удивительно? Чтобы ее создать, потребовалось всего 10 секунд. 

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

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

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

Как работать со сводными таблицами в Excel (с примерами)10

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

Как работать со сводными таблицами в Excel (с примерами)11

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

Как работать со сводными таблицами в Excel (с примерами)12

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

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

Полезные советы по подбору источника данных

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

  1. Лучше всего в качестве источника данных использовать умную таблицу. Ее главное преимущество заключается в том, что каждая колонка имеет свое название, и если добавлять колонки или строки, диапазон данных автоматически расширяется на соответствующее их количество.
  2. Не рекомендуется повторения групп в колонках. Так, желательно, чтобы даты располагались в одном столбце, а не разбивались по месяцам в отдельных колонках.
  3. Правильно форматируйте поля. Важно, чтобы все числа были в числовом формате, а даты были действительно в формате даты. Иначе Excel будет трудно правильно сгруппировать и обработать данные. В принципе, Excel автоматически определяет тип данных, но в некоторых случаях возможны глюки. Поэтому перед тем, как создавать сводную таблицу, необходимо удостовериться, что вся информация в исходной находится в правильном формате. 

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

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

Как работать со сводными таблицами в Excel (с примерами)13

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

Как работать со сводными таблицами в Excel (с примерами)14

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

Как создать дашборд в Excel

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

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

Как правило, в компаниях созданием умных таблиц и ограничиваются, в то время как дашборды имеют огромное количество преимуществ:

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

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

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

  1. Фигуры и объекты Word Art. Они позволяют рисовать все, что угодно, вплоть до инженерных чертежей. Кроме этого, есть множество текстовых меток, которые позволяют описать любую составную часть дашборда.
  2. Использование сводных таблиц.
  3. Графики, которые могут в качестве данных также использовать исходный диапазон.

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

Выводы

Таким образом, создавать сводные таблицы в Excel – вовсе несложная задача, которая поможет сэкономить множество часов при создании отчетов. Достаточно нажать всего несколько кнопок, и отчетная таблица будет создана автоматически. Далее ее можно редактировать и подгонять под себя. Успехов. 

Оцените качество статьи. Нам важно ваше мнение:

Как работать в Excel с таблицами для чайников: пошаговая инструкция

Программа Microsoft Excel удобна для составления таблиц и произведения расчетов. Рабочая область – это множество ячеек, которые можно заполнять данными. Впоследствии – форматировать, использовать для построения графиков, диаграмм, сводных отчетов.

Работа в Экселе с таблицами для начинающих пользователей может на первый взгляд показаться сложной. Она существенно отличается от принципов построения таблиц в Word. Но начнем мы с малого: с создания и форматирования таблицы. И в конце статьи вы уже будете понимать, что лучшего инструмента для создания таблиц, чем Excel не придумаешь.

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.



Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

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

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

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

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Поменяйте, к примеру, размер шрифта, сделайте шапку «жирным». Можно установить текст по центру, назначить переносы и т.д.

Как создать таблицу в Excel: пошаговая инструкция

Простейший способ создания таблиц уже известен. Но в Excel есть более удобный вариант (в плане последующего форматирования, работы с данными).

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

Примечание. Можно пойти по другому пути – сначала выделить диапазон ячеек, а потом нажать кнопку «Таблица».

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

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

Иногда пользователю приходится работать с огромными таблицами. Чтобы посмотреть итоги, нужно пролистать не одну тысячу строк. Удалить строки – не вариант (данные впоследствии понадобятся). Но можно скрыть. Для этой цели воспользуйтесь числовыми фильтрами (картинка выше). Убираете галочки напротив тех значений, которые должны быть спрятаны.

Таблицы в Excel (понятие, структура, настройка, умная таблица)

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

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

Понятие умных таблиц

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

А вот таблица Excel – принципиально иное понятие. Оно не сводится к диапазону данных, у него есть свои свойства, название, определенная структура и огромное количество преимуществ.

Поэтому можно выделить отдельное название для таблицы Excel – «Умная таблица» или Smart Table.

Создание умной таблицы

Предположим, мы создали диапазон данных с информацией о продажах.Работа с таблицами в Excel

Это еще не таблица. Чтобы превратить диапазон в нее, необходимо его выделить и найти вкладку «Вставка» и там найти кнопку «Таблица» в одноименном блоке.Работа с таблицами в Excel

Появится маленькое окошко. В нем можно скорректировать набор ячеек, который необходимо превратить в таблицу. Кроме этого, необходимо указать, что в первой строке находятся заголовки столбцов. Также можно воспользоваться комбинацией клавиш Ctrl + T, чтобы вызвать это же диалоговое окно.Работа с таблицами в Excel

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

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

Понятие структуры таблицы Excel

Все таблицы имеют определенное имя, отображаемое на специальной вкладке «Конструктор». Она показывается сразу после выделения любой ячейки. Стандартно имя принимает форму «Таблица 1» или «Таблица 2», и соответственно.Работа с таблицами в Excel

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

Кроме того, именованные таблицы могут использоваться в Power Query или ряде других надстроек.

Давайте нашу таблицу назовем «Отчет». Имя можно увидеть в окне, которое называется диспетчером имен. Чтобы его открыть, необходимо пройти по следующему пути: Формулы – Определенные Имена – Диспетчер имен.Работа с таблицами в Excel

Также возможен ручной ввод формулы, где также можно увидеть имя таблицы.Работа с таблицами в Excel

Но наиболее забавным является то, что Excel способен одновременно видеть таблицу в нескольких разрезах: целиком, а также по отдельным столбцам заголовкам, итогам. Тогда ссылки будут выглядеть так.Работа с таблицами в Excel

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

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

Если необходимо суммировать содержимое всего столбца с продажами, необходимо написать следующую формулу:

=СУММ(D2:D8)

После этого она автоматически превратится в =СУММ(Отчет[Продажи]). Простыми словами, ссылка будет вести на конкретную колонку. Удобно, согласитесь?Работа с таблицами в Excel

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

Теперь давайте более подробно поговорим о том, какие таблицы могут иметь свойства.

Таблицы Excel: свойства

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

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

Также в функционал входит автофильтр. Но если он не нужен, его всегда можно отключить в настройках.

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

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

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

Все эти свойства – хорошо. Но ведь можно самостоятельно настроить таблицу и расширить ее функционал.

Настройка Таблицы

Для начала необходимо открыть вкладку «Конструктор», где располагаются параметры таблицы. Настраивать их можно путем добавления или снятия конкретных флажков, расположенных в группе «Параметры стилей таблиц»Работа с таблицами в Excel

Предусмотрены следующие возможности:

  1. Добавление или удаление строки заголовков.
  2. Добавление или удаление строки с итогами.
  3. Сделать строки чередующимися.
  4. Выделение крайних колонок жирным начертанием.
  5. Включение или отключение чередующейся заливки строк.
  6. Отключение автофильтра.

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

Также можно найти группу «Инструменты», где возможно создание сводной таблицы, удаление копий и преобразование таблицы в стандартный диапазон.Работа с таблицами в Excel

Но наиболее занимательная функция – создание срезов.Работа с таблицами в Excel

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

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

Чтобы отфильтровать таблицу, необходимо выбрать категорию, которая наиболее интересна в данный момент.Работа с таблицами в Excel

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

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

Основные ограничения умных таблиц

Несмотря на то, что таблицы Excel имеют множество преимуществ, все же пользователю придется мириться с некоторыми минусами:

  1. Представления не функционируют. Простыми словами, нет возможности запомнить определенные параметры листа.
  2. Нельзя использовать книгу вместе с другим человеком.
  3. Нет возможности вставлять промежуточные итоги.
  4. Нельзя использовать формулы массивов.
  5. Нет возможности объединять ячейки. Но этого не рекомендуется делать и так.

Впрочем, достоинств значительно больше, чем недостатков, поэтому эти минусы будут не очень заметными.

Примеры использования умных таблиц

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

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

Подведение итогов с помощью функционала Excel

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

Далее делается правый клик по любому месту. Появляется всплывающее меню, где есть пункт «Таблица». В нем есть опция «строка итогов», которую и нужно включить. Также ее добавление возможно через конструктор.Работа с таблицами в Excel

Далее в нижней части таблицы появляется строка с итогами. Если открыть выпадающее меню, там можно увидеть следующие настройки:

  1. Среднее арифметическое.
  2. Количество.
  3. Максимум.
  4. Смещенное отклонение.

И многое другое. Чтобы получить доступ к функциям, не вошедшим в список выше, нужно нажать по пункту «Другие функции». Здесь удобно то, что диапазон автоматически определяется. Мы выбрали функцию СУММ, потому что в нашем случае надо знать, сколько всего футболок было продано.Работа с таблицами в Excel

Автоматическая вставка формул

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

Функция сортировки

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

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

Причем все Excel может сделать вместо вас. 

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

Все, теперь информация о том, кто совершил оплату, представлена наглядно. 

Фильтрация

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

Выводы

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

Оцените качество статьи. Нам важно ваше мнение:

90000 Excel Sample Data for Testing and Examples 90001
90002

90003 Below is a table with the Excel sample data used for many of my web site examples. You can use this sample data to create test files, and build
Excel tables
and pivot
tables from the data. Copy and paste from this table, or download the sample data file. 90004
90003 There are hundreds more sample Excel files that you can download, and Excel lessons, with videos and written steps. 90004

90007 Sample Data 90008
90003 Copy and paste the data from the table below.Or, to download the sample data in an Excel file, click this link:
Excel sample data workbook 90004
90011
90012
90013
90003 90015 OrderDate 90016 90004
90018
90019
90003 90015 Region 90016 90004
90018
90019
90003 90015 Rep 90016 90004
90018
90019
90003 90015 Item 90016 90004
90018
90013
90003 90015 Units 90016 90004
90018
90013

90003 90015 UnitCost 90016 90004
90018
90013
90003 90015 Total 90016 90004
90018
90055
90056
90013
90003 1/6/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 95 90004
90018
90013
90003 1.99 90004
90018
90013
90003 189.05 90004
90018
90055
90056
90013
90003 1/23/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Kivell 90004
90018
90019
90003 Binder 90004
90018
90013
90003 50 90004
90018
90013
90003 19.99 90004
90018
90013
90003 999.50 90004
90018
90055
90056
90013
90003 2/9/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Jardine 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 36 90004
90018
90013
90003 4.99 90004
90018
90013
90003 179.64 90004
90018
90055
90056
90013
90003 2/26/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Gill 90004
90018
90019
90003 Pen 90004
90018
90013
90003 27 90004
90018
90013
90003 19.99 90004
90018
90013
90003 539.73 90004
90018
90055
90056
90013
90003 3/15/2019 90004
90018
90019
90003 West 90004
90018
90019
90003 Sorvino 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 56 90004
90018
90013
90003 2.99 90004
90018
90013
90003 167.44 90004
90018
90055
90056
90013
90003 4/1/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Binder 90004
90018
90013
90003 60 90004
90018
90013
90003 4.99 90004
90018
90013
90003 299.40 90004
90018
90055
90056
90013
90003 4/18/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Andrews 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 75 90004
90018
90013
90003 1.99 90004
90018
90013
90003 149.25 90004
90018
90055
90056
90013
90003 5/5/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Jardine 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 90 90004
90018
90013
90003 4.99 90004
90018
90013
90003 449.10 90004
90018
90055
90056
90013
90003 5/22/2019 90004
90018
90019
90003 West 90004
90018
90019
90003 Thompson 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 32 90004
90018
90013
90003 1.99 90004
90018
90013
90003 63.68 90004
90018
90055
90056
90013
90003 6/8/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Binder 90004
90018
90013
90003 60 90004
90018
90013
90003 8.99 90004
90018
90013
90003 539.40 90004
90018
90055
90056
90013
90003 6/25/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Morgan 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 90 90004
90018
90013
90003 4.99 90004
90018
90013
90003 449.10 90004
90018
90055
90056
90013
90003 7/12/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Howard 90004
90018
90019
90003 Binder 90004
90018
90013
90003 29 90004
90018
90013
90003 1.99 90004
90018
90013
90003 57.71 90004
90018
90055
90056
90013
90003 7/29/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Parent 90004
90018
90019
90003 Binder 90004
90018
90013
90003 81 90004
90018
90013
90003 19.99 90004
90018
90013
90003 1,619.19 90004
90018
90055
90056
90013
90003 8/15/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 35 90004
90018
90013
90003 4.99 90004
90018
90013
90003 174.65 90004
90018
90055
90056
90013
90003 9/1/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Smith 90004
90018
90019
90003 Desk 90004
90018
90013
90003 2 90004
90018
90013
90003 125.00 90004
90018
90013
90003 250.00 90004
90018
90055
90056
90013
90003 9/18/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 16 90004
90018
90013
90003 15.99 90004
90018
90013
90003 255.84 90004
90018
90055
90056
90013
90003 10/5/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Morgan 90004
90018
90019
90003 Binder 90004
90018
90013
90003 28 90004
90018
90013
90003 8.99 90004
90018
90013
90003 251.72 90004
90018
90055
90056
90013
90003 10/22/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Pen 90004
90018
90013
90003 64 90004
90018
90013
90003 8.99 90004
90018
90013
90003 575.36 90004
90018
90055
90056
90013
90003 11/8/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Parent 90004
90018
90019
90003 Pen 90004
90018
90013
90003 15 90004
90018
90013
90003 19.99 90004
90018
90013
90003 299.85 90004
90018
90055
90056
90013
90003 11/25/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Kivell 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 96 90004
90018
90013
90003 4.99 90004
90018
90013
90003 479.04 90004
90018
90055
90056
90013
90003 12/12/2019 90004
90018
90019
90003 Central 90004
90018
90019
90003 Smith 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 67 90004
90018
90013
90003 1.29 90004
90018
90013
90003 86.43 90004
90018
90055
90056
90013
90003 12/29/2019 90004
90018
90019
90003 East 90004
90018
90019
90003 Parent 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 74 90004
90018
90013
90003 15.99 90004
90018
90013
90003 1,183.26 90004
90018
90055
90056
90013
90003 1/15/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Gill 90004
90018
90019
90003 Binder 90004
90018
90013
90003 46 90004
90018
90013
90003 8.99 90004
90018
90013
90003 413.54 90004
90018
90055
90056
90013
90003 2/1/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Smith 90004
90018
90019
90003 Binder 90004
90018
90013
90003 87 90004
90018
90013
90003 15.00 90004
90018
90013
90003 1,305.00 90004
90018
90055
90056
90013
90003 2/18/2020
90004 90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Binder 90004
90018
90013
90003 4 90004
90018
90013
90003 4.99 90004
90018
90013
90003 19.96 90004
90018
90055
90056
90013
90003 3/7/2020
90004 90018
90019
90003 West 90004
90018
90019
90003 Sorvino 90004
90018
90019
90003 Binder 90004
90018
90013
90003 7 90004
90018
90013
90003 19.99 90004
90018
90013
90003 139.93 90004
90018
90055
90056
90013
90003 3/24/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Jardine 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 50 90004
90018
90013
90003 4.99 90004
90018
90013
90003 249.50 90004
90018
90055
90056
90013
90003 4/10/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Andrews 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 66 90004
90018
90013
90003 1.99 90004
90018
90013
90003 131.34 90004
90018
90055
90056
90013
90003 4/27/2020
90004 90018
90019
90003 East 90004
90018
90019
90003 Howard 90004
90018
90019
90003 Pen 90004
90018
90013
90003 96 90004
90018
90013
90003 4.99 90004
90018
90013
90003 479.04 90004
90018
90055
90056
90013
90003 5/14/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Gill 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 53 90004
90018
90013
90003 1.29 90004
90018
90013
90003 68.37 90004
90018
90055
90056
90013
90003 5/31/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Gill 90004
90018
90019
90003 Binder 90004
90018
90013
90003 80 90004
90018
90013
90003 8.99 90004
90018
90013
90003 719.20 90004
90018
90055
90056
90013
90003 6/17/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Kivell 90004
90018
90019
90003 Desk 90004
90018
90013
90003 5 90004
90018
90013
90003 125.00 90004
90018
90013
90003 625.00 90004
90018
90055
90056
90013
90003 7/4/2020
90004 90018
90019
90003 East 90004
90018
90019
90003 Jones 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 62 90004
90018
90013
90003 4.99 90004
90018
90013
90003 309.38 90004
90018
90055
90056
90013
90003 7/21/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Morgan 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 55 90004
90018
90013
90003 12.49 90004
90018
90013
90003 686.95 90004
90018
90055
90056
90013
90003 8/7/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Kivell 90004
90018
90019
90003 Pen Set 90004
90018
90013
90003 42 90004
90018
90013
90003 23.95 90004
90018
90013
90003 1,005.90 90004
90018
90055
90056
90013
90003 8/24/2020
90004 90018
90019
90003 West 90004
90018
90019
90003 Sorvino 90004
90018
90019
90003 Desk 90004
90018
90013
90003 3 90004
90018
90013
90003 275.00 90004
90018
90013
90003 825.00 90004
90018
90055
90056
90013
90003 9/10/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Gill 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 7 90004
90018
90013
90003 1.29 90004
90018
90013
90003 9.03 90004
90018
90055
90056
90013
90003 9/27/2020
90004 90018
90019
90003 West 90004
90018
90019
90003 Sorvino 90004
90018
90019
90003 Pen 90004
90018
90013
90003 76 90004
90018
90013
90003 1.99 90004
90018
90013
90003 151.24 90004
90018
90055
90056
90013
90003 10/14/2020
90004 90018
90019
90003 West 90004
90018
90019
90003 Thompson 90004
90018
90019
90003 Binder 90004
90018
90013
90003 57 90004
90018
90013
90003 19.99 90004
90018
90013
90003 1,139.43 90004
90018
90055
90056
90013
90003 10/31/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Andrews 90004
90018
90019
90003 Pencil 90004
90018
90013
90003 14 90004
90018
90013
90003 1.29 90004
90018
90013
90003 18.06 90004
90018
90055
90056
90013
90003 11/17/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Jardine 90004
90018
90019
90003 Binder 90004
90018
90013
90003 11 90004
90018
90013
90003 4.99 90004
90018
90013
90003 54.89 90004
90018
90055
90056
90013
90003 12/4/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Jardine 90004
90018
90019
90003 Binder 90004
90018
90013
90003 94 90004
90018
90013
90003 19.99 90004
90018
90013
90003 1,879.06 90004
90018
90055
90056
90013
90003 12/21/2020
90004 90018
90019
90003 Central 90004
90018
90019
90003 Andrews 90004
90018
90019
90003 Binder 90004
90018
90013
90003 28 90004
90018
90013
90003 4.99 90004
90018
90013
90003 139.72 90004
90018
90055
дев’яносто одна тисяча триста сорок-шість

90007 Sample Data Notes 90008
90003 This is sales data for an imaginary stationery company, and each row
represents an order. It shows: 90004

91351
91352 when the order was placed, 91353
91352 region in which the sale was made 91353
91352 the sales representative’s name 91353
91352 the item sold 91353
91352 the number of units sold 91353
91352 the cost of a single unit 91353
91352 the total cost of the order 91353
91366

90007 Get the Sample Data 90008
90003 To use this sample data, download the sample file, or copy and paste it from the table on this page.90004
91371 Download the Sample File дев’яносто одна тисяча триста сімдесят-два
91351
91352 To download the sample data in an Excel file, click this link: Excel sample data workbook 91353
91352 The zipped file is in xlsx format, and does not contain any macros 91353
91352 91379 NOTE 91380: The
Total column contains values. You could change it to a formula, to multiply the Units and Cost columns. 91353
91366
91371 Copy and Paste +91372
91351
91352 Click at the end of the «Sample Data» heading, above the table (you will not see anything happen) 91353
91352 Scroll down to the end of the sample data table 91353
91352 Press Shift, and click at the end of the last number in the table, to select all the data 91353
91352 Press Ctrl + C to copy the data 91353
91352 Paste it into an Excel workbook, for use in your own tests.91353
91366

90007 Create an Excel Table 90008
90003 After you paste the sample data into Excel, you can format it as a named Excel table. This will make it easier to sort and filter the data. 90004
90003 Watch this short
video to see the steps, and there are written instructions on the Creating an Excel Table page. 90004

90007 Sample Data — Formatted Numbers 90008
90003 If you copy numbers such as 1-4 or 3/5 and paste them into Excel, they
will usually change to dates.90004
90003 For example, copy the numbers below, and
paste them onto a worksheet, to see how Excel adjusts them. 90004

90003 Learn how to paste this type of data, and keep the formatting — instructions
on the Data Entry Tips page. 90004

90007 Related Links 90008
90003 Excel Tables 90004
90003 Pivot Tables 90004
90003 Data Entry Tips 90004
90003 More Sample Files 90004
90003 Excel Topics 90004

90007 Do not Miss Our Excel Tips 90008
90003 Do not miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.90004
90003 91428 90004

90003 _____________________ 90004
90003 91433 90004

.90000 Data Tables in Excel — Easy Excel Tutorial 90001
90002 One Variable Data Table | Two Variable Data Table 90003
90002 Instead of creating different scenarios, you can create a 90005 data table 90006 to quickly try out different values ​​for formulas. You can create a one variable data table or a two variable data table. 90003
90002 Assume you own a book store and have 100 books in storage. You sell a certain% for the highest price of $ 50 and a certain% for the lower price of $ 20.If you sell 60% for the highest price, cell D10 below calculates a total profit of 60 * $ 50 + 40 * $ 20 = $ 3800.. 90003

90010 One Variable Data Table 90011
90002 To create a one variable data table, execute the following steps. 90003
90002 1. Select cell B12 and type = D10 (refer to the total profit cell). 90003
90002 2. Type the different percentages in column A. 90003
90002 3. Select the range A12: B17. 90003
90002 We are going to calculate the total profit if you sell 60% for the highest price, 70% for the highest price, etc.90003
90002 90003
90002 4. On the Data tab, in the Forecast group, click What-If Analysis. 90003
90002 90003
90002 5. Click Data Table. 90003
90002 90003
90002 6. Click in the ‘Column input cell’ box (the percentages are in a column) and select cell C4. 90003
90002 We select cell C4 because the percentages refer to cell C4 (% sold for the highest price). Together with the formula in cell B12, Excel now knows that it should replace cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the total profit, etc.90003
90002 90003
90002 Note: this is a one variable data table so we leave the Row input cell blank. 90003
90002 7. Click OK. 90003
90002 Result. 90003
90002 90003
90002 Conclusion: if you sell 60% for the highest price, you obtain a total profit of $ 3800, if you sell 70% for the highest price, you obtain a total profit of $ 4100 etc. 90003

90002 Note: the formula bar indicates that the cells contain an array formula.Therefore, you can not delete a single result. To delete the results, select the range B13: B17 and press Delete. 90003
90010 Two Variable Data Table 90011
90002 To create a two variable data table, execute the following steps. 90003
90002 1. Select cell A12 and type = D10 (refer to the total profit cell). 90003
90002 2. Type the different unit profits (highest price) in row 12. 90003
90002 3. Type the different percentages in column A.90003
90002 4. Select the range A12: D17. 90003
90002 We are going to calculate the total profit for the different combinations of ‘unit profit (highest price)’ and ‘% sold for the highest price’. 90003
90002 90003
90002 5. On the Data tab, in the Forecast group, click What-If Analysis. 90003
90002 90003
90002 6. Click Data Table. 90003
90002 90003
90002 7. Click in the ‘Row input cell’ box (the unit profits are in a row) and select cell D7.90003
90002 8. Click in the ‘Column input cell’ box (the percentages are in a column) and select cell C4. 90003
90002 We select cell D7 because the unit profits refer to cell D7. We select cell C4 because the percentages refer to cell C4. Together with the formula in cell A12, Excel now knows that it should replace cell D7 with $ 50 and cell C4 with 60% to calculate the total profit, replace cell D7 with $ 50 and cell C4 with 70% to calculate the total profit, etc. 90003
90002 90003
90002 9.Click OK. 90003
90002 Result. 90003
90002 90003
90002 Conclusion: if you sell 60% for the highest price, at a unit profit of $ 50, you obtain a total profit of $ 3800, if you sell 80% for the highest price, at a unit profit of $ 60, you obtain a total profit of $ 5200, etc. 90003
90002 Note: the formula bar indicates that the cells contain an array formula. Therefore, you can not delete a single result. To delete the results, select the range B13: D17 and press Delete.90003

.90000 Pivot Tables in Excel — Easy Excel Tutorial 90001
90002 Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table 90003
90002 90005 Pivot tables 90006 are one of 90005 Excel 90006 ‘s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. 90003
90002 Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.90003
90002 90003

90014 Insert a Pivot Table 90015
90002 To insert a 90005 pivot table 90006, execute the following steps. 90003
90002 1. Click any single cell inside the data set. 90003
90002 2. On the Insert tab, in the Tables group, click PivotTable. 90003
90002 90003
90002 The following dialog box appears. Excel automatically selects the data for you.The default location for a new pivot table is New Worksheet. 90003
90002 3. Click OK. 90003
90002 90003
90014 Drag fields 90015
90002 The 90005 PivotTable Fields pane 90006 appears. To get the total amount exported of each product, drag the following fields to the different areas. 90003
90002 1. Product field to the Rows area. 90003
90002 2. Amount field to the Values ​​area. 90003
90002 3.Country field to the Filters area. 90003
90002 90003
90002 Below you can find the pivot table. Bananas are our main export product. That’s how easy pivot tables can be! 90003
90002 90003

90014 Sort 90015
90002 To get Banana at the top of the list, sort the pivot table. 90003
90002 1. Click any cell inside the Sum of Amount column. 90003
90002 2. Right click and click on Sort, Sort Largest to Smallest.90003
90002 90003
90002 Result. 90003
90002 90003
90014 Filter 90015
90002 Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France? 90003
90002 1. Click the filter drop-down and select France. 90003
90002 Result. Apples are our main export product to France. 90003
90002 90003
90002 Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.90003
90014 Change Summary Calculation 90015
90002 By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps. 90003
90002 1. Click any cell inside the Sum of Amount column. 90003
90002 2. Right click and click on Value Field Settings. 90003
90002 90003
90002 3. Choose the type of calculation you want to use.For example, click Count. 90003
90002 90003
90002 4. Click OK. 90003
90002 Result. 16 out of the 28 orders to France were ‘Apple’ orders. 90003
90002 90003
90014 Two-dimensional Pivot Table 90015
90002 If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First, insert a pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.90003
90002 1. Country field to the Rows area. 90003
90002 2. Product field to the Columns area. 90003
90002 3. Amount field to the Values ​​area. 90003
90002 4. Category field to the Filters area. 90003
90002 90003
90002 Below you can find the two-dimensional pivot table. 90003
90002 90003
90002 To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.90003
90002 90003

.90000 Sample Excel Spreadsheets — Excel Templates 90001
90002 90003 90004 PT0030 — 90003 Pivot Table
Filter Markers 90004 — In Excel 2003 there are no markers on a pivot
table, to show which fields have been filtered. In this sample file
from AlexJ, a symbol appears above those fields, to help you identify
them. For details see the Contextures Blog article Add
Filter Markers in Excel Pivot Table. AlexJ_PivotFilter.zip
23 kb 19-Nov-10 90007
90002 90003 90004 PT0029 — 90003 Change Pivot Table Fields
on Specific Sheets 90004 — Change any page field in a pivot table,
and the same selections are made in all other pivot tables that
contain that page field.Specify which worksheets to change, and
which pivot tables and pivot fields to ignore. Uses Slicers, if
version is Excel 2010 or later. Sample code from Jeff Weir. PivotMultiPagesChange_JW.zip
45 kb 27-Aug-12 Updated 21-Jun-13 90007
90002 90003 90004 PT0028 — 90003 Change Specific Page Fields
with Multiple Selection Settings 90004 — Create a list of page fields
that should be automatically changed. Change any of the specified
page fields in a pivot table, and the same selections are made in
all other pivot tables that contain that page field.Also changes
the «Multiple Item Selection» settings to match changed
page fields. PivotMultiPagesChangeSet2010.zip
47 kb 15-Jul-12 90007
90002 90003 90004 PT0027 — 90003 Change All Page Fields
— All Sheets or Active Sheet 90004 — This sample file has 3 variations
on the «Change All Page Fields» code, which also changes
the «Multiple Item Selection» settings to match changed
page fields. For Excel 2007 and Excel 2010 only. 90025 1) Change any page field in a pivot table, and all matching page
fields, on all sheets, are changed.90025 2) Change any page field in a pivot table, and all matching page
fields, on the active sheet only, are changed. 90025 3) Change a specific page field in a pivot table, and that page
field, on the active sheet only, is changed. PivotMultiPagesChangeAllVar.zip
37 kb 11-Apr-12 90007
90002 4) Change specific page field in any pivot table, and that page
field is changed in all pivot tables in the workbook. PivotChangeAllSpecField.zip
16 kb 27-Aug-12 90025 90007
90002 90003 90004 PT0026 — 90003 Pivot Table Slicer Detail
90004 — With this pivot table tip from AlexJ, you can see the difference
when drilling to details in a pivot tables that has been filtered
with an Excel Slicer.If the filtered field is not in the pivot
table layout, results might not be what you expect. AlexJ_DoubleClick_Slicer.zip
19 kb 21-Mar-12 90007
90002 90003 90004 PT0025 — 90003 Change All Page Fields
with Multiple Selection Settings 90004 — Change any page field in
a pivot table, and the same selections are made in all other pivot
tables that contain the same page fields. Also changes the «Multiple
Item Selection «settings to match changed page fields. PivotMultiPagesChangeAll2010.zip
40 kb 28-Dec-11 90007
90002 90003 90004 PT0024 — 90003 Pivot Table from Multiple
Sheets — ADO version 90004 — Excel template from Excel MVP Kirill
Lapin (KL). Uses code to automatically create a pivot table from
multiple sheets in a workbook. Similar to sample file PT0023, but
based on ADO, and is less prone to error in different versions of
Excel. No manual refresh for pivot table. VBA_PT_NormalMultipleSheets
EN 06.zip 21 kb 28-Dec-11 90025 Plug and Play version: VBA_PT_NormalMultipleSheets
EN 07.zip 28 kb 28-Dec-11 90007
90002 90003 90004 PT0023A — 90003 Student Budget Workbook 90004
— Track and plan spending in the student budget from Bob Ryan,
of Simply Learning Excel. Review cash and credit card spending in
pivot tables that show monthly totals. Actual_Budget_Workbook_Basic.zip
16 kb 24-Aug-09 90007
90002 90003 90004 PT0023 — 90003 Pivot Table from Multiple
Sheets 90004 — Excel template from Excel MVPs, Kirill Lapin (KL),
with amendments by Hector Miguel Orozco Diaz.Details in blog post
Create
a Pivot Table from Multiple Sheets. Uses code to automatically
create a pivot table from multiple sheets in a workbook. VBA.MULT_CONS_PIVOT_EN.zip
16 kb 24-Aug-09 90007
90002 90003 90004 PT0022 — 90003 Filter Pivot Table for
Date Range 90004 — — Enter start and end dates on the worksheet,
and update the pivot table in this Excel template. Dates from that
range are selected in the Report Filter Date field PivotCodeDateFilter2003.zip
20 kb 28-Jan-09.
PivotCodeDateFilter2007.zip
27 kb 28-Jan-09 90007
90002 90003 90004 PT0021 — 90003 Change All Page Fields 90004
— Change any page field in the main pivot table, and the same selections
are made in all pivot tables that contain the same page fields.
Excel 2002 / 03- PivotMultiPagesChangeAll.zip
17 kb 04-May-08 Excel 2007-
PivotMultiPagesChangeAll2007.zip
17 kb 31-Jul-09 90007
.

Отправить ответ

avatar
  Подписаться  
Уведомление о