А какие секреты в Excel? Секреты Microsoft Exсel Все секреты excel функций

Группировка данных

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

Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам (см. рис. 2).

Рисунок 2 – Выбор типа группировки

В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.

Рисунок 3 – Группировка строк «вниз»

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

Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .

Рисунок 5 – Окно настроек структуры

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

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

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

Рисунок 6 – Многоуровневая структура каталога в Excel

Теперь вы сможете раскрывать и закрывать части каталога, щёлкая по плюсам и минусам в левой колонке (см. рис. 6). Чтобы развернуть весь уровень, нажмите на одну из цифр в верхней части.

Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

Достаточно часто при работе с таблицами Excel возникает необходимость закрепить некоторые области листа. Там могут располагаться, например, заголовки строк / столбцов, логотип компании или другая информация.

Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.

Рисунок 8 – Закрепляем строку или столбец

Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей (пункт заменяет собой строку Закрепить области , если на странице применена «заморозка»).

А вот закрепление нескольких строк или области из строк и столбцов осуществляется не так прозрачно. Вы выделяете три строки, щёлкаете по пункту Закрепить области , и… Excel «замораживает» только две. Почему так? Возможен ещё более плохой вариант, когда области закрепляются непредсказуемым образом (например, вы выделяете две строки, а программа ставит границы после пятнадцатой). Но не будем списывать это на недосмотр разработчиков, потому что единственный правильный вариант использования данной функции выглядит по-другому.

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

Рисунок 9 – Закрепляем область из строк и столбцов

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

Поворот листа (замена строк на столбцы и наоборот)

Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

Вот и всё, поворот таблицы выполнен (см. рис. 13). При этом форматирование сохраняется, а формулы изменяются в соответствии с новым положением ячеек – никакой рутинной работы не потребуется.

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки .

Перенос строк в ячейках

Достаточно часто в книгах Excel встречаются длинные надписи, не помещающиеся в ячейку по ширине (см. рис. 17). Можно, конечно, раздвинуть столбец, но не всегда этот вариант приемлем.

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

В зависимости от своих предпочтений вы можете пойти двумя путями:

Сначала создать надпись, а потом её повернуть.
Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

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

Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.

Рисунок 25 – Выбираем заготовку условного форматирования

Гистограммы выглядят достаточно интересно и хорошо отражают суть информации о цене – чем она выше, тем длиннее отрезок.

Цветовые шкалы и наборы значков можно использовать для индикации различных состояний, например, переходы от критических затрат к допустимым (см. рис. 26).

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

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

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек (см. рис. 28).

Рисунок 28 – Удаляем правила условного форматирования

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

Использование фильтров

Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.

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

После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).

Рисунок 29 – Создаём фильтры

Теперь ячейки преобразятся в раскрывающиеся списки, где вы можете задать параметры выборки. Например, мы ищем все упоминания про Intel в столбце Наименование товара . Для этого выбираем текстовый фильтр Содержит (см. рис. 30).

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

Впрочем, гораздо быстрее добиться того же эффекта, вписав слово в поле Поиск контекстного меню, показанного на рисунке 30. Зачем тогда вызывать дополнительное окно? Оно пригодится в том случае, если вы хотите указать несколько условий выборки или выбрать другие параметры фильтрации (не содержит , начинается с…, заканчивается на… ).

Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).

Рисунок 32 – Числовые фильтры

Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).

Отображение информационных кривых

Информационные кривые (инфокривые) – нововведение в Excel 2010. Эта функция позволяет отобразить динамику изменения числовых параметров прямо в ячейке, не прибегая к построению диаграммы. Изменения в числах будут сразу же показаны на микрографике.

Рисунок 33 – Инфокривая Excel 2010

Чтобы создать инфокривую, нажмите на одну из кнопок в блоке Инфокривые на вкладке Вставка (см. рис. 34), а затем задайте диапазон ячеек для построения.

Рисунок 34 – Вставка инфокривой

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

Заключение

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

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

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».

Создание Таблицы Excel

В наличии имеется обычный диапазон данных о продажах.

Чтобы преобразовать этот диапазон в Таблицу, следует выделить любую ячейку и перейти Вставка → Таблицы → Таблица

либо нажать горячую клавишу Ctrl+T .

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

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

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор , которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен .

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab . Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

СУММ(D2:D8)

то она автоматически переделается в

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

1. Каждая Таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов .

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

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры . В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Ниже можно посмотреть видеоурок о Таблицах Excel.

секреты EXCEL (часть первая)

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

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

: Секрет первый "Волшебная клавиша F4"
===============================

Вспомните, часто ли вы выполняете однотипные действия. Я думаю что, практически постоянно. Это вставка строк, добавление новых листов, форматирование и т.д. Список можно продолжать бесконечно.

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

Но этого можно избежать. Ведь достаточно лишь один раз совершить действие (например, вставить строку) и нажать клавишу F4 ровно столько раз, сколько нужно повторов проделанного действия.

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

Но, на этом возможности клавиши F4 не заканчиваются. С ее помощью можно быстро ссылки в формулах изменить с относительных на абсолютные.

Для начала напомню вам, что формулы с абсолютными ссылками, в отличие от относительных не изменяются при автозаполнении и копировании. Это очень удобно если формулы одного столбца ссылаются не на соседние столбцы родной строки, а на одну какую-либо ячейку.

Предположим, имеется следующая формула =A2+C1 При перетягивании ее вниз на следующей строке формула видоизмениться на =А3+С2, далее =А4+С3 и т.д.

Хотя там необходимо =А3+С1, =А4+С1 и т.д.

Для того что бы ячейка С1 не изменялась перед ней надо поставить знак доллара. Но можно просто нажать на F4 сразу после ввода адреса ячейки.

При однократном нажатии на F4 формула, скажем для ячейки С1, примет вид $С$1, при двукратном - С$1, при трехкратном - $С1, а затем превращается опять в относительную ссылку (С1) и так по кругу.

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

: Секрет второй "Многоэтажные ячейки"
======================================

Часто текст не вмещается в ячейку. Как правило это можно поправить увеличением её длины. Но порой это сделать бывает недопустимо.

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

Это достигается лазаньем в формат ячеек и установкой функции «перенос по словам».

Но есть еще один способ. Он срабатывает при нажатии клавиш ALT+ENTER. Перед этим необходимо в ячейке установить курсор мыши между теми словами, которые и должны разделиться по строчкам.

: Секрет третий "Текст в формулах"
===

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

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

Это можно проделать путем создания пользовательского формата.

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

Таким образом, окно «тип» в нашем примере будет выглядеть так: Основной «км»

Теперь в ячейке помимо чисел будет еще и текст. И сохраниться возможность пользоваться формулами.

===========================================
: Секрет четвёртый "Выравниваем размер ячеек"
===========================================

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

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

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

Со строками дело обстоит точно также.

==============================================
: Секрет пятый "Фотография ячеек"
==============================================

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

Мы можем « сфотографировать» определенный участок листа, или диаграмму в формате рисунка.

Делается это так. Сначала выделяется диапазон и, удерживая клавишу Shift, заходим в раздел меню «Правка». С меню произошли изменения!!! И там появилась новая команда «Копировать рисунок». Её мы и нажимаем, а в появившемся окошке указываем «как на экране» и жмем «ОК».

Теперь фотографию ячеек можно вставлять в виде рисунка. И не только в excel.

Насчет диаграмм, почти всё также. Сначала активируем диаграмму и выбираем «копировать рисунок». Теперь диаграмму можно вставлять как рисунок без всяких секторов и составных частей.

В Excel - 2007 это происходит несколько иначе. Выделяете диапазон, затем заходите на вкладку "Главная" и нажимаете на надпись "вставить" (она расположена ниже пиктограммы "вставить"). В появившемся меню выбирайте "как рисунок - копировать как рисунок".

В последние годы появились новые "умные" технологии: умные телефоны, умные дома, холодильники, чехлы для телефонов, телевизоры и т. д. Не стали исключением и электронные таблицы. Умные таблицы в Excel были внедрены начиная с версии 2010 года.

Понятие об умных таблицах

Книга Excel сама представляет собой огромную таблицу. Однако эта таблица требует постоянной подстройки: протягивание формул, сортировки, оформления, если эту таблицу необходимо представить в каком-либо документе или на презентации.

Корпорация Microsoft, создавшая Excel, предложила называть умные таблицы таблицами, а то, что до этого называлось таблицами, - диапазоном. Однако в нашей стране эти идеи не прижились, поэтому таблицы остались таблицами, а новшество стали называть умными таблицами.

При внедрении умных таблиц улучшается восприятие документа электронной таблицы, обработка данных становится проще, и они приводятся к определенной системе.

Создание умной таблицы в Excel

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

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

Далее возникнет диалоговое окно, в котором будет предложено уточнить диапазон ячеек, входящих в данную таблицу, и если таблица содержит заголовки ("шапку"), то необходимо поставить галочку около надписи "Таблица с заголовками".

После чего необходимо подтвердить выполненные действия путем нажатия на кнопку "ОК" или нажав Enter.

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

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

Выпадающие списки

Одним из секретов повышения скорости ввода данных в электронную таблицу является использование выпадающих списков.

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

Выделяем ячейку, в которой будет находиться данный список, идем на вкладку "Данные", выбираем группу "Работа с данными" и нажимаем "Проверка данных".

В появившемся окне нажимаем на стрелочку рядом с "Типом данных" и выбираем "Список".

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

Подтверждаем сделанные изменения. В результате получим с вашими данными.

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

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

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

Представленный способ является одним из секретов эффективной работы с умными таблицами в Excel.

Изменяем стиль умной таблицы

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

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

После этого перемещаемся в таб "Конструктор", группа "Стили таблиц". Если не хватает представленных стилей, кликаем на "Дополнительные параметры" и видим всю палитру стилей.

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

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

Параметры стилей

Умные таблицы в Excel 2010 и старше, а также в Excel 2007 можно настраивать под себя в зависимости от предпочтений и необходимости.

Для этого переходим курсором на любую ячейку таблицы. Переходим на таб "Конструктор", группа "Параметры стилей таблиц", и там ставим/убираем галочки с интересующих нас опций: "Кнопка фильтра", "Чередующиеся столбцы или строки", "Строка заголовка или итогов", "Первый или последний столбец".

Использование формул

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

В случае необходимости создания нового столбца, в котором нужно ввести формулу, в ячейке данного столбца вводим формулу, которая несколько отличается от стандартной для данного типа электронной таблицы. Она имеет вид [@[{Название столбца}]]арифметические действия.

Здесь {Название столбца} - конкретное название столбца, в котором осуществляется расчет, например "Сумма, руб.", арифметические действия - это алгебраические выражения, принятые в формулах Excel и, в случае необходимости, числа, @ - показывает, что данные будут взяты из той же строки.

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

При нажатии на Enter произойдет пересчет в данном столбце во всех ячейках таблицы.

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

Если вы заинтересовались данной информацией, хорошо владеете иностранными языками и будете искать дополнительные источники информации за рубежом, помните о том, что в России и за рубежом разные разделители элементов списка. У них это запятая, а у нас - точка с запятой, что необходимо учитывать в формулах.

Автофильтрация

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

Например, есть таблица с покупателями, а в другом столбце указаны месяцы осуществления покупок. Нас интересует только февраль. Чтобы вручную не осуществлять поиск покупателей февраля, нажимаем на стрелочку столбца "Месяц" и ставим там галочку около "февраль", снимая остальные галочки. Таким образом, будут показаны только покупатели, сделавшие покупки в феврале. Остальные при этом никуда не исчезнут, если опять нажать стрелку в заголовке "Месяц" и отметить галочками все месяцы, то снова на экране появятся все покупатели.

Задаем название таблицы

После того как умная таблица в Excel создана, приложение присваивает ей название в соответствии с порядком ее следования (если это первая таблица будет присвоено название "Таблица1" и т. д.).

Для того чтобы узнать название таблицы, ставим курсор в любую ее ячейку, переходим в таб "Конструктор", группа "Свойства" и там находим параметр "Имя таблицы", который можно редактировать, для чего нужно поместить на "Таблица1" курсор, выделить и ввести новое название, после чего нажать Enter.

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

Название таблицы может быть использовано при применении ГПР. Например, мы хотим найти, кто у нас был 1229-м покупателем. Для этого в какую-то свободную ячейку, например K1, вводим 1229, в ячейку L1 вводим формулу =ВПР(K1;Таблица1 (или ее измененное название);1). Здесь 1 - номер столбца, в котором будет осуществляться поиск. В результате в ячейке L1 появятся Ф. И. О. покупателя под №1229 (если, конечно, он был в первом столбце).

Адресация в умных таблицах

Умные таблицы в Excel позволяют вместо диапазона ячеек с данными применять имена полей. Так, чтобы подсчитать сумму по столбцу C с 1-й по 21-ю ячейку, можно ввести формулу, общепринятую в Excel = СУММ(C1:C21). Предположим, что в столбце С у нас занесена информация по сумме покупки, совершенной каждым конкретным покупателем, и называется этот столбец "Сумма, руб.", а таблица была переименована в "Покупатели". Таким образом, в качестве формулы может быть введено следующее выражение: = СУММ(Покупатели[Сумма, руб.]).

Помимо этого, можно использовать специальную адресацию:

«=Покупатели[#Эта строка]» будет ссылаться на текущую строку.
«=Покупатели[#Данные]» будет ссылаться на данные, не учитывая строки заголовков.
«=Покупатели[#Заголовки]» будет ссылаться на заголовки столбцов таблицы, расположенные в первой строке таблицы.
«=Покупатели[#Итоги]» будет ссылаться на итоговую строку (в случае ее наличия в умной таблице).
«=Покупатели[#Все]» будет ссылаться на всю таблицу, включая все вышеперечисленные формулы.

Дополнительные "фишки" умных таблиц

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

В Excel 2010 появилась возможность при работе с умными таблицами создавать срезы. Они представляют собой автофильтр отдельного столбца, представленный в виде элемента графики. Переходим в таб "Конструктор", группа команд "Инструменты", нажимаем "Вставить срез", в диалоговом окне выбираем названия столбцов (или одного столбца), из которых будет сформирован срез. При необходимости фильтрации таблицы выбираем необходимую категорию или категории, зажав кнопку Ctrl.

Как удалить умную таблицу Excel

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

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

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

В заключение

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