Динамические таблицы в excel

Динамическая таблица в Excel

Если вам необходимо постоянно обновлять и добавлять данные или если вы работаете с диаграммами и сводными таблицами, вам понадобятся динамические именованные диапазоны, которые увеличиваются и уменьшаются в зависимости от ваших данных. Чтобы понять, как работают динамические именованные диапазоны, сначала нужно познакомиться с функцией Excel СМЕЩ (OFFSET), если вы еще не знакомы с ней. Функция СМЕЩ (OFFSET) относится к ссылочным функциям и функциям поиска Excel.

Начнем с простейшего динамического именованного диапазона, который будет расширяться вниз по одному столбцу, но только до тех пор, пока в этом столбце есть записи. Например, если в столбце А есть 10 последовательных строк с данными, динамический именованный диапазон будет охватывать диапазон А1:А10. Чтобы создать базовый динамический именованный диапазон, сделайте следующее.

Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите MyRange. В поле Формула (Refers to) введите следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$l:$A$100);l), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А$1:$А$100);1). Теперь щелкните на кнопке Добавить (Add), затем щелкните на кнопке ОК.

К сожалению, динамические именованные диапазоны нельзя проверить в стандартном поле имени слева от строки формул. Несмотря на это, можно щелкнуть в поле имени, ввести имя MyRange и нажать клавишу Enter. Excel автоматически выделит диапазон. Конечно, можно воспользоваться и диалоговым окном Переход (Go То), выбрав команду Правка → Перейти (Edit → Go To) (сочетание клавиш Ctrl/Apple+G). В поле Ссылка (Reference) введите MyRange и щелкните на кнопке ОК.

В динамическом именованном диапазоне, который вы создали в предыдущем примере, функция СЧЁТЗ (COUNTA) стоит на месте аргумента Высота (Height) функции СМЕЩ (OFFSET).

В следующем примере динамический именованный диапазон мы применим для определения таблицы данных, которая должна быть динамической. Для этогоn в поле Формула (Refers to) введите следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А$1:$А$100);СЧЁТЗ($1:$1)). Теперь динамический именованный диапазон будет расширяться на столько записей, сколько есть в столбце А, и на столько строк, сколько заголовков в строке 1. Если вы уверены, что количество столбцов в таблице данных меняться не будет, можете заменить вторую функцию СЧЁТЗ (COUNTA) постоянным числом, например, 10.

Единственная проблема при использовании динамического именованного диапазона для таблицы данных заключается в том, что предполагается, что столбец А определяет максимальную длину таблицы. Чаще всего это так, однако иногда самым длинным столбцом может быть другой столбец таблицы. Преодолеть эту проблему можно при помощи функции Excel МАКС (МАХ), которая возвращает самое большое число в диапазоне ячеек. В качестве примера создайте таблицу, как на рис. 3.4.

В строке 1 хранятся функции СЧЁТЗ (COUNTA), которые ссылаются вниз на соответствующий столбец и, таким образом, возвращают количество записей в каждом столбце. Функция МАКС (МАХ) будет использоваться в качестве аргумента Высота (Height) функции СМЕЩ (OFFSET). Это гарантирует, что динамический именованный диапазон для этой таблицы всегда будет расширяться вниз на столько ячеек, сколько их содержится в самом длинном столбце в таблице. Конечно же, можно скрыть строку 1, так как пользователю совершенно не нужно ее видеть.

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

В следующем примере список чисел в столбце А также содержит пустые ячейки. Это означает, что, если вы попытаетесь воспользоваться функцией СЧЁТ (COUNT) или СЧЁТЗ (COUNTA), динамический именованный диапазон закончится раньше, чем последняя ячейка с данными. Взгляните, например, на рис. 3.5.

В этом случае, хотя последнее число диапазона в действительности находится в строке 10, динамический диапазон расширяется вниз только до шестой строки. Причина этого лежит в функции СЧЁТ (COUNT), которая считает значения в ячейках от А1 до А100. Так как в списке только шесть числовых значений, диапазон содержит только шесть строк.

Чтобы преодолеть эту проблему, воспользуйтесь функцией Excel ПОИСКПОЗ (MATCH). Функция ПОИСКПОЗ (MATCH) возвращает относительную позицию элемента массива, соответствующего указанному значению в указанном порядке. Например, если вы примените следующую функцию ПОИСКПОЗ (MATCH): =МАТСН(6;$А$1:$А$100;0), в русской версии Excel =ПОИСКПОЗ(6;$А$1:$А$100;0), к тому же набору чисел, что и на рис. 3.5, она вернет число 10, представляющее строку 10 столбца А. Она возвращает 10, так как вы приказали функции найти число 6 в диапазоне А1:А100.

Очевидно, когда вы используете функцию ПОИСКПОЗ (MATCH) в динамическом именованном диапазоне, последнее число диапазона, вероятно, заранее неизвестно. Таким образом, понадобится задать в функции поиск слишком большого числа, которое никогда не появится в диапазоне, и изменить ее последний аргумент с 0 на 1.

В предыдущем примере вы приказали функции ПОИСКПОЗ (MATCH) найти в точности число 6, не больше и не меньше. Заменив 0 на 1, вы заставите функцию искать самое большое значение, меньшее или равное указанному. Для этого воспользуйтесь формулой =МАТСН(1Е+306;$А$1:$А$100;1), в русской версии Excel =ПОИСКПОЗ(1Е+306;$А$1:$А$100;1).

Чтобы создать динамический именованный диапазон, который будет расширяться до последней строки, содержащей число (независимо от наличия пустых ячеек до нее), введите следующую формулу в поле Формула (Refers to) диалогового окна Присвоение имени (Define Name) (рис. 3.6): =OFFSET(Sheet2!$A$1;0;0;MATCH(lE+306;Sheet2!$A$l:$A$100;1);1), в русской версии Excel =CMEЩ(Sheet2!$A$1;0;0;ПОИСКПОЗ(1E+306;Sheet2!$A$l:$A$100;1);1).

Следующий логический тип динамических именованных диапазонов, вытекающий из данного, — это диапазон, расширяющийся до последней текстовой записи независимо от пустых ячеек в списке или таблице. Для этого измените функцию ПОИСКПОЗ (MATCH) таким образом: МАТСН(«*»;$А$1:$А$100;-1), в русской версии Excel ПОИСКПОЗ(«*»;$А$1;$А$100;-1). Такая функция всегда возвращает номер строки, где содержится последняя текстовая запись в диапазоне $А$1:$А$100.

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

Для этого сначала вставьте две пустые строки выше списка. Выделите строки 1 и 2 и выберите команду Вставка → Строки (Insert → Row). В первой строке (строка 1) введите следующую функцию: =МАХ(МАТСН»*»;$А$3:$А$100;-1);МАТСН(1Е+306;$А$3:$А$100;1)), в русской версии Excel =МАКС(ПОИСКПОЗ»*»;$А$3:$А$100;-1);ПОИСКПОЗ(1Е+306;$А$3:$А$100;1)). В ячейке под ячейкой с формулой введите число 1. Ячейка еще ниже, под ячейкой с числом 1, должна содержать текстовый заголовок списка. Число 1 было добавлено, чтобы вторая функция ПОИСКПОЗ (MATCH) не вернула ошибку #N/A, если в диапазоне АЗ:А100 не окажется чисел. Первая функция ПОИСКПОЗ (MATCH) всегда найдет текст — в заголовке.

Дайте ячейке А1 имя MaxRow. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define), присвойте динамическому диапазону имя, например, MyList и в поле Формула (Refers to:) введите следующую формулу: =OFFSET(Sheet2!$A$3;0;0;MaxRow;1), в русской версии Excel =CMEЩ(Sheet2!$A$3;0;0;MaxRow;1).

В следующем списке перечислены типы динамических именованных диапазонов, которые могут оказаться полезными. Для веет этих примеров понадобится заполнить столбец А и текстом, и числовыми значениями. Кроме того, выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите имя, состоящее из одного слова (например, MyRange). Все, что будет изменяться, — это формула в поле Формула (Refers to).

Расширить диапазон на столько строк, сколько существует числовых записей: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;COUNT($A:$A);1), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТ($А;$А);1).

Расширить диапазон на столько строк, сколько существует числовых и текстовых записей: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;COUNTA($A:$A);1), в русской версии Excel =СМЕЩ($А$1;0;0;СЧЁТЗ($А:$А);1).

Расширить до последней числовой записи: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)), в русской версии Excel =СМЕЩ($А$1;0;0;ПОИСКПОЗ(1Е+306;$А:$А)). Если вы ожидаете, что может встретиться число, большее 1Е+306 (1 с 306 нулями), введите еще большее число.

Расширить до последней текстовой записи: в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;МАТCH(«*»;$А:$А;-1)), в русской версии Excel =СМЕЩ($А$1;0;0;ПОИСКПОЗ(«*»;$А:$А;-1)).

Расширить вниз в зависимости от значения в другой ячейке:В ячейку В1 введите число 10, а затем в поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;$B$1;1), в русской версии Excel =СМЕЩ($А$1;0;0;$В$1;1). Теперь измените число в ячейке В1, и диапазон изменится соответствующим образом.

Расширять вниз по одной строке каждый месяц: В поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0,MONTH(TODAY());1), в русской версии Excel =СМЕЩ($А$1;0;0.МЕСЯЦ(СЕГОДНЯ));1).

Расширять вниз по одной строке каждую неделю: В поле Формула (Refers to) введите следующее: =OFFSET($A$1;0;0;WEEKNUM(TODAY());1), В русской версии Excel =СМЕЩ($А$1;0;0;ДЕНЬНЕД(СЕГОДНЯ());1)

Для последней формулы необходимо установить Пакет анализа (Analysis ToolPak). Это можно сделать командой Сервис → Надстройки (Tools → Add-ins).


Источник: officeassist.ru

Как работать в Excel с таблицами для чайников: пошаговая инструкция

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

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

Как создать таблицу в Excel для чайников

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

Читайте также:  Замена эксель

Посмотрите внимательно на рабочий лист табличного процессора:

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

Сначала давайте научимся работать с ячейками, строками и столбцами.

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

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» – «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» – «Ширина столбца» – вводим заданный программой показатель (как правило это 8,43 – количество символов шрифта Calibri с размером в 11 пунктов). ОК.

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

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

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+”=”).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+”=”.

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

  1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
  2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
  3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
  4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

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

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

Как создать таблицу в Excel: пошаговая инструкция

Простейший способ создания таблиц уже известен. Но в Excel есть более удобный вариант (в плане последующего форматирования, работы с данными).

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» – инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» – «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+”=”).

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

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

Источник: exceltable.com

Умные Таблицы 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. При внесении формулы в одну ячейку, она сразу копируется на весь столбец. Не нужно вручную протягивать.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Источник: statanaliz.info

«Умная таблица» в Excel

Создать «Умную таблицу», которая впервые была представлена в Excel 2007, очень просто. Для ввода данных в нее можно использовать автоформу, которая защитит формулы от случайной перезаписи. В Excel 2010 для «Умной таблицы» был добавлен инструмент «Срезы» для упрощения фильтрации данных.

Общие правила создания и заполнения таблиц

Чтобы ваша таблица, «обычная» или «умная», правильно работала, следует придерживаться при ее создании и заполнении следующих несложных правил:

  • не использовать в таблице объединенные ячейки;
  • присваивать графам (колонкам) уникальные имена;
  • в одной графе использовать один формат данных;
  • не оставлять при заполнении таблицы пустых строк;
  • в «обычной» таблице следует выделить строку заголовков (шапку таблицы) форматированием (цвет, шрифт) и закрепить на начальном экране.

Создание «Умной таблицы»

Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:

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

В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:

Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:

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

Когда «Умная таблица» уже создана, подобрать для нее подходящее цветовое оформление становится легче. Для этого нужно выбрать любую ячейку внутри таблицы и снова нажать кнопку «Форматировать как таблицу» на вкладке «Главная». При наведении курсора на каждый образец форматирования, «Умная таблица» будет менять цветовое оформление в режиме предпросмотра. Остается только выбрать и кликнуть на подходящем варианте.

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

На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):

  • редактирование имени таблицы;
  • изменение цветового чередования строк на цветовое чередование столбцов;
  • добавление строки итогов;
  • удаление кнопок автофильтра;
  • изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
  • удаление дубликатов;
  • добавление срезов*, начиная с Excel 2010;
  • создание сводной таблицы;
  • удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».

*Срезы представляют из себя удобные фильтры по графам в отдельных окошках, работающие аналогично кнопкам автофильтра в строке заголовков. Создается срез (или срезы) нажатием кнопки «Вставить срез» и выбором нужной колонки (или колонок). Чтобы удалить срез, его нужно выбрать и нажать на клавиатуре «Delet» или пункт «Удалить (имя среза)» в контекстном меню.

Работа с «Умной таблицей»

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

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

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

В этом меню необходимо выбрать пункт «Другие команды…» для перехода к окну настройки панели быстрого доступа:

Здесь необходимо выбрать в верхнем раскрывающемся списке «Команды не на ленте», в окне со списком команд выделить пункт «Форма…», нажать кнопку «Добавить >>», а затем кнопку «OK». На панели быстрого доступа появится кнопка вызова автоформы.

Автоформа будет вызываться кнопкой на панели быстрого доступа для той «Умной таблицы», внутри которой находится активная ячейка.

Обратите внимание, что ячейка с формулой не редактируется. Добавление новой записи или сохранение внесенных изменений в существующую строку происходит при нажатии кнопок «Добавить», «Закрыть» или «Enter» на клавиатуре. При нажатии кнопок «Назад» или «Далее» внесенные изменения не сохраняются.

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

Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1 , но она не копируется автоматически в новую запись. А в ячейках с этой формулой программа Excel выводит замечание: «Несовместимая формула в вычисляемом столбце».

Для нашего примера подойдет следующая формула: =СТРОКА([@Наименование])-1 , которая вычисляет номер текущей строки рабочего листа Excel и уменьшает ее на единицу, так как первая запись в нашей «Умной таблице» начинается со второй строки рабочего листа Excel.

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

Преимущества «Умной таблицы»

К преимуществам «Умной таблицы» можно отнести следующие:

  • автоматическое форматирование по выбранному шаблону;
  • автоматическое расширение «Умной таблицы» при добавлении новых записей или колонок;
  • автоматическое копирование формул в новую запись;
  • возможность добавления строки итогов (критерий расчета итога для каждой отдельной графы можно выбрать из раскрывающегося списка);
  • возможность использования автоформы* для добавления новых записей и редактирования существующих (такая же возможность существует и для обычных таблиц).

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

Источник: vremya-ne-zhdet.ru

Финансы в Excel

Динамический источник данных сводной таблицы

Содержание
Фиксированный диапазон
“Умная таблица”
Динамический именованный диапазон
Вложения:

pivotsources.xlsx [Источники данных сводных таблиц] 30 kB

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

Читайте также:  Как в excel убрать клетки

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

  1. Фиксированный диапазон
  2. “Умная таблица” Excel 2007
  3. Динамический именованный диапазон

Фиксированный диапазон

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

В зависимости от практической задачи может потребоваться добавить новые данные в источник сводного отчета. Обычно добавляются новые строки. Это может происходить как при ручном вводе или копировании, так и при автоматизированном получении данных из внешних систем. После простого добавления данных вниз исходного диапазона Excel не включит эту новую строку в источник сводной таблицы. Обновление отчета не приведет к изменениям. Действительно, сводная таблица основана на заранее определенном фиксированном диапазоне данных, не включающих новые строки. Пример такой ситуации показан в файле-примере на листах Data и ReportData – строки 11 и 12 в отчет не попали:

Одним из вариантов решения проблемы является добавление новых строк в середину диапазона – при этом Excel корректно исправит ссылку на источник данных сводной таблицы.

На практике же обычно удобнее и проще заранее определить максимально возможный диапазон строк источника сводной таблицы, добавив про запас достаточное количество строк. В зависимости от задачи можно добавить как 10, так и 10000 строк, как ни странно, это почти не сказывается на производительности сводной таблицы. В файле-примере поменяйте источник данных сводной таблицы ReportData следующим образом:

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

Создавать запас пустых строк в источнике данных можно также для старых сводных таблиц xls-формата. Здесь, правда, имеется небольшая недоработка в интерфейсе: если столбец хранит даты, а в исходных данных присутствуют пустые значения, сводный отчет не позволит применить к этому полю стандартную группировку по годам, кварталам, месяцам и т.д. Новые сводные таблицы xlsx-формата лишены этого недостатка.

“Умная таблица”

Начиная с версии Excel 2007, в интерфейсе рабочего листа стала доступна работа с так называемой «умной таблицей» (лента Вставка Таблица). Такая таблица имеет расширенные возможности по дизайну, собственную нотацию формул и может быть опубликована на сервере SharePoint для совместного доступа нескольким пользователям. В нашем случае важно, что «умная таблица» Excel может быть указана в качестве источника данных сводного отчета – можно указать ее по имени или выделить мышью (листы Table, ReportTable):

Добавление строк в «умную таблицу» не влияет на источник данных сводного отчета. Ссылка определяется по имени, поэтому анализ всегда проводится на полном объеме информации. Т.е. это вариант решения можно признать удобным в случае, когда исходные данные могут быть преобразованы в «умную таблицу». При ручном заполнении источника данных сводной таблицы этот вариант работы можно даже назвать приоритетным. Недостатком является несовместимость с xls-форматом, а также сложность при автоматизации импорта из внешних систем.

Динамический именованный диапазон

«Продвинутые» пользователи Excel умеют использовать именованные диапазоны рабочего листа. При помощи таких имен можно заменять ссылки в формулах или указывать источники данных. Обычные именованные диапазоны – это просто замена длинной ссылки в координатах рабочего листа на короткое имя. Так в примере на листе Data можно выделить область A1:D12, присвоить ей имя, а затем указать его в качестве источника данных сводной таблицы ReportData.

Результат будет совершенно такой же, как и при использовании ссылки на фиксированный диапазон. Т.е. проблемы с добавлением новых строк простой именованный диапазон не решает.

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

Пример такого использования имени с формулой на листах Name и ReportName.

Формула имени source выглядит следующим образом:

Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:

Добавив несколько заполненных строк вниз таблицы исходных данных, можно убедиться, что сводная таблица успешно их обрабатывает (по нажатию кнопки «Обновить»).

Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.

Источник: www.excelfin.ru

Умные таблицы в Excel

Видео

Постановка задачи

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в “умную” Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки – она растянется ниже, если добавить новые столбцы – разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

  • В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data) ).
  • При добавлении новых строк в них автоматически копируются все формулы.
  • При создании нового столбца с формулой – она будет автоматически скопирована на весь столбец – не надо тянуть формулу черным крестом автозаполнения.
  • При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

    Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

  • К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
    • =Таблица1[#Все] – ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
    • =Таблица1[#Данные] – ссылка только на данные (без строки заголовка)
    • =Таблица1[#Заголовки] – ссылка только на первую строку таблицы с заголовками столбцов
    • =Таблица1[#Итоги] – ссылка на строку итогов (если она включена)
    • =Таблица1[#Эта строка] – ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] – будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

    В Excel 2003 было что-то отдаленно похожее на такие “умные” таблицы – называлось Списком и создавалось через меню Данные – Список – Создать список (Data – List – Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

    Источник: www.planetaexcel.ru