Форматировать как таблицу - что за команда
Оформление больших таблиц. Умная строка итогов и автоматическая нумерация. Удаление пустых строк. Быстрый ввод формул





На вкладке Главная есть кнопка Форматировать как таблицу. Как это работает? Какие задачи помогает решить данная команда и как отказаться от полосатого стиля, если он неудобен.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

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

На вкладке Главная есть кнопка Форматировать как таблицу. Как это работает? Какие задачи помогает решить данная команда и как отказаться от полосатого стиля, если он неудобен. Создавая таблицу с помощью команды Форматировать как таблицу мы получаем особый объект, позволяющий сделать несколько фильтров на одном листе, сам отслеживающий изменение размеров (прибавка строк), актуальность суммарных значений в итоговой строке, приглядывает за столбцами с формулами и не только.

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

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Как оформить таблицу

На этот за будем работать с макетом реестра договоров – весьма компактная и аккуратная таблица в примере и нереально громоздкий документ на практике. Для начала нужно оформить этот реестр путем преобразования в «умную» таблицу. И поступим вот как – выделять весь набор ячеек этой таблицы необязательно (как и в последующих инструментах для обработки больших списков; исключения бывают, но о них будет сказано отдельно), достаточно выбрать одну любую заполненную ячейку (при условии, что в таблице нет полностью пустых столбцов и/или строк), выполнить команду Главная – Стили – Форматировать как таблицу [Home – Styles – Formatas_Table] и выбрать любой подходящий вариант оформления левой кнопкой мыши (позже его можно будет изменить, если результат не совсем устроит)

Далее появится небольшое диалоговое окно Форматирование таблицы [Formatas_Table], в котором можно скорректировать диапазон ячеек, если Excel определил его неверно либо в том случае, когда заголовок состоит более чем из одной строки или же Вы не хотите, чтобы строка с фильтром добавилась под строкой заголовка. Буду действовать по второму сценарию и поэтому выберу диапазон таблицы без заголовка, но при этом не забуду сбросить флажок Таблица с заголовками [My_table_has_headers] поступлю в примере именно так

Теперь можно жать ОК и результат будет следующим:

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

Во внешнем виде таблицы произошли в итоге следующие изменения: оформлены заголовки таблицы, установлен автофильтр, появились границы между строками либо к ним была применена чередующаяся заливка (это зависит от выбранного макета). В интерфейсе окна программы также произошли изменения – если выбрана любая ячейка получившейся умной таблицы, в правой части ленты появляется контекстная вкладка Конструктор [Design] группы Работа с таблицами [Table_Tools] – чуть позже обязательно с ней поработаем. А пока попробуйте добавить в таблицу новую строку в любом месте и первая же приятность случится – чередующаяся заливка строк будет сохранена, она не пострадает

 

Элементы дизайна и оформление таблицы

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

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

 

Актуальный итог при фильтрации таблицы

Excel очень любит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ [SUBTOTAL]. В данном конкретном случае вот за что – отфильтруйте таблицу по любому условию (я выберу по столбцу Заказчик элемент Заказчик 2) и оцените итог – он пересчитывается, ориентируясь исключительно на видимые строки таблицы и не берет в расчет скрытые строчки. Эту функцию можно использовать и за рамками умных таблиц – обязательно с ней еще поработаем, а пока вернемся к таблице

 

Как использовать автофильтр

Автофильтр можно применить к любой таблице – для этого обычно выделяют строку, на которой должны располагаться кнопки фильтра, и выполняют команду Данные – Сортировка и фильтр – Фильтр [DataSort & Filter - Filter]

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

Условия можно установить сразу на несколько столбцов (в меню под кнопкой фильтра нужно оставить флажки напротив тех позиций, которые должны быть видны, либо воспользоваться командами Числовой фильтр [Number_Filters] или Текстовый фильтр [Text_Filters], чтобы скрыть/отобразить сразу группу строк, отвечающую указанному условию). Быстро сбросить все установленные фильтры можно при помощи команды на ленте: Данные – Сортировка и фильтр – Очистить [DataSort & FilterClear]

 

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

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

После чего выделяете отфильтрованные строки и совершенно обычным образом удалите их – скрытые строки не пострадают – снимите фильтр и увидите результат:

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

 

Как сделать автоматическую нумерацию

В Excel пока нет встроенного инструмента, который приводил бы в порядок нумерацию строк после различных манипуляций с таблицами. Но выход есть. В любой таблице в ячейке, предназначенной для первого порядкового номера, напишите следующую формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$B$4:B4) – можно вручную, так как щелчок по ячейкам будет приводит к подстановке названий элементов таблицы вместо стандартных адресов. Мы уже разобрались, что используемая функция обрабатывает только видимые ячейки, и для подсчета их количества использовали номер_функции [function_num] равный 3. Теперь протяните эту формулу вниз до конца таблицы и проверьте результат – отфильтруйте таблицу любым произвольным образом

 

Ввод формул в столбцы «умной» таблицы

В настоящем примере предлагаю добавить столбец, в котором нужно высчитать цену как результат деления данных столбца Всего, руб. на ячейки столбца Количество. Чтобы в такой таблице добавить столбец (или строчку) не нужно выделять целый столбец листа и выполнять знакомую операцию вставки через контекстное меню либо при помощи клавиш Ctrl + «+». Достаточно выбрать ячейку соседнего столбца таблицы и вызвать контекстное меню – команда Вставить [Insert] сразу предложит добавить элементы в таблицу, остается только выбрать нужный вариант

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

 

Ввод новых записей

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

Далее заполняете ячейки нужным текстом. Однако, есть приятная мелочь, которая позволит облегчить ввод текстового значения, которые выше в списке уже встречалось. Для этого на ячейке, в которую нужно ввести данные, нажмите правой кнопкой и выберите команду Выбрать из раскрывающегося списка [Pickfrom_Drop-down_List…] (либо нажмите сочетание клавиш Alt + стрелка вниз):

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

Похожие материалы

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

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

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

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.