Таблицы Excel против диапазонов: что на самом деле меняет сочетание клавиш Ctrl+Т

1. Что на самом деле делает сочетание клавиш Ctrl+Т

Перед пояснением: ваши данные уже имеют заголовки, границы и фильтр. Скажите, что, по вашему мнению, на самом деле отличается после нажатия Ctrl + T, кроме полос.

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

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

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

2. Структурированные ссылки: именование столбцов вместо ячеек

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

=SUM(Orders[Revenue])                       the whole Revenue column
=SUMIFS(Orders[Revenue], Orders[Region], "North")
=[@Units] * [@UnitPrice]                    this row's Units times this row's UnitPrice
=Orders[#Headers]                           the heading row
=Orders[#All]                               headings and data together

Четыре части обозначений охватывают почти все. Orders[Revenue] представляет собой целый столбец данных без заголовков. [@Units], со знаком в означает «этот столбец в строке, в которой я сейчас нахожусь», и это то, что вам нужно в вычисляемом столбце. Orders[#Headers] — это строка заголовка, которую просматривает функция MATCH, когда поиск находит собственный столбец. И Orders[[Units]:[Revenue]] представляет собой блок соседних столбцов для функций, которым нужен прямоугольник.

Выигрыш не в скорости набора текста. Суть в том, что формулу может прочитать и проверить любой, кто не смотрит на лист. =SUMIFS($H$2:$H$17,$D$2:$D$17,"North") невозможно проверить, не пролистав до столбца D и не подсчитав; =SUMIFS(Orders[Revenue], Orders[Region], "North") говорит вслух, что он делает, и это неправильно, и вы это можете видеть. Эта читабельность также делает поиск по вставленному столбцу, поскольку именованный столбец остается самим собой независимо от того, куда он перемещается.

3. Расчетный столбец, который заполняется сам

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

=[@Units] * [@UnitPrice]

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

table, row 18, column H     =[@Units]*[@UnitPrice]      1100
plain range, row 18, col H  (empty)                     (empty)

Скажите вслух, сколько вам стоит версия с простым диапазоном, в рабочей книге, которая увеличивается каждую неделю. Не перепечатывание, которое занимает десять секунд. Проблема в том, что приведенная ниже сумма теперь неверна и ничто не указывает на это, поэтому стоимость не равна десяти секундам; дело в том, что кто-то читает число между ними.

4. Ребра, которые двигаются, доказаны в одном добавленном ряду.

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

=SUM(Orders[Revenue])       9890
=SUM(Plain!H2:H17)          9890

И правильные, и неотличимые. Теперь в первой пустой строке под каждым экземпляром набирается один новый приказ: приказ 1017, пять столов по 220. Больше ничего не трогается и ни одна формула не редактируется.

=SUM(Orders[Revenue])      10990
=SUM(Plain!H2:H17)          9890

Сумма таблицы переместилась на 1100, а диапазон - нет. Excel также сообщил, что собственный адрес таблицы увеличился с $A$1:$H$17 к $A$1:$H$18, и никто его не редактирует.

Обратите внимание, какая из этих двух ошибок является опасной. Формула, которая нарушается, заявляет о себе. Формула, которая продолжает возвращать правильно выглядящее число, которое на 1100 слишком мало, не дает результата, и разрыв постепенно увеличивается с каждой неделей поступления новых данных. Все, что находится ниже по течению, наследует его: повороты, построенные на $A$1:$H$17 никогда не увидите строку 18, диаграммы отображают старый диапазон, а именованные диапазоны, написанные от руки, остаются точно такими же широкими, как и в день их ввода. Постройте вращаться на столе и достаточно освежиться; постройте его по адресу, и вы каждый месяц будете повторно выбирать источник навсегда.

5. Строка «Итого» не является СУММОЙ.

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

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

=SUBTOTAL(109, Orders[Revenue])

нет SUM. SUBTOTAL с номером функции 109, что означает «суммировать и игнорировать все, что скрыто фильтром». Вот поведение, представленное рядом с обычной СУММОЙ в том же столбце, с семнадцатью заказами в таблице:

                        no filter    filtered to North
Total row  SUBTOTAL        10,990                3,595
Elsewhere  SUM             10,990               10,990

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

Две детали, которые стоит взять с собой. Номера функций серии 100 игнорируют отфильтрованные строки; эквиваленты 1-й серии, такие как SUBTOTAL(9, ...), не игнорируйте строки, которые вы скрыли вручную. А в раскрывающемся списке строки «Итого» предлагаются «Среднее», «Количество», «Макс», «Мин» и остальные — все в виде ПРОМЕЖУТОЧНЫХ ИТОГИ, поэтому ко всем из них применяется одинаковая фильтрация. Если вы хотите, чтобы в заголовке отображалась отфильтрованная цифра, пока люди кликают, это именно то сочетание, которое делает слайсеры ощущайте себя как панель мониторинга, а не как электронная таблица.

6. Имя и правила, которые Excel применяет к нему.

Новая таблица называется Table1, затем Table2, затем Table3. Эти имена бесполезны в формулах, и именно поэтому люди приходят к выводу, что структурированные ссылки нечитабельны: =SUM(Table3[Column2]) заслуживает своей репутации. Переименуйте ее в момент создания в поле «Имя таблицы» слева на вкладке «Дизайн таблицы».

Собственная документация Майкрософт устанавливает правила, которые обеспечивает соблюдение этого поля. Имя должно начинаться с буквы, подчеркивания или обратной косой черты, а в остальном могут использоваться буквы, цифры, точки и подчеркивания. Пробелы не допускаются. Имя может содержать до 255 символов и должно быть уникальным в книге, а заглавные буквы не делают его уникальным, поэтому Sales и SALES столкнуться. Четыре одиночные буквы зарезервированы и отклонены, C, c, R и r, поскольку Excel использует их как ярлыки для выбора столбца или строки. Все, что похоже на адрес ячейки, отсутствует по той же причине.

На практике: Orders, Products, tblStaff, Sales_2026. Назовите его в честь одной строки, в единственном или множественном числе, но последовательной, потому что это слово вы будете читать в каждой формуле в течение следующих двух лет.

7. Чего вам не позволит сделать стол

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

Конкретно вот что он отказывается или исправляет.

Объединенные ячейки идут. Таблица не может содержать объединенную ячейку, поэтому сочетание клавиш Ctrl+Т в декорированном блоке не будет вести себя так, как выглядел блок. Это почти всегда улучшение: объединенные ячейки — единственная наиболее распространенная причина, по которой сортировка или фильтр выдают бессмыслицу.

Каждый столбец получает уникальный непустой заголовок. Я дал Excel чтение строки заголовка Name, Value, Valueи пробел, затем нажмите Ctrl+Т. То, что вернулось, было Name, Value, Value2, Column3. Он переименовал дубликат и придумал заголовок для пробела, но не принял ни того, ни другого. Если в конце заголовка в вашей таблице застрял номер, который вы не вводили, это то, что произошло.

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

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

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

8. Превращаем его обратно в диапазон

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

Происходят две вещи, о которых стоит знать, прежде чем делать это. Форматирование сохраняется, поэтому лист выглядит идентично, что делает изменения невидимыми для следующего человека. Каждая структурированная ссылка в книге перезаписывается в адреса ячеек, поэтому =SUM(Orders[Revenue]) молча становится =SUM($H$2:$H$18). Формулы все еще работают сегодня и незаметно утратили то свойство, о котором идет вся эта страница. Если вы конвертируете, скажите об этом в комментарии к листу.

Крайние случаи, которые ловят людей

Шесть, о которых стоит знать, прежде чем они произойдут.

@, который появляется из ниоткуда. Ввод структурированной ссылки путем щелчка по ячейке иногда приводит к Orders[@Revenue] когда вам нужна была вся колонка. Знак в означает «только эта строка», поэтому сумма, построенная таким образом, возвращает цифру одной строки. Если сумма в таблице выглядит слишком маленькой, сначала найдите знак в.

Копирование формулы таблицы вбок. Orders[Revenue] переходит в Orders[UnitPrice] при перетаскивании вправо точно так же смещается относительная ссылка на ячейку. Зафиксируйте его, удвоив скобки, Orders[[Revenue]:[Revenue]], который является эквивалентом знака доллара со структурированной ссылкой.

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

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

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

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