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





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс 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. Логика копирования и переноса формул. Зачем ячейке нужны знаки доллара $ и как их поставить.

Создание сводной таблицы и настройки отчёта
Сводные таблицы. Часть 1
Команда Сводная таблица позволяет получить компактный упорядоченный отчёт по выгрузке из базы данных. Создать и настроить отчёт сводной таблицы очень просто. Главное - начать.

Наиболее используемые сочетания клавиш Excel
Горячие клавиши
Горячие клавиши ускоряют доступ ко многим командам, необходимым в ежедневной работе. Если учить хотя бы одно сочетание в 1-2 недели, результат будет заметен и ощутим!

Заказчики

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

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

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

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



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