Умные Таблицы Excel – секреты эффективной работы - statanaliz.info

Умные Таблицы Excel – секреты эффективной работы

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

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

Как создать Таблицу в Excel

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

Обычный диапазон данных

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

Создать таблицу Excel с ленты

Есть горячая клавиша Ctrl+T.

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

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

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

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

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

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

Вкладка Конструктор для таблицы Excel

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

Таблица в диспетчере имен

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

Таблица в подсказке при наборе формулы

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

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

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

Выбор элемента таблицы в формуле

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

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

=СУММ(D2:D8)

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

=Отчет[Продажи]

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

Ссылка на столбец таблицы

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

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

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

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

Заголовки таблицы Excel

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

Заголовки таблицы всегда на экране

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

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

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

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

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

Добавление нового столбца

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

Введение формулы в столбец Таблицы Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Стили Таблицы

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

Инструменты Таблицы Excel

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

Кнопка создания среза в Таблице Excel

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

Выбор столбцов для среза

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

Срез Таблицы Excel

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

Фильтрация Таблицы с помощью среза

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

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

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

Параметры среза

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

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

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

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

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

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

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

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

Множество других секретов Excel вы найдете в онлайн курсе.

Поделиться в социальных сетях:
  • Аноним

    Есть еще один недостаток, нельзя транспонировать таблицу, что бы «заголовки» стали вертикально

    • Езепов Дмитрий

      Таблица — она по определению идет вниз по строкам. Поэтому транспонировать можно разве что как диапазон.
      Спасибо за замечание.

  • Аноним

    Добрый день. Мне очень нужна пом ощь. Есть очень сложная таблица, мне необходимо сделать из нее простую. Таблица представляет собой прайс, в первом столбце идет перечень наименований товара через запятую, имеются несколько информационных строк и пересечение этих строк и столбцов есть стоимость товара. Как сделать прайс нормальный по каждой позииции? всю голову сломала, все видео посмотрела, но никак не получается. В первом файле — та таблица, которую надо преобразовать. во втором файле — пример, как должна выглядеть таблица. Очень прошу — помогите с формулой.

    • Аноним

      Добрый день, Ольга. Я бы предложила Вам сделать следующее: ① Отменить обьединение ячеек;② Вручную заполнить цены в разьединенных ячейках;③ Создать сводную таблицу;④ Если количество столбцов и строк в будущем не изменится, создать макрос

  • Аноним

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

  • Дмитрий

    К минусам умных таблиц следует отнести — огромный размер файла, так 12 таблиц умных 200х2 увеличивают размер файла с 322 Кб до 35 Мб

  • Аноним

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

  • Аноним

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

  • Аноним

    Как транспонировать умную таблицу, сохраняя при этом все формулы?

  • https://elitepipeiraq.com/ uPVC مورد تجهيزات الأنابيب

    I do believe all the ideas you’ve presented for your post. They are really convincing and will certainly work. Nonetheless, the posts are too short for novices. May just you please lengthen them a little from subsequent time? Thanks for the post.

  • https://bwerpipes.com/ bwer Ductile Hdpe Pipe Iraq

    Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! However, how can we communicate?

  • https://bwerpipes.com/ bwer Upvc Agri Iraq

    Somebody essentially lend a hand to make significantly articles I’d state. That is the very first time I frequented your website page and up to now? I surprised with the research you made to make this actual submit amazing. Wonderful task!

  • https://bwerpipes.com/ bwer Weld Iraq

    Fantastic site. Lots of helpful information here. I am sending it to some friends ans additionally sharing in delicious. And of course, thanks for your effort!

  • https://bwerpipes.com/ bwer Hdpe Upvc Iraq

    I loved as much as you will receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get got an edginess over that you wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly very often inside case you shield this hike.

  • https://bwerpipes.com/ bwer pipe iraq

    hello!,I like your writing very so much! proportion we keep up a correspondence extra approximately your post on AOL? I need an expert in this space to unravel my problem. May be that is you! Taking a look forward to see you.

  • https://bwerpipes.com/ bwer pipe solutions iraq

    hello!,I like your writing very so much! proportion we keep up a correspondence extra approximately your post on AOL? I need an expert in this space to unravel my problem. May be that is you! Taking a look forward to see you.

  • https://bwerpipes.com/ bwer Hdpe Fittings Iraq

    Wow, amazing blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your web site is magnificent, as well as the content!

  • https://bwerpipes.com/ bwer Ductile Irrigation Iraq

    I just could not depart your web site prior to suggesting that I really loved the usual info an individual supply in your visitors? Is gonna be back regularly to check up on new posts.

  • https://bwerpipes.com/ bwer agri innovation iraq

    I was suggested this web site by my cousin. I’m not sure whether this post is written by him as no one else know such detailed about my trouble. You are incredible! Thanks!

  • https://bwerpipes.com/ bwer Factory Pipe Iraq

    you are in reality a just right webmaster. The site loading velocity is incredible. It seems that you are doing any unique trick. In addition, The contents are masterwork. you have performed a wonderful task on this topic!

  • https://bwerpipes.com/ bwer hdpe agriculture iraq

    My brother suggested I might like this website. He was totally right. This post actually made my day. You cann’t imagine just how much time I had spent for this information! Thanks!

  • https://bwerpipes.com/ bwer Factory Upvc Iraq

    I loved as much as you will receive carried out right here. The sketch is attractive, your authored material stylish. nonetheless, you command get got an impatience over that you wish be delivering the following. unwell unquestionably come more formerly again since exactly the same nearly a lot often inside case you shield this hike.

  • https://accounts.binance.com/bg/register-person?ref=OMM3XK51 Създаване на личен профил

    I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article. https://accounts.binance.com/bg/register-person?ref=OMM3XK51

  • https://www.binance.info/zh-CN/join?ref=UM6SMJM3 免费Binance账户

    Your point of view caught my eye and was very interesting. Thanks. I have a question for you.

  • https://www.binance.info/fr/join?ref=UM6SMJM3 Meilleur code de parrainage Binance

    Your article helped me a lot, is there any more related content? Thanks!

Комментарии для сайта Cackle
Пролистать наверх