Осваивая работу с таблицами, вы вскоре обнаружите, что они предлагают ряд преимуществ по сравнению со стандартными базами данных рабочего листа. Главное преимущество использования таблиц – это упрощение форматирования. Об этом речь пойдет далее.
Если в формулах используется множество именованных диапазонов, синтаксис таблицы является прекрасной альтернативой для создания имен столбцов и всей таблицы в целом. К тому же при внесении изменений в таблицу именованные диапазоны автоматически корректируются соответствующим образом.
Еще одно преимущество относится к работе с диаграммами. Если диаграмма создается на основе таблицы, ряды диаграммы автоматически расширяются при добавлении в таблицу новых данных.
Если ваша компания использует службу SharePoint, вы получите в руки дополнительное преимущество – сможете публиковать таблицу на сервере SharePoint. Для этого выберите в контекстной вкладке команду Работа с таблицами→Конструктор→Данные из внешней таблицы→Экспорт→Экспорт таблицы в список SharePoint. Эта команда открывает диалоговое окно, в котором можно ввести адрес сервера, а также предоставить дополнительную информацию, необходимую для публикации таблицы.
В то же время таблицы, по сравнению с базами данных рабочего листа, имеют ряд ограничений.
Ограничения таблиц
Таблицы Excel имеют ряд преимуществ по сравнению с обычными базами данных рабочего листа. Однако по определенным причинам создатели инструментария таблиц наложили на таблицы некоторые ограничения.
• Если рабочий лист содержит таблицу, невозможно создать и применять пользовательские представления.
• Таблица не может содержать формулы массивов, распространяющиеся на несколько ячеек.
• Нельзя автоматически вставлять промежуточные итоги.
• Нет возможности совместно использовать рабочую книгу, содержащую таблицу.
• Нельзя отслеживать изменения в рабочей книге, содержащей таблицу.
• Нельзя также использовать команду Главная→Выравнивание→Объединить и поместить в центре, так как она искажает разделение на строки и столбцы.
Если одно из этих ограничений нарушает ваши планы, преобразуйте таблицу в обычную базу данных рабочего листа с помощью команды Конструктор→Сервис→Преобразовать в диапазон.
Создание таблиц
Excel позволяет создавать таблицы из пустого диапазона ячеек, однако чаще пользователи преобразуют в таблицы уже существующие диапазоны данных (базы данных рабочего листа). Следующие инструкции предполагают, что уже существует диапазон данных, подходящий для создания таблицы.
1. Убедитесь, что диапазон не содержит пустых строк и столбцов.
2. Активизируйте любую ячейку в диапазоне.
3. Выберите команду Вставка→Таблицы→Таблица (или нажмите <Ctrl+T>).
В открывшемся диалоговом окне Excel отобразит адрес диапазона данных, определенный программой эмпирическим методом. Как правило, программа идентифицирует диапазон таблицы корректно; если это не так, исправьте адреса, и только затем щелкните на кнопке ОК.
После щелчка на кнопке ОК таблица будет автоматически отформатирована, и в ней будет включен режим фильтрации. На ленте Excel отобразит контекстную вкладку Работа с таблицами, Конструктор, элементы которой содержат команды, предназначенные для работы с таблицами.
Существует еще один метод преобразования диапазона в таблицу – выберите команду Главная→Стили→Форматировать как таблицу.
В диалоговом окне создания таблицы Excel может принять некорректное решение относительно адреса диапазона, если таблица не отделена от остальной информации рабочего листа хотя бы одной пустой строкой или столбцом. В этом случае скорректируйте поле адреса диапазона или щелкните на кнопке Отмена и реорганизуйте рабочий лист так, чтобы отделить таблицу от остальной информации пустой строкой и столбцом.
Изменение внешнего вида таблицы
При создании таблицы Excel применяет к ней стиль, принятый по умолчанию. Ее внешний вид в данном случае зависит от того, какая тема документа была использована для рабочей книги. Если данный внешний вид вас не устраивает, можете без труда его изменить.
Выделите любую ячейку в таблице и выберите команду Работа с таблицами→Конструктор→Стили таблиц→Экспресс-стили. Вначале на ленте будет отображена всего одна строка стилей, однако если щелкнуть в нижней части полосы вертикальной прокрутки, раскроется вся галерея стилей, разбитая на группы Светлый, Средний и Темный. Обратите внимание, что при перемещении указателя мыши по галерее активизируется функция оперативного просмотра (т.е. ко всей таблице временно применяется текущий стиль). Если вас устраивает какой-то стиль, щелкните мышью, и он будет применен к таблице.
Если во вкладке Разметка страницы выбрать команду Темы, откроется галерея, в которой можно выбрать тему для всей рабочей книги. Тема, в частности, определяет и состав стилей таблиц в галерее стилей.
Если применение стиля не срабатывает или работает некорректно, скорее всего, к диапазону до преобразования в таблицу уже был применен какой-либо стиль (табличное форматирование не замещает собой обычное). Для очистки текущего форматирования цвета фона выделите всю таблицу и выберите команду Главная→Шрифт→Цвет заливки→Нет заливки. Для очистки текущего цвета шрифта выберите команду Главная→Шрифт→Цвет текста. После выполнения этих команд стили таблицы будут применяться корректно.
Навигация и выделение в таблице
Перемещение по ячейкам таблицы практически ничем не отличается от перемещения по ячейкам рабочего листа. Единственное отличие заключается в функциональности клавиши табуляции. Нажатие клавиши <Таb> приводит к переходу к соседней ячейке справа, а по достижению правой границы таблицы (но не диапазона) нажатие клавиши <Таb> приводит к переходу к первой ячейке слева в следующей строке.
Во время перемещения указателя мыши по таблице его форма динамически изменяется. Форма указателя помогает выделить различные фрагменты таблицы.
• Для выделения всего столбца переместите указатель мыши к верхней границе ячейки заголовка столбца; при этом его форма изменится на направленную вниз черную стрелку. После первого щелчка мышью будут выделены все данные в столбце; после второго – все ячейки столбца, включая заголовок. Для выделения нескольких столбцов следует удерживать нажатой клавишу <Ctrl> при щелчке мышью на каждом из них.
• Для выделения всей строки переместите указатель мыши к левой границе первой ячейки строки таблицы; при этом указатель мыши изменит свою форму на направленную вправо стрелку. Щелчок приведет к выделению всей строки таблицы. Для выделения строки таблицы можно также использовать комбинацию клавиш <Shift+пробел>.
• Для выделения всей таблицы переместите указатель мыши к левому верхнему углу крайней левой верхней ячейки таблицы. Когда указатель примет форму диагональной стрелки, щелкните мышью, и будет выделен весь диапазон данных таблицы. Второй щелчок приведет к выделению всей таблицы, включая строки заголовков и итогов. Также для выделения всей таблицы можно воспользоваться клавишами <Ctrl+A>.
При щелчке правой кнопкой мыши на ячейке таблицы открывается контекстное меню, содержащее связанные с таблицей команды.
Добавление строк и столбцов в таблицу
Для того, чтобы добавить новый столбец в конец таблицы, активизируйте ячейку в столбце справа от таблицы и начните ввод данных. Excel автоматически расширит таблицу по горизонтали.
Аналогично, если начать вводить данные в строке сразу под таблицей, таблица будет увеличена по вертикали и включит в себя новую строку. Исключение составляет ситуация, когда таблица содержит строку итогов. Если начать вводить данные под строкой итогов, таблица увеличена не будет.
Для того, чтобы добавить строки и столбцы, во внутренней части таблицы щелкните правой кнопкой и выберите в контекстном меню пункт Вставить. Откроется подменю, предлагающее вставку столбца слева или строки выше текущей ячейки.
Когда курсор ячейки находится в крайней нижней правой ячейке таблицы, нажатие клавиши <Таb> приводит к вставке новой строки внизу таблицы.
При помещении указателя мыши над маркером в правом нижнем углу таблицы его форма изменяется на диагональную линию с двумя стрелками. Щелкните мышью и перетащите маркер вниз, и в таблицу будут добавлены новые строки. Щелкните мышью и перетащите этот маркер вправо, и в таблицу будут добавлены новые столбцы.
При вставке в таблицу нового столбца в строку заголовка помещается имя Столбец 1, Столбец 2 и т.д. Эти имена лучше заменить на что-либо более информативное.
Удаление строк и столбцов
Для удаления строки (или столбца) таблицы выделите в ней любую ячейку. Если нужно удалить несколько строк или столбцов, выделите несколько соответствующих ячеек. После этого щелкните правой кнопкой и выберите в контекстном меню команду Удалить→Строки таблицы или Удалить→Столбцы таблицы.
Запоминание параметров строк и столбцов
При выполнении каких-либо операций над строкой или столбцом таблицы программа Excel запоминает их параметры и распространяет на добавляемые строки и столбцы. К примеру, если к столбцу был применен денежный формат, а затем в таблицу была добавлена новая строка, денежный формат будет применен и к значению, введенному в этот столбец новой строки.
То же относится и к другим операциям, таким как условное форматирование, защита ячеек, проверка данных и т.п. Если создается диаграмма на основе данных таблицы, она будет автоматически расширена при добавлении в таблицу новых данных. Пользователи, которые работали с предыдущими версиями Excel, наверняка оценят эту новую функциональность.
Перемещение таблицы
Для перемещения таблицы в новое место в пределах рабочего листа поместите указатель мыши над одной из ее границ. Когда форма указателя изменится на перекрестие с четырьмя стрелочками, щелкните мышью и перетащите таблицу в новое место.
Для перемещения таблицы на другой рабочий лист (той же или другой рабочей книги) выполните следующее.
1. Дважды нажмите клавиши <Ctrl+A>. Будет выделена вся таблица.
2. Нажмите <Ctrl+X> для вырезания выделенных ячеек.
3. Активизируйте другой рабочий лист и выделите ячейку, которая будет служить верхним левым углом таблицы.
4. Нажмите <Ctrl+V>, и таблица будет вставлена.
Настройка параметров таблицы
Группа Параметры стилей таблиц контекстной вкладки Работа с таблицами содержит несколько флажков, определяющих сокрытие или отображение некоторых элементов таблицы, а также некоторые параметры форматирования.
• Строка заголовков. Отображение строки заголовков.
• Строка итогов. Отображение строки итогов.
• Первый столбец. Включение специального форматирования первого столбца.
• Последний столбец. Включение специального форматирования последнего столбца.
• Чередующиеся строки. Включение отображения четных и нечетных строк с разными цветами фона.
• Чередующиеся столбцы. Включение отображения четных и нечетных столбцов с разными цветами.
Использование форм данных
Для облегчения обработки данных рабочего листа или таблицы программа Excel может отобразить диалоговое окно форм данных, которое позволит вводить новые данные, удалять строки, а также искать строки, удовлетворяющие заданным критериям. Это окно работает либо с базами данных, либо с диапазонами, преобразованными в таблицы.
К сожалению, команда доступа к формам данных не вынесена на ленту; ее можно установить только на панели быстрого доступа. Для этого выполните следующие действия.
1. Щелкните правой кнопкой на панели быстрого доступа и выберите в меню команду Настройка панели быстрого доступа. Откроется диалоговое окно параметров Excel с выбранной вкладкой Настройка.
2. В раскрывающемся списке Выбрать команды из выберите пункт Команды не на ленте.
3. В левом списке выберите элемент Форма.
4. Щелкните на кнопке Добавить, чтобы добавить этот элемент на панель быстрого доступа.
5. Щелкните на ОК, чтобы закрыть диалоговое окно параметров программы.
Удаление дублирующихся строк из таблицы
Если в таблице есть дублирующиеся строки, можете удалить ненужные копии. В прошлом удаление дублирующихся строк выполнялось в основном вручную, однако теперь Excel максимально облегчила пользователям решение этой задачи.
Начните с выделения любой ячейки в таблице. После этого выберите команду Работа с таблицами→Конструктор→Сервис→Удалить дубликаты. Откроется диалоговое окно, показанное на рисунке. В этом диалоговом окне перечислены все столбцы таблицы. Установите флажки в тех столбцах таблицы, в которых нужно найти дубликаты значений. По умолчанию установлены флажки во всех столбцах, и, как правило, этот режим чаще всего и выбирают. Щелкните на кнопке ОК, и Excel удалит из таблицы дублирующиеся строки, после чего отобразит сообщение о том, сколько строк было удалено.
К сожалению, Excel не предоставляет возможности увидеть дублирующиеся строки перед их удалением. Если результат “очистки” таблицы вас не устроит, можете отменить выполненную операцию, щелкнув на соответствующей кнопке панели быстрого доступа или нажав <Ctrl+Z>.
Для того, чтобы удалить дубликаты из базы данных рабочего листа, не являющегося таблицей, выберите команду Данные→Работа с данными→Удалить дубликаты.
Предупреждение
Дублирующиеся значения определяются по содержимому, отображаемому в ячейках, а не по фактическим данным, в них хранящимся. Для примера предположим, что одна из дат отображается как 15.05.2010, а в другой ячейке та же дата отображается как 15 мая 2010 г. При удалении дубликатов Excel будет трактовать эти даты как разные. Поэтому рекомендуется использовать во всех ячейках столбца один и тот же формат.
Сортировка и фильтрация в таблице
Строка заголовков таблицы содержит кнопки со стрелками, щелчок на которых приводит к отображению параметров сортировки и фильтрации, зависящих от типа столбца. Например, после щелчка на кнопке в заголовке столбца Дата заявки открывается меню с параметрами сортировки дат. Когда столбец отсортирован или отфильтрован, рисунок на кнопках в строке заголовка изменяется, чтобы напомнить вам о том, что отображение строк таблицы зависит от данного столбца.
Если вы работаете с базой данных рабочего листа, а не с таблицей, для добавления стрелок к ячейкам строки заголовков выберите команду Данные→Сортировка и фильтр→Фильтр. Эта команда работает как переключатель, так что если захотите впоследствии скрыть стрелки, достаточно будет еще раз воспользоваться этой же командой. Эту же команду можно использовать для сокрытия стрелок в строке заголовков таблицы.
Сортировка таблицы
Сортировка таблицы реорганизует ее строки, основываясь на содержимом заданного столбца (столбцов). К примеру, сортировка может понадобиться, чтобы упорядочить фамилии сотрудников по алфавиту или по суммам продаж.
Для сортировки таблицы по некоторому столбцу щелкните на стрелке в заголовке этого столбца и выберите одну из команд сортировки. Состав этих команд варьируется в зависимости от типа столбца. Если в столбце содержится текст, будут предложены команды Сортировка от А до Я и Сортировка от Я до А. Если в столбце содержатся числовые или логические данные, будут предложены команды Сортировка от минимального к максимальному и Сортировка от максимального к минимальному. Если в столбце содержатся даты, будут предложены команды Сортировка от старых к новым и Сортировка от новых к старым.
В любом случае можно также воспользоваться командами сортировки по цвету фона или текста. Этот вариант уместен только в том случае, если изменены цвета стиля таблицы или используется условное форматирование, зависящее от содержимого ячеек.
Если таблица отсортирована по некоторому столбцу, кнопка в его заголовке дополняется стрелочкой, указывающей тип сортировки (по возрастанию или по убыванию).
Сортировку можно выполнять по любому количеству столбцов. Вся хитрость в данном случае заключается в том, чтобы первым выбрать наименее важное (последнее) условие сортировки, затем более важное и т.д. Наиболее “важный” столбец выбирается последним.
К примеру, в рабочей таблице агентства недвижимости может потребоваться сортировка по маклерам, занимающимся конкретными объектами, затем по регионам, в которых эти объекты находятся, и, в заключение, по ценам объектов. В этом случае сортировку придется вначале выполнить по цене, затем по месту расположения объекта и, в последнюю очередь, по маклерам. На рисунке показана таблица, отсортированная таким образом.
Еще один способ сортировки по множеству столбцов сводится к использованию специального диалогового окна Сортировка. Для открытия этого окна выберите команду Главная→Редактирование→Сортировка и фильтр→Настраиваемая сортировка. Также можно щелкнуть правой кнопкой на любой ячейке таблицы, после чего выбрать в контекстном меню пункт Сортировка→Настраиваемая сортировка.
В диалоговом окне Сортировка используйте раскрывающиеся списки для выбора первого критерия сортировки. Обратите внимание, что последовательность выбора критериев сортировки в этом диалоговом окне прямо противоположная той, которая была описана в предыдущем абзаце. В рассматриваемом примере следует начать со столбца Маклер. После этого щелкните на кнопке Добавить уровень и добавьте следующий уровень сортировки; в данном случае это будет Регион. Следующим и последним уровнем в примере будет столбец Цена объекта. На рисунке показано диалоговое окно после задания критериев сортировки по трем столбцам. Результатом применения этих критериев будет та же таблица, которая была показана на рисунке выше.
Фильтрация таблицы
Под фильтрацией таблицы понимается отображение в ней только тех строк, которые удовлетворяют заданному критерию (все остальные строки скрываются).
Программа Excel предлагает два способа фильтрации таблицы. В этом разделе будет описан стандартный метод, ранее называемый автофильтром. В большинстве случаев этот метод является вполне адекватным. Более сложные критерии требуют использования расширенной фильтрации, которая рассматривается далее.
Используя описанную выше таблицу агентства недвижимости, предположим, что нас интересуют только объекты, расположенные в Митино. В заголовке столбца Регион щелкните на стрелке и снимите флажок Выделить все. Программа снимет флажки во всех значениях данного столбца. После этого установите флажок около значения Митино и щелкните на кнопке ОК. Результат фильтрации показан на рисунке – в таблице теперь отображаются только объекты, расположенные в Митино. Обратите внимание, что некоторые номера строк пропущены – в этих строках находятся отфильтрованные (т.е. скрытые) данные. Также обратите внимание на значок кнопки в заголовке столбца – в нем теперь отображается пиктограмма фильтрации.
Таблицу можно фильтровать по множеству значений (к примеру, отображать объекты в регионах Митино и Строгино).
Фильтровать таблицу можно также по любому количеству столбцов. К примеру, может потребоваться просмотреть информацию только по двухкомнатным квартирам в Митино. В этом случае нужно повторить описанную выше операцию для столбца Кол-во комнат.
Если нужно установить дополнительные параметры фильтрации, выберите пункт Текстовые фильтры (или Числовые фильтры, если столбец содержит числовые данные). Пункты открывшегося меню говорят сами за себя, и вы имеете достаточную свободу в отображении только тех столбцов, которые вас интересуют.
В дополнение можете щелкнуть на ячейке правой кнопкой мыши и выбрать в контекстном меню команду Фильтр. Открывшееся подменю предложит вам дополнительные варианты фильтрации.
В строке итогов отображаются результаты вычислений по видимым строкам.
Некоторые стандартные операции работы с электронными таблицами в фильтрованных таблицах работают несколько по-другому. Предположим, была выбрана команда Главная→Ячейки→Формат→Скрыть или отобразить→Скрыть строки, чтобы скрыть строки. Если затем скопировать диапазон, содержащий скрытые строки, все данные будут скопированы – даже те, которые содержатся в скрытых строках. В отфильтрованной же таблице копируются только видимые строки. Таким образом, фильтрация упрощает копирование подмножеств данных больших таблиц и их вставку в другие места рабочего листа (или книги). Правда, следует заметить, что скопированные данные уже не будут представлять собой таблицу – это будет обычный диапазон.
Аналогично, можно выделить и удалить видимые строки таблицы, при этом удаление никак не повлияет на строки, скрытые в результате фильтрации.
Для снятия фильтрации по столбцу щелкните на стрелке в его заголовке и в открывшемся меню выберите команду Удалить фильтр. Если фильтрация установлена по множеству столбцов, ускорить процесс снятия фильтров поможет команда Главная→Редактирование→Сортировка и фильтр→Очистить.
Работа со строкой итогов
Строка итогов является дополнительным элементом таблицы, содержащим формулы, которые обобщают информацию в столбце. По умолчанию строка итогов не отображается. Если нужно, чтобы программа ее отобразила, установите в контекстной вкладке Работа с таблицами→Конструктор флажок Параметры стилей таблиц→Строка итогов. Этот флажок позволяет в любое время отображать и скрывать строку итогов.
По умолчанию в строке итогов отображается сумма значений числового столбца, однако в некоторых случаях необходим другой тип обобщения. Если выбрать ячейку в строке итогов, в ней будет отображена стрелка раскрывающегося списка. В этом списке можно выбрать любую другую обобщающую формулу.
• Нет. Нет формулы.
• Среднее. Вычисляется среднее значение чисел столбца.
• Количество. Вычисляется количество непустых ячеек в столбце.
• Количество чисел. Подсчет количества числовых значений в столбце (пустые, текстовые и содержащие ошибки ячейки не принимаются в расчет).
• Максимум. Отображается максимальное значение в столбце.
• Минимум. Отображается минимальное значение в столбце.
• Сумма. Вычисление суммы значений в столбце.
• Смещенное отклонение. Вычисляется смещенное отклонение значений в столбце. Смещенным отклонением называется стандартная статистическая единица измерения, характеризующая разброс значений.
• Смещенная дисперсия. Отображение дисперсии значений столбца. Дисперсией называют еще одну статистическую единицу измерения, характеризующую разброс значений.
• Другие функции. Открывается диалоговое окно, в котором можно выбрать функцию, отсутствующую в списке.
В списке обобщающих функций есть также функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ, в которой используется специальный структурированный синтаксис. Первый аргумент этой функции определяет тип обобщения. К примеру, если этот аргумент равен 109, функция будет вычислять сумму. Можно заместить формулу, вставленную программой, и ввести в итоговую ячейку любую другую формулу.
Предупреждение
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ является единственной функцией, игнорирующей данные, скрытые фильтрацией. Если используются другие формулы, обращающиеся к данным отфильтрованной таблицы, их невозможно скорректировать так, чтобы учитывались только видимые строки. К примеру, если используется функция СУММ для суммирования значений в столбце, а некоторые строки скрыты, ее результат будет отражать все данные столбца – даже невидимые.
Предупреждение
Если строка итогов скрыта, то формула, ссылающаяся на ячейку строки итогов таблицы, вернет ошибку. Как только строка итогов снова будет восстановлена на экране, эта формула будет продолжать работать, как полагается.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ достаточно универсальна, однако в то же время она является одной из самых противоречивых функций в арсенале Excel. Прежде всего, само ее имя вводит в заблуждение, поскольку она способна вычислять не только итоги. Первый ее аргумент имеет числовой тип и определяет операцию, выполняемую функцией. Эти числа практически невозможно запомнить, однако функция автозавершения Excel поможет вам выбрать нужное число.
В версии Excel 2003 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ была расширена дополнительными возможными значениями первого аргумента, решающими проблемы совместимости с более ранними версиями Excel.
Первый аргумент функции определяет реально используемую функцию. К примеру, если первым аргументом является единица, работа функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ аналогична функции СРЗНАЧ. В следующей таблице представлены возможные значения первого аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Если первый аргумент функции больше ста, она работает по-другому. В частности, в область расчетов не попадают данные скрытых вручную строк данных. Если же использовать первый аргумент, меньший 100, то в формуле ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитываются все вручную скрытые строки, а не учитываются только строки, отсеянные фильтрацией.
Дополнительную путаницу вносит то, что скрытые вручную строки не всегда трактуются одинаково. Если строка скрыта вручную в диапазоне, который тоже скрыт фильтром, Excel трактует их уже как отфильтрованные, а не скрытые вручную. В этом случае функция с первым аргументом, большим ста, будет вести себя точно так же, как и с аргументом, меньшим ста. При этом снятие фильтра приведет к отображению всех строк – даже тех, которые ранее были скрыты вручную.
Возможность использования первого аргумента, большего ста, была впервые введена в версии Excel 2003. Эту обновленную версию функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать в любом месте рабочей книги, а не только в таблицах. Однако учтите, что функция не имеет обратной совместимости. Если первый аргумент функции больше ста, а рабочая книга открыта в версии Excel, предшествовавшей Excel 2003, ее результатом будет значение ошибки.
Еще одной интересной особенностью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ является ее способность точно вычислять общий итог. При этом функция игнорирует ячейки, тоже содержащие формулы с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Использование формул в таблице
Добавление в таблицу строки итогов представляет собой простейший способ обобщения данных столбца таблицы. В то же время часто приходится использовать формулы внутри таблицы. Предположим, что в таблицу, показанную на рисунке, нужно добавить столбец, отображающий разницу между фактическими и запланированными доходами. Добавить столбец намного легче, если данные размещены в таблице, а не диапазоне.
Чтобы добавить столбец, выполните следующие действия.
1. Активизируйте ячейку Е2 и в качестве заголовка введите Разница. Excel автоматически расширит таблицу так, чтобы она включала новый столбец.
2. Перейдите к ячейке ЕЗ и введите знак равенства, указывая на начало формулы.
3. Нажмите стрелку влево, и Excel отобразит формулу =[@Факт]. Как видите, в формуле содержится ссылка на заголовок столбца.
4. Введите знак “минус” и дважды нажмите стрелку влево. Формула примет следующий вид: =[@Факт]-[@План].
5. Нажмите <Enter>, чтобы завершить ввод формулы.
Excel скопирует формулу во все строки таблицы.
На рисунке показана таблица с новым столбцом.
Все ячейки нового столбца содержат одну и ту же формулу:
=[@Факт]-[@План]
Примечание
Символ @ перед именем заголовка столбца означает конкретную строку, в которой находится формула.
Обратите внимание, что на рабочем листе мы не создавали никаких имен – формула использует ссылки на таблицу, основанные на названиях столбцов. Если изменить текст заголовка столбца, все формулы, ссылающиеся на него, будут автоматически обновлены.
Несмотря на то, что мы вводили формулу в первую строку данных таблицы, такой порядок не обязателен. В какую бы ячейку пустого столбца ни вводилась бы формула, она автоматически будет скопирована во все его ячейки. Если необходимо отредактировать формулу, скорректируйте содержимое любой ячейки столбца, и все изменения будут отражены в остальных его ячейках.
В приведенном выше примере при создании формулы была использована методика указания. В качестве альтернативы можно вводить формулу вручную, используя стандартные ссылки на ячейки. К примеру, в ячейку ЕЗ можно ввести следующую формулу:
=D3-СЗ
Если формула вводится с использованием ссылок на ячейки, Excel все равно ее копирует автоматически во все остальные ячейки столбца – просто в данном случае не используются имена столбцов.
Когда Excel вставляет в вычисляемый столбец формулу, отображается смарт-тег, содержащий ряд вариантов дальнейших действий. Если предпочитаете копировать формулу вручную, выберите вариант Не создавать вычисляемые столбцы автоматически.
Ссылки на данные таблицы
В предыдущем разделе было показано, как создавать в таблице столбцы формул. Что же нужно сделать, чтобы ссылаться на данные таблицы в ячейках, находящихся за ее пределами? В этом случае можно воспользоваться преимуществами структурированных ссылок, содержащих имя таблицы, заголовок столбца и прочие элементы таблицы. Нет никакой необходимости создавать для этих элементов имена.
Сама таблица имеет имя (к примеру, Таблица 1). На ее содержимое можно ссылаться, используя заголовки столбцов.
Естественно, при обращении к содержимому таблицы можно использовать и стандартные ссылки на ячейки, однако рассматриваемый метод имеет существенное преимущество: если размеры таблицы изменяются за счет вставки дополнительных строк или удаления существующих, имена автоматически корректируются.
Рассмотрим рисунок, на котором показана простая таблица Таблица8, содержащая информацию об объемах продаж в регионах. Для вычисления суммы всех значений таблицы можно использовать следующую формулу:
=СУММ(Таблица8)
Эта формула всегда возвращает сумму всех значений таблицы, даже если в ней будут удаляться и добавляться строки и столбцы. Если вы измените имя таблицы, Excel автоматически скорректирует все формулы, на нее ссылающиеся. К примеру, если имя Таблица1 изменить на Продажи, приведенная выше формула будет изменена на следующую:
=СУММ(Продажи)
Чтобы изменить имя таблицы, выделите в ней любую ячейку и отредактируйте поле Работа с таблицами→Конструктор→Свойства→Имя таблицы. Также можно воспользоваться диспетчером имен во вкладке Формулы.
Чаще всего формулы будут ссылаются на конкретные столбцы таблицы, а не на всю таблицу в целом. Следующая формула возвращает сумму всех данных столбца Объем:
=СУММ(Таблица8[Объем])
Обратите внимание, что имя столбца заключено в квадратные скобки. При изменении имени столбца будут автоматически скорректированы все формулы, на него ссылающиеся.
Предупреждение
Учтите, что результат предыдущей формулы не будет скорректирован, если путем фильтрации скрыть строки таблицы. Пропустить скрытые строки можно с помощью функций АГРЕГАТ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В данном случае можно использовать следующие формулы:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица8[Объем])
=АГРЕГАТ(9;1;Таблица8[Объем])
При создании формул, ссылающихся на данные таблицы, Excel обеспечивает пользователя некоторой поддержкой. На рисунке продемонстрирована функция автозавершения, помогающая создать формулу, отображая список элементов таблицы.
А вот еще один пример формулы, возвращающей сумму объемов продаж за январь:
=СУММ(Таблица8[Месяц];“Январь”;Таблица8[Объем])
Использовать структурированный синтаксис ссылок на таблицы не обязательно, можно использовать ссылки на фактические диапазоны. К примеру, следующая формула вернет тот же результат, что и предыдущая:
=СУММ(ВЗ:В8;“Январь”;D3:D8)
Для ссылок на ячейку строки итогов таблицы используется формула следующего вида:
=Таблица8[[#Итоги];[Объем]]
Если строка итогов в таблице не отображается, приведенная выше формула вернет значение ошибки #ССЫЛКА!.
Эта формула возвращает значение столбца Объем строки итогов.
Для подсчета общего количества строк в таблице Таблица8 можно использовать следующую формулу:
=СТРОКИ(Таблица8[#Все])
Данная формула подсчитывает все строки, включая строки итогов и заголовков. Для подсчета только строк данных используется формула следующего вида:
=СТРОКИ(Таблицав[#Данные])
Формула, находящаяся в той же строке, что и таблица, может использовать ссылку #Эта строка. Для примера предположим, что мы вводим формулу в ячейку строки 3, находящуюся за пределами таблицы. Следующая формула подсчитывает количество записей в строке 3 таблицы Таблица8:
=СЧЁТЗ(Таблица8[#Эта строка])
Также можно комбинировать ссылки на строки и столбцы, вкладывая квадратные скобки и включая множество ссылок, разделенных точками с запятой.
=Таблица8[[#Эта строка];[Объем]]/Таблица8[[#Итоги];[Объем]]
Формулу, подобную этой, гораздо легче создать, используя метод указания.
В таблице перечислены идентификаторы строк, а также описано, какой диапазон они представляют.
Для генерации последовательных номеров не скрытых ячеек отфильтрованной таблицы можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Нумерация будет изменяться по мере сокрытия и отображения строк в результате установки и снятия фильтра. Если таблица содержит имена столбцов в строке 1, введите следующую формулу в ячейку А2, а затем скопируйте ее во все последующие строки таблицы:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;В$2:В2)
Преобразование таблицы в диапазон
Если требуется преобразовать таблицу в обычную базу данных рабочего листа, выделите любую ячейку в таблице и выберите команду Работа с таблицами→Конструктор→Сервис→Преобразовать в диапазон. Стиль форматирования таблицы останется прежним, но сам диапазон перестанет функционировать как таблица.
Формулы внутри и вне таблицы, использовавшие структурированные ссылки на таблицы, будут преобразованы в соответствующие диапазоны адресов.
Заполнение промежутков
Импортируя данные, вы можете получить рабочий лист, подобный тому, который показан на рисунке ниже. В этом примере каждая ячейка столбца A связана с несколькими строками других столбцов. Сортировка такого списка может привести к беспорядочному отображению информации, и, в конечном счете, вы не сможете разобраться, кем из продавцов было сделано такое количество продаж.
Хорошо, если список невелик. В этом случае отсутствующие данные можно ввести в пустые ячейки вручную. Однако что делать, если размер базы данных огромен? Тогда используйте следующий более эффективный способ заполнения пустующих ячеек.
1. Выделите диапазон ячеек (в рассматриваемом примере это A3:А14).
2. Выберите команду Главная→Редактирование→Найти и выделить→Выделение группы ячеек.
3. В открывшемся диалоговом окне установите переключатель пустые ячейки.
4. Щелкните на ОК, чтобы закрыть диалоговое окно.
5. В строке формул введите знак равенства, за которым следует адрес первой ячейки столбца (в нашем примере введите =A3), после чего нажмите клавиши <Ctrl+Enter>, чтобы скопировать формулу во все выделенные ячейки.
6. Нажмите <Esc>, чтобы снять выделение.
7. Преобразуйте формулы в значения. Для этого вновь выделите диапазон, скопируйте его в буфер обмена и выберите команду Главная→Буфер обмена→Вставить→Вставить значения.
Все пустые ячейки будут заполнены данными, находящимися выше.