Kwert-soft.ru

IT Софт для ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Выбрать данные из таблицы в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать формулу в excel сумма

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А теперь о том, какую пользу запросы могут причинить вашей компании.

В моей практике был случай, когда в одной компании каждый месяц информационная система выгружала данные в Excel в таблицу размером примерно 200 столбцов на 25 000 строк.

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

Экономисты компании ничего не знали про запросы, и поэтому составляли отчёты при помощи автофильтров и полуручного копирования данных.

Только представьте себе – практически 20 дней в месяц трое экономистов терзали и истязали одну несчастную таблицу.

Составление запросов заняло у нас 2 дня, и после этого весь объём работ по составлению всех нужных отчётов стал проделывать 1 экономист за 3-4 дня.

А теперь давайте посчитаем экономию.

Что было: 3 экономиста х 20 дней х 8 часов = 480 человеко-часов.

Что стало: 1 экономист х 4 дня х 8 часов = 32 человеко-часа.

Сокращение трудозатрат в 15 раз. Пример, конечно, очень показательный, но далеко не единственный в моей практике.

Если вы хотите добиться у себя на работе таких же результатов – предлагаю вам пройти мой видеокурс « Бюджетирование и управленческий учёт в Excel ».

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

На нашем тренинге уже успели обучиться более 2 600 человек .

Мы уже провели более 140 тренингов 15 городах России – от Питера до Владивостока и везде получали самые высокие оценки нашей работы от участников.

После видеокурса вы:

  • Сводите данные из нескольких таблиц за 3 минуты в автоматическом режиме, даже если формат и содержимое таблиц не совпадает, а сами таблицы находятся в разных файлах.
  • Исключаете 93% ошибок, возникающих по вине персонала, за счёт файлов, в которые физически невозможно ввести неправильные данные.
  • Автоматически обрабатываете огромные массивы данных и за 5 минут строите сложные отчёты по таблицам из сотен тысяч строк, так как используете приёмы автоматической работы с большими объёмами данных.
  • Создаёте многопользовательские системы файлов, которые автоматически обмениваются данными и стабильно работают, даже если кто-то из пользователей внёс правки в исходные файлы.
  • Надёжно защищаете свои файлы от неправильных действий пользователей и начинаете автоматически отслеживать, кто, когда, какие изменения и в какой файл внёс.
  • Настраиваете самособирающиеся отчеты, которые при изменении исходных данных меняют не только своё содержание, но и форму. Поэтому их не нужно менять вручную.
  • Автоматически «раскидываете» нужные данные по разным файлам или мгновенно выбираете данные из огромных таблиц по одному или нескольким критериям внутри одного файла.
  • Моментально находите решения задач экономической оптимизации. Например, за 7 минут составляете планы продаж и производства, которые приносят максимальную прибыль в условиях жёсткого ограничения ресурсов.
  • Составляете понятные и удобочитаемые отчеты, которые собирают информацию одновременно из нескольких файлов, и которые можно быстро проверить и легко расшифровать.

Обычно после тренинга многие участники говорят: «Мы даже не думали, что в Excel можно делать такие удивительные вещи!». Впрочем, лучше сами посмотрите, что говорят участники:

Подробную информацию можно посмотреть на сайте видеокурса .

А если у вас остались вопросы или нужна дополнительная информация — звоните по телефону 8 800 100-93-44 (звонок из любого города России с любого телефона бесплатный).

А на сегодня всё. Жду вас в видеокурсе! 🙂

И до встречи в следующих выпусках рассылки! 🙂

ИП Гришин Илья Юрьевич, ОГРН 307667133100016.

Телефон: 8 800 201-49-35 с 9:00 до 16:00 по Московскому времени

Все права защищены. Гришин И.Ю. 2010-2017

Политика конфиденциальности

Настоящая политика конфиденциальности распространяются на всех посетителей и пользователей сайта. Пользователю сайта необходимо внимательно ознакомиться с настоящими Условиями.

Пользователь дает ИП Гришин Илья Юрьевич разрешение на обработку своих персональных данных, указываемых им в анкете, на сайтах компании.

ИП Гришин Илья Юрьевич использует всю добровольно предоставленную пользователем информацию для обработки и предоставления информационных образовательных услуг.

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

Ссылка на основную публикацию
Adblock
detector