Как строить таблицы в Excel
В этой статье мы затронем самые частые темы, которые встречаются при работе с таблицами excel. Мы рассмотрим, как нарисовать таблицу в excel, как транспонировать (перевернуть) таблицу в excel, покажем, как сортировать таблицу в excel, а также как продлить, отформатировать и напечатать.
Работа в Microsoft Excel во многом состоит из построения, оформления и вычисление таблиц.
Практически каждый, кто открывает Excel сталкивается с задачами создания, оформления, преобразования, сортировки и печати таблиц.
В этой статье мы затронем самые частые темы, которые встречаются при работе с таблицами excel. Мы рассмотрим, как нарисовать таблицу в excel, как транспонировать (перевернуть) таблицу в excel, покажем, как сортировать таблицу в excel, а также как продлить, отформатировать и напечатать.
Как сводные таблицы Excel помогут сэкономить ваше время? Блог SF Education
После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения. Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).
Форматирование диапазона в виде таблицы
Если ваш диапазон данных еще не отформатирован в виде таблицы, мы рекомендуем вам сделать это. Данные, хранящиеся в таблицах, имеют множество преимуществ по сравнению с данными в диапазонах ячеек на листе, особенно при использовании сводных таблиц (узнать больше о преимуществах использования таблиц ).
Чтобы отформатировать диапазон в виде таблицы, выберите диапазон ячеек и нажмите «Вставить»> «Таблица».
Убедитесь, что диапазон правильный, что у вас есть заголовки в первой строке этого диапазона, а затем нажмите «ОК».
Диапазон теперь отформатирован как таблица. Присвоение имени таблице упростит обращение к ней в будущем при создании сводных таблиц, диаграмм и формул.
Щелкните вкладку «Дизайн» в разделе «Работа с таблицами» и введите имя в поле в начале ленты. Эта таблица получила название «Продажи».
Вы также можете изменить здесь стиль таблицы, если хотите.
Как сделать сводную таблицу в Excel |
В этой статье мы затронем самые частые темы, которые встречаются при работе с таблицами excel. Мы рассмотрим, как нарисовать таблицу в excel, как транспонировать (перевернуть) таблицу в excel, покажем, как сортировать таблицу в excel, а также как продлить, отформатировать и напечатать.
Как создать сводную таблицу?
В появившемся окне вы должны указать область или имя таблицы. Обычно оно уже указано по умолчанию. Также можно выбрать: создать сводный список на текущем листе или на новом. Для удобства откроем еще один лист.
На новом листе появится область для будущей таблицы и список полей. Среди них вы увидите заголовки столбцов с предыдущего листа.
Чтобы сформировать сводную таблицу, надо определить, по какому критерию мы будем ее собирать. В нашем случае фильтром отчета может быть Заказчик. Надо захватить этот заголовок и с помощью мыши перетащить его в соответствующую область.
Сводные таблицы очень часто используют для контроля и составления отчетов, ведь с их помощью можно быстро анализировать данные по многим параметрам. Например, нам необходимо видеть, сколько было куплено фруктов. Для этого в качестве названия строк используем Товар. Теперь выбранные поля будут отмечены галочками, а в области таблицы уже начнет вырисовываться понятный список.
Здесь можно указать период времени, за который будет отображаться информация, а также шаг: дни, месяцы, кварталы и т.д.
Чтобы заполнить сводную таблицу данными, перетащим в область значений поле Стоимость. Теперь наш документ выглядит как полноценная таблица. Она отображает стоимость товара, проданного в определенные даты.
Сводная таблица готова, а значит, можно начать с ней работать. Разберемся с тем, для чего нужен фильтр отчета. Для этого кликнем по ячейке «Все». В выпадающем списке появятся наши заказчики. Можно отсортировать таблицу, выкинув из нее данные по одному из них.
Если выбрать Гену, в таблице будут указаны только те фрукты, которые купил он. Представьте теперь, что в списке не два, а сто заказчиков и несколько сотен товаров: в таком случае не обойтись без сводной таблицы.
Сортировать можно не только по фильтру отчета, но и наименованию строк и столбцов. Например, если нас интересует результат продажи определенных товаров, мы можем изменить сводную таблицу, выделив только те строки, которые хотим.
Кроме того, у поля значений есть дополнительные параметры отображения информации. Чтобы открыть окно с их настройками, кликните по названию вашего значения — у нас это Сумма по полю Стоимость. В разделе с параметрами можно выбрать не сумму, а количество. Тогда вместо стоимости товара будет показано, сколько раз его покупали.
Все поля можно менять местами, просто перетаскивая их мышкой. Например, изменим фильтр отчета сводной таблицы: поставим на его место дату, а поле с заказчиком перенесем в названия столбцов. Таблица моментально перестроится и примет следующий вид:
Мы также можем сортировать списки по строкам, столбцам и фильтру отчета. Например, если отфильтровать таблицу по дате, мы увидим только те продажи, которые произошли в выбранный день.
Сводные таблицы открывают для пользователя много новых и полезных возможностей, которые сильно упрощают работу в Excel. Начав с простого примера и освоив эту функцию, вы сможете с легкостью оперировать большими объемами данных и быстро создавать отчеты по разным критериям.
Как обновить сводную таблицу в Excel — Советы по работе со сводными таблицами — Как в офисе.
Полученный результат показан на рис. 22. Как видите, теперь каждый показатель отформатирован корректно. Конечно, приведенный в этом совете рецепт не универсален. Скорее, он указывает направление, в котором стоит экспериментировать.
Как обновить сводную таблицу в Excel
В настоящей заметке представлена коллекция простых и изящных инструментов работы со сводными таблицами в Excel. То, что по-английски называется tips & tricks. Выделите время и ознакомьтесь с приводимыми здесь советами. [1] Кто знает, может быть, вы наконец-то найдете ответ на долго мучивший вас вопрос?
Совет 1. Автоматическое обновление сводных таблиц
Иногда требуется, чтобы сводные таблицы обновлялись автоматически. Предположим, вы создали сводную таблицу для менеджера. Вряд ли вы сможете регулярно обновлять ее, разве что менеджер допустит вас к своему ноутбуку. Можно включить автоматическое обновление сводной таблицы, которое будет выполняться всякий раз при открытии книги:
- Щелкните правой кнопкой мыши на сводной таблице и в контекстном меню выберите пункт Параметры сводной таблицы.
- В появившемся диалоговом окне Параметры сводной таблицы выберите вкладку Данные.
- Установите флажок Обновить при открытии файла.
Флажок Обновить при открытии файла следует устанавливать для каждой сводной таблицы отдельно.
Скачать заметку в формате Word или pdf, примеры в формате Excel (файл содержит код VBA).
Совет 2. Одновременное обновление всех сводных таблиц книги
Если в рабочей книге содержится несколько сводных таблиц, одновременное их обновление может быть проблематичным. Существует несколько способов преодолеть эти трудности:
Способ 1. Можно выбрать для каждой сводной таблицы, входящей в состав рабочей книги, настройку, задающую автоматическое обновление при открытии книги (подробнее см. Совет 1).
Способ 3. Воспользуйтесь кодом VBA для обновления всех сводных таблиц в рабочей книге по требованию. Данный подход предусматривает использование метода RefreshAll объекта Workbook. Для использования этой методики создайте новый модуль и введите следующий код:
Учтите, что метод RefreshAll наравне со сводными таблицами обновляет все внешние диапазоны данных. Если рабочая книга содержит данные из внешних источников, например, базы данных или внешние файлы, все они будут обновлены вместе со сводными таблицами (подробнее о записи кода VBA см. VBA в сводных таблицах).
Совет 3. Сортировка элементов данных в произвольном порядке
Совет 4. Преобразование сводной таблицы в жестко заданные значения
Для преобразования части сводной таблицы выполните следующие действия:
- Выделите копируемые данные сводной таблицы, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать (или наберите на клавиатуре Ctrl+C).
- Щелкните правой кнопкой мыши в произвольном месте рабочего листа и в контекстном меню выберите команду Вставить (или наберите Ctrl+V).
Если нужно преобразовать всю сводную таблицу, выполните следующие действия:
- Выделите всю сводную таблицу, щелкните правой кнопкой мыши и в контекстном меню выберите пункт Копировать. Если сводная не содержит область ФИЛЬТРЫ, то для выделения области сводной таблицы можно воспользоваться клавиатурным сокращением Ctrl+Shift+*.
- Щелкните правой кнопкой мыши в произвольном месте листа и в контекстном меню выберите параметр Специальная вставка.
- Выберите параметр Значения и щелкните ОК.
Совет 5. Заполнение пустых ячеек в полях СТРОКИ
После преобразования сводной таблицы на листе отображаются не только значения, но и вся структура данных сводной таблицы. Например, данные, показанные на рис. 3, были получены на основе сводной таблицы с макетом в табличной форме.
Рис. 3. Использовать эту преобразованную сводную таблицу без заполнения пустых ячеек в левой части проблематично
Рис. 4. После применения команды Повторять все подписи элементов заполняются все пустые ячейки
Совет 6. Ранжирование числовых полей сводной таблицы
После создания ранга можно настроить подписи полей и форматирование (рис. 14.9). В результате будет получен красивый ранжированный отчет.
Рис. 5. Создайте сводную таблицу, в которой объем продаж в области ЗНАЧЕНИЯ выводится дважды
Рис. 6. Сортировка от максимальных значений к минимальным с помощью дополнительных вычислений
Совет 7. Уменьшение размера отчета сводной таблицы
Совет 8. Создание автоматически развертываемого диапазона данных
Совет 9. Сравнение обычных таблиц с помощью сводной таблицы
Рис. 10. На основе двух исходных таблиц создается одна результирующая
Рис. 11. Создайте сводную таблицу, которая позволяет визуально сравнить два набора данных
Совет 10. Автоматическая фильтрация сводной таблицы
Рис. 12. Трюк по использованию автофильтра в сводной таблице
Совет 11. Преобразование наборов данных, отображаемых в сводных таблицах
Рис. 13. Эту таблицу в матричном стиле следует преобразовать в табличный набор данных
Для устранения этой проблем можно воспользоваться в качестве промежуточного этапа сводной таблицей с несколькими консолидированными диапазонами (подробнее см. Сводная таблица на основе нескольких листов или диапазонов консолидации). Для преобразования набора данных, имеющего матричный стиль, в набор данных, более подходящий для создания сводных таблиц, выполните следующие действия.
Рис. 14. Результат конкатенации столбцов Рынок сбыта и Описание услуги
После создания конкатенированного столбца преобразуйте формулы в значения. Для этого выделите только что созданный столбец, нажмите Ctrl+C, после чего выполните команду Вставить -> Специальная вставка -> Значения. Теперь можно удалить столбцы Рынок сбыта и Описание услуги (рис. 15).
Рис. 15. Удалены столбцы Рынок сбыта и Описание услуги
Рис. 16. Сводная на основе нескольких диапазонов консолидации
Рис. 18. Этот набор данных идеально подходит для создания сводной таблицы (сравните с рис. 13)
Совет 12. Включение двух числовых форматов в сводную таблицу
А теперь рассмотрим ситуацию, когда нормализованный набор данных затрудняет построение удобной для анализа сводной таблицы. Примером может служить показанная на рис. 19 таблица, которая включает два разных показателя для каждого рынка сбыта. Обратите внимание на столбец D, который идентифицирует показатель.
Рис. 19. Эта таблица включает несколько типов данных для одного поля из области значений
Рис. 20. Каждому показателю можно присвоить только один числовой формат
Для решения этой проблемы применяется пользовательский числовой формат, который любое значение, большее 1,5, форматирует как число. Если же значение меньше 1,5, оно форматируется как процент. В диалоговом окне Формат ячеек выберите вкладку (все форматы) и в поле Тип введите следующую форматирующую строку (рис. 21): [>=1,5]$# ##0; [
Рис. 21. Примените пользовательский числовой формат, в котором любые числа, меньшие 1,5, форматируются как проценты
Полученный результат показан на рис. 22. Как видите, теперь каждый показатель отформатирован корректно. Конечно, приведенный в этом совете рецепт не универсален. Скорее, он указывает направление, в котором стоит экспериментировать.
Совет 13. Создание частотного распределения для сводной таблицы
Щелкните правой кнопкой мыши на любом значении в области строк и в контекстном меню выберите параметр Группировать. В диалоговом окне Группирование (рис. 24) определите значения параметров, определяющих начало, конец и шаг частотного распределения. Щелкните ОК.
Рис. 24. В диалоговом окне Группирование настройте параметры частотного распределения
Если в сводную таблицу добавить поле Заказчик (рис. 25), получим частотное распределение транзакций заказчиков относительно размера заказов (в долларах).
Рис. 25. Теперь в вашем распоряжении оказалось распределение транзакций заказчиков в соответствии с размерами заказов (в долларах)
Совет 14. Использование сводной таблицы для распределения набора данных по листам книги
Рис. 27. Начните с создания сводной таблицы, которая содержит поле фильтра
Рис. 28. Щелкните на кнопке Отобразить страницы фильтра отчета
В появившемся диалоговом окне (рис. 29) можно выбрать поле фильтра, для которого будут созданы отдельные сводные таблицы. Выберите подходящее поле фильтра и щелкните ОК.
Рис. 29. Диалоговое окно Отображение страниц фильтра отчета
Для каждого элемента поля фильтра будет создана сводная таблица, помещенная на отдельный лист (рис. 30). Обратите внимание на то, что ярлычки листов называются так же, как и элементы поля фильтра. Учтите, что параметр Отобразить страницы фильтра может применяться к полям фильтра поочередно.
Рис. 30. Отдельная сводная таблица для каждого рынка сбыта
Совет 15. Использование сводной таблицы для распределения набора данных по отдельным книгам
‘Изменение названия сводной таблицы (при необходимости)
‘Циклический обход каждого элемента выделенного поля
‘Копирование данных в новую книгу и удаление временного листа
Введите этот код в новый модуль VBA. Проверьте значения следующих констант и переменных и в случае необходимости измените их:
В результате выполнения кода VBA данные для каждого рынка сбыта будут сохранены в отдельной книге.
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 14.Как сделать сводную таблицу в Excel?.
Но перед тем, как начать пользоваться всеми «профитами» от Сводных таблиц Excel, нужно приложить немало усилий, чтобы хорошо освоить довольно сложную технику создания таблиц и работы с ними. Но в итоге, эффект от приобретения нового навыка, вот увидите, окупит потраченное время, энергию и силы.
Как обновить сводную таблицу в Excel?
Представим, что в исходные данные с продажами внесли изменения и нам нужно обновить сводную таблицу.
В этом случае встаем в любую из ячеек сводной таблицы (в панели вкладок появится блок Работа со сводными таблицами) и далее выбираем Анализ -> Данные -> Обновить:
Однако же, если в начальные данные добавили новые строки (к примеру, появились данные за новые периоды), то такой способ уже не сработает.
Потому что при создании сводной таблицы мы задали фиксированный диапазон с исходными данными и новые данные в него уже не попадают. Поэтому в случае добавления новых данных нужно обновить диапазон в качестве источника для таблицы. Для этого в панели вкладок переходим в Анализ -> Данные -> Источник данных и задаем новый диапазон.
Поэтому будьте крайне внимательны при работе с таблицей, так как достаточно легко забыть обновить источник данных и не отследить количество задействованных строк и столбцов.
Эту проблему можно решить задав в качестве источника данных не фиксированный диапазон (как в примере выше мы использовали ссылку $A$1:$G$820), а целиком выделить все столбцы, не ограничивая таблицу по высоте (т.е. использовать ссылку $A:$G).
Однако такой вариант уже не подойдет, если мы захотим добавить не строку, а именно столбец в исходные данные (к примеру, у нас добавится еще 1 метрика, которую мы захотим смотреть и анализировать).
Но и для этой проблемы есть решение, давайте воспользуемся умной таблицей и несколькими полезными свойствами, которая она дает.
Как растянуть таблицу в excel.
- Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
- Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.
Предположу, что нет ни одного человека знакомого с Excel, который бы не слышал про сводные таблицы. Данный инструмент является одним из самых главных и полезных в плане умения обработать и структурировать большие объемы данных.