Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц

Рубрика: 7. Полезняшки Excel

Менее 1% всех пользователей Excel используют таблицы. И это странно, потому что таблицы очень удобны. Их дизайн выглядит профессионально. Они автоматически расширяются при добавлении новых данных, что делает их удобным источником для сводных таблиц, диаграмм и динамических диапазонов. Они содержат строку итогов с настраиваемыми вычислениями. Сортировка и фильтрация не требуют дополнительных манипуляций, так как область данных всегда известна. Формулы используют структурированные ссылки, что позволяет поддерживать однородные данные в столбцах. Книга будет полезна и новичку, который не знает, что такое таблица, и эксперту, который найдет примеры формул и кода VBA.

Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.

Zak Barress i Kevin Dzhons. Tablitsy Excel. Oblozhka

Скачать заметку в формате Word или pdf, примеры в формате Excel

Содержание

Глава 1. Что такое таблица

Глава 2. Поведение и анатомия таблиц

Глава 3. Работа с таблицами

Глава 4. Структурированные ссылки в таблицах

Глава 5. Сводные на основе таблиц

Глава 6. Сортировка, фильтрация, срезы

Глава 7. Форматирование таблиц

Глава 8. Работа с внешними данными

Глава 9. Автоматизация таблиц с помощью VBA

Глава 10. Таблицы в Excel Online

Глава 11. Таблиц на Mac, iPad и Office Mobile

На момент написания книги последней версией был Excel 2013. При переводе я использовал Microsoft Office 365 ProPlus образца 2019 г. В нем сделаны примеры и скриншоты. Оригинальные примеры на английском языке можно скачать с сайта авторов.

Глава 1. Что такое таблица

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

При импорте данных из внешнего источника, например куба OLAP, по умолчанию используется таблица Excel. Если вы используете Power Query, результаты запросов помещаются в таблицы Excel. По сути, Microsoft осознает универсальную привлекательность этого инструмента и использует таблицы Excel везде, где такая функциональность имеет смысл.

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

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

Таблицы появились в Excel начиная с версии 2007. Они стали формой представления по умолчанию для запросов куба OLAP и Power Query. Microsoft продолжает расширять и совершенствовать функциональные возможности таблиц.

Глава 2. Поведение и анатомия таблиц

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

Ris. 2. Skrytie stroki zagolovka

Рис. 2. Скрытие строки заголовка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Значения заголовка таблицы должны быть уникальными в пределах таблицы. При вводе значения заголовка, совпадающего с другим заголовком в той же таблице, Excel добавляет число в конец самого правого значения заголовка, чтобы сделать его уникальным. Например, если есть столбец с заголовком «ID», и вы вводите «ID» в другой столбец слева от существующего значения, самое правое значение заголовка, исходное «ID», изменяется на «ID2». Если вместо этого вы введете новый «ID» справа от существующего заголовка «ID», Excel изменит новый заголовок на «ID2».

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

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

Диапазон тела данных

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

Ris. 3. Diapazon tela dannyh

Рис. 3. Диапазон тела данных

Строка итогов

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

Ris. 4. Stroka itogov

Рис. 4. Строка итогов

Вычисляемый столбец

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

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

Ris. 5. Knopka Parametry avtozameny

Рис. 5. Кнопка Параметры автозамены

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

Ris. 6. Parametry avtozameny

Рис. 6. Параметры автозамены

Если на рис. 6 кликнуть на строке Параметры автозамены появится окно Автозамена, в котором можно настроить свойства всех вновь создаваемых таблиц:

Ris. 7. Avtozamena

Рис. 7. Автозамена

Мы не знаем простого способа определить, является ли столбец истинным вычисляемым столбцом. Попробуйте изменить формулу в одной из ячеек столбца и посмотрите, появится ли кнопка Параметры автозамены. Если столбец вычисляемый, формула применится ко всем ячейкам столбца, а в ячейке вы увидите кнопку Параметры автозамены, как на рис. 5. Если столбец не является вычисляемым, значения в других ячейках не пересчитаются, а в ячейке появится кнопка иного дизайна:

Ris. 8. Knopka Parametry avtozameny dlya sozdaniya vychislyaemogo stolbtsa

Рис. 8. Кнопка Параметры автозамены для создания вычисляемого столбца

Маркер изменения размера таблицы

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

Ris. 9. Marker razmera tablitsy

Рис. 9. Маркер размера таблицы

Вы также можете изменить размер таблиц на ленте, щелкнув на таблице, чтобы появилась контекстная вкладка Работа с таблицами. А затем пройти по меню Конструктор –> Свойства –> Размер таблицы. Откроется диалоговое окно Изменить размер таблицы. При изменении размера используйте указания, содержащиеся в окне. Изменять размер таблиц можно только в том случае, если лист не защищен.

Ris. 10. Okno Izmenit razmer tablitsy

Рис. 10. Окно Изменить размер таблицы

Поведение таблиц

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

Таблицы Excel не позволяют:

  • Иметь заголовки в более чем одной строке
  • Иметь итоги в более чем одной строке
  • Иметь повторяющиеся заголовки столбцов
  • Использовать формулы массива с несколькими ячейками (но допускаются формулы массива, возвращающие значения в одну ячейку)
  • Иметь ячейки, общие для нескольких таблиц
  • Иметь имя совпадающее с именем другой таблицы в книге

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

Ris. 11. Nekotorye razlichiya mezhdu spiskom dannyh i tablitsej Excel

Рис. 11. Некоторые различия между списком данных и таблицей Excel

Как таблицы взаимодействуют с другими объектами листа

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

Существует различие между вставкой строк в таблицу и добавлением строк в таблицу. Вставка строк означает выбор одной или нескольких ячеек и использование функции вставки Excel с помощью ленты или контекстного всплывающего меню, вызываемого правой кнопкой мыши (рис. 12). Добавление строк означает добавление строки в нижнюю часть таблицы одним из двух способов: 1) выделите нижнюю правую ячейку таблицы и нажмите клавишу TAB; 2) введите значение в любую ячейку в первой пустой строке под таблицей.

Ris. 12. Kontekstnoe menyu Vstavka

Рис. 12. Контекстное меню Вставка

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

Ris. 13. Dobavlenie strok v verhnyuyu tablitsu

Рис. 13. Добавление строк в верхнюю таблицу: а) исходное положение; б) добавлено две строки; добавление третей строки

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

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

Таблица или список?

Иногда бывает трудно определить, является ли диапазон ячеек частью таблицы. Выбор любой ячейки в таблице приводит к отображению контекстной вкладки Работа с таблицами. Если эта вкладка присутствует на ленте, активная ячейка находится в таблице. (Excel всегда отображает контекстную вкладку при выборе объекта, к которому эта вкладка имеет отношение. Например, при выборе сводной диаграммы Excel отображает две вкладки: Анализ и Конструктор.)

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

Таблицы Excel могут стать очень большими. При работе с большой таблицей может быть неудобно, если названия столбцов не отображается. Один из способов решить эту проблему – закрепить строку заголовка таблицы (меню Вид –> Закрепить области –> Закрепить верхнюю строку). Если эта опция не включена, Excel отображает заголовки таблицы в строке заголовка листа (где обычно отображаются буквы столбцов A, B, C и т.д.) до тех пор, пока активная ячейка находится внутри таблицы. Когда заголовки таблицы отображаются таким образом, доступны кнопки фильтра.

Имя таблицы

У каждой таблицы есть уникальное имя. Excel автоматически присваивает имя ТаблицаN, где N – номер для каждой новой таблицы. Например, Таблица1, Таблица2, … При копировании таблицы в другое место Excel использует имя исходной таблицы с добавлением цифр, чтобы гарантировать, что имя уникально в пределах области книги. Например, при последовательном копировании Таблица2 могут появиться Таблица212, затем Таблица21213. При импорте данных из внешней базы данных, такой как SQL Server или Access, имена таблиц по умолчанию имеют значение Table_QueryName, например, Table_Employees.

Вы можете изменить имя таблицы в любое время. Кроме того, имя таблицы:

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

Чтобы изменить имя таблицы, выделите любую ячейку таблицы, пройдите по меню Работа с таблицами –> Конструктор –> Имя таблицы. Введите имя таблицы в поле:

Ris. 14. Redaktirovanie imeni tablitsy na lente

Рис. 14. Редактирование имени таблицы на ленте

Если введенное имя не является уникальным или не соответствует правилам, Excel выведет сообщение об ошибке:

Ris. 15. Oshibka v imeni tablitsy

Рис. 15. Ошибка в имени таблицы

При изменении имени таблицы Excel автоматически применяет это изменение ко всем формулам, ссылающимся на таблицу по имени. Будьте осторожны при удалении таблицы, созданной в результате запроса. Когда вы это сделаете, Excel преобразует ссылки в формулах, ссылающихся на эту таблицу, в #ССЫЛКА!, и вы не можете отменить это без ручного редактирования каждой затронутой формулы.

Соглашение об именах

Традиционное соглашение об именовании называется венгерской нотацией. Венгерская нотация использует первые несколько букв имени для идентификации типа объекта (так называемый префикс типа). Эти буквы задаются в нижнем регистре. Они обычно представляют собой сокращенную форму типа объекта, например wkb для рабочей книги или wks для рабочего листа. Для таблиц обычно принято использовать либо tbl (для таблицы), либо lo (для списка). Программисты и пользователи Excel уже много лет используют префиксы типов. Хотя большинство современных стандартов больше не используют их, они могут сделать имена объектов более интуитивными.

Мы рекомендуем использовать смысловые имена вместо префиксных или венгерских обозначений. Такие имена как Данные, Активы или Расходы могут быть наиболее полезны. Помните о двух вещах при именовании таблиц:

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

Срезы

Начиная с версии 2010 в Excel появился новый инструмент фильтрации – срез, который является визуальным фильтром для сводных таблиц и сводных диаграмм. Начиная с версии 2013 срезы доступны и для таблиц. Также в этой версии появились срезы для шкал времени (подробнее см. Excel 2013. Срезы сводных таблиц; создание временной шкалы).

 

Комментарии: 2 комментария

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

Я обычный пользователь и использую умные таблицы. Это же очень удобно!


Прокомментировать