Программа 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+»=»).

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

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

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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel.
На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица».

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

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

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

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

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

Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии.
Обратите внимание, как расположились эти данные в нижней области панели настройки.
Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец.
При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость.

Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel.
Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов — просто наведите мышь, нажмите левую кнопку и тяните.
Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду.
В параметрах полей значений вы найдете множество вариантов для анализа.
Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.
Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.
Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры — просто перетяните, как было указано выше.
Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».
Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу.
Также можно выбрать и значения для отдела.
Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.

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

Обновление данных в сводной таблице в Excel

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».
Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.
Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

Как в сводную таблицу Excel добавить столбец или таблицу

Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра.
Перейдите на вкладку «Анализ» и откройте источник данных.
Excel сам все предложит.
Обновите и вы получите новый перечень полей в области настройки.
Добавить таблицу вы сможете только если «склеите» ее с исходной. Можно заменить диапазон в уже существующей, но добавить «на лету» другой диапазон нельзя. Зато можно создать новую сводную таблицу на основе нескольких исходных, даже расположенных на разных листах.

Как сделать сводную таблицу в Excel из нескольких листов

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».
Выберите все команды.
И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.
Значок появится сверху.
У вас должны быть две идентичные по полям таблицы на разных листах. У нас это данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов.
Нам нужно несколько полей, а не одно.
На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.
Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.
Щелкайте «Далее» и создавайте на новом листе.
После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях.
Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.
Снимая или устанавливая галочки, вы регулируете значения, которые вам необходимо увидеть. Неудобно и то, что расчет проводится для всех значений одинаковый.
Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

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

Вам откроются на выбор три типа для структуризации информации:

• Сжатая форма

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

• Структурированная форма

Все показатели подаются иерархично: от малого к большому.

• Табличная форма

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

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

В этом разделе самоучителя дана пошаговая инструкция, как создать продвинутую сводную таблицу в современных версиях Excel (2007 и более новых). Для тех, кто работает в более ранних версиях Excel, рекомендуем статью: Как создать продвинутую сводную таблицу в Excel 2003?

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

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

1. Выделите любую ячейку в диапазоне или весь диапазон данных, который нужно использовать для построения сводной таблицы.ЗАМЕЧАНИЕ: Если выделить одну ячейку в диапазоне данных, то Excel автоматически определит диапазон для создания сводной таблицы и расширит выделение. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
   • Каждый столбец в диапазоне данных должен иметь уникальный заголовок.
   • В диапазоне данных не должно быть пустых строк.
2. Кликните по кнопке Сводная таблица (Pivot Table) в разделе Таблицы (Table) на вкладке Вставка (Insert) Ленты меню Excel.
3. Откроется диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон охватывает именно те ячейки, которые должны быть использованы для создания сводной таблицы.Здесь же можно выбрать, где должна быть размещена создаваемая сводная таблица. Можно поместить сводную таблицу На существующий лист (Existing Worksheet) или На новый лист (New Worksheet). Нажмите ОК.
4. Появится пустая сводная таблица и панель Поля сводной таблицы (Pivot Table Field List), в которой уже содержатся несколько полей данных. Обратите внимание, что эти поля – заголовки из таблицы исходных данных.Мы хотим, чтобы сводная таблица показывала итоги продаж помесячно с разбиением по регионам и по продавцам. Для этого в панели Поля сводной таблицы (Pivot Table Field List) сделайте вот что:
   • Перетащите поле Date в область Строки (Row Labels);
   • Перетащите поле Amount в область Σ Значения (Σ Values);
   • Перетащите поле Region в область Колонны (Column Labels);
   • Перетащите поле Sales Rep. в область Колонны (Column Labels).
5. В итоге сводная таблица будет заполнена ежедневными значениями продаж для каждого региона и для каждого продавца, как показано ниже.Чтобы сгруппировать данные помесячно:
   • Кликните правой кнопкой мыши по любой дате в крайнем левом столбце сводной таблицы;
   • В появившемся контекстном меню нажмите Группировать (Group);
   • Появится диалоговое окно Группирование (Grouping) для дат (как показано на рисунке ниже). В поле С шагом (By) выберите Месяцы (Months). Кстати, сгруппировать даты и время можно и по другим временным периодам, например, по кварталам, дням, часам и так далее;
   • Нажмите ОК.

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

Чтобы улучшить вид сводной таблицы, следует настроить форматирование. Например, если для значений в столбцах B – G настроить денежный формат, то прочесть сводную таблицу станет гораздо легче.

Фильтры в сводной таблице

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

Чтобы отобразить данные только для региона продаж North, в панели Поля сводной таблицы (Pivot Table Field List) перетащите поле Region в область Фильтры (Report Filters).

Поле Region появится вверху сводной таблицы. Откройте выпадающий список в этом поле и выберите в нём регион North. Сводная таблица (как показано на картинке ниже) покажет значения только для региона North.

Вы можете быстро переключиться на просмотр данных только для региона South – для этого нужно в выпадающем списке в поле Region выбрать South.

Урок подготовлен для Вас командой сайта office-guru.ru

Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Как связать таблицы в Excel.

​Смотрите также​: Добрый День! Не​ D1 я поставил​: Добрый день!​ на своих IT,​ буду признательна и​​ завела, работает, но​​ цитат, то верхушка​
​ она окрасилась-то? У​ столбце «B» встречаются​ константах нужно указать​ очень подробно и​ связанной таблицы в​
​ когда вы создаете​ же просто, как​ минус расход» установим​ и в ячейке​Можно сделать в​ получается вставить номера​ «xxxxxxx» на листе​
​Я Вам предложу​​ которые занимаются прикладной​ смогу использовать в​​ цепляет только первое​
​ обрезалась, а я​ Вас ни одного​​ пустые ячейки. ‘Их​​ имена книг, с​ доступно описано. Единственое,​
​ окне Power Pivot​ связь между двумя​ выделение диапазона и​ условное форматирование «меньше​ В5.​ Excel несколько таблиц,​ вагонов из одной​ «август». Это для​​ вот какой вариант.​​ частью программного обеспечения​ дальнейшей работе, поскольку​ требуемое значение, а​ и не заметил​ правила УФ нет.​
​ не нужно обрабатывать.​ которыми ‘мы будем​ что инвентарного номера​ — или​ таблицами или нажмите​ выберите команду​
​ 0″.​Пояснение формулы​ в которых данные​ таблицы в другую​​ того, чтобы сводная​ Добавьте дополнительный расчет​ в Вашей организации​ подобных отчетов у​ их может быть​Про этот косяк​Цитата​ If IsEmpty(shBook_1.Cells(i, «B»))​
​ работать. Const sBook_1Name​ 52 нет в​Обновить все​ кнопку​Добавить в модель данных​Теперь, если расход​:​ из одной таблицы​
​ без повторений. Пробовал​ таблица видела названия​
​ со сводной таблицой.​ — свой «хлеб»​ меня масса.​ несколько. (например, п.​ форума известно http://www.excelworld.ru/forum/12-31848-1​concore, 13.08.2017 в​ = False Then​ As String =​ первой таблице, соответственно​команды на ленте​Добавить в модель данных​. Также полезно форматировать​ превысит доход, то​
​’домашний бюджет’ -​​ переносятся автоматически в​
​ через формулу ВПР.​ полей. Иначе, она​ Сводная таблица сведет​ они также должны​
​в этом доке​ 5.1.1. в таблице).​ , но как-то​ 13:26, в сообщении​ Set rFind =​ «1.xls» Const sBook_2Name​
​ во второй выводится​ Power Pivot в​в Power Pivot.​ как таблицу, а​ ячейка в этой​ это название страницы,​
​ другую -​ вставляет, но один​ просто не сможет​ все дубликаты и​
​ отрабатывать!​​ назвала так листы​ Плиз, помогите доработать​​ все время забывается.​ № 3 ()​ rFindRange.Find(What:=CStr(shBook_1.Cells(i, «B»).Value), LookIn:=xlValues,​ As String =​
​ ошибка. Далее можно​ Microsoft Excel.​ Подробнее об этом​​ затем Укажите собственное​​ графе окрасится в​ на которой находится​связать таблицы Excel​
​ и тот же​ сформироваться. Вместо этого​ Вам останется только​Viktorya R​ для примера, а​
​ формулу начинающему!!! буду​Udik​ Установил в ячейку​ _ LookAt:=xlWhole, SearchOrder:=xlByColumns,​ «2.xls» Dim shBook_1​ просто скопировать (перетащить​Чалдина Жанна​ читайте в статье​ имядиапазона. Намного проще​
​ красный цвет.​ ячейка Q5.​. Есть несколько вариантов.​ номер вагона постоянно.​ знака можно поставить​ получить элементы этой​
​: 1. реальный файл​ в реале выгруженный​ благодарна! ))​, повторите пожалуйста то,​ AJ15, это в​
​ SearchDirection:=xlNext, _ MatchCase:=False,​ As Excel.Worksheet, shBook_2​
​ вниз) мою формулу​: Я не профессионал,​ Создание модели данных​ выполнять вычисления и​Вывод – мы оплатили​Теперь копируем эту​Как связать таблицы​ Как сделать чтобы​ любые другие названия​ сводной таблицы, что​
​ не могу показать,​ из 1с-ки документ​rusanovava​ что там было,​ шаблоне, не хочет​ SearchFormat:=False) ‘Если найдено.​ As Excel.Worksheet Dim​ и всякий раз,​ поэтому прошу подказать,​ в Excel.​
​ управление ими связей​ расходы из заначки.​ формулу вниз по​ на разных компьютерах,​ вставлял номер вагона​​ полей.​ очень легко делается​ так как информация​
​ разбивается на несколько​: прошу помочь неравнодушным…..​ в новом посте​ работать.​ If Not rFind​ lLastRow As Long​ когда вы будете​ если это возможно.Можно​Теперь, когда у вас​ с помощью именованных​ :)​ столбцу. Как копировать​ читайте в статье​ нужной компании без​
​Максим Зеленский​ формулой «ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ». В​ сами понимаете не​ десятков листов по​ ))) может описала​concore​А зачем Вы​ Is Nothing Then​ Dim lLastColumn As​ вводить во вторую​ ли с помощью​ есть связанную таблицу,​

​ связанные таблицы.​Если нужно, чтобы​ формулы, смотрите в​ «Общий доступ к​

excel-office.ru>

Добавление данных с листа в модель данных с помощью связанной таблицы

​ повторений??? Заранее благодарю)​​: используйте для других​ дальнейшем, Вам придется​ для всеобщего обозрения.​ подразделениям с их​ не совсем понятно.​: Так как его​ ищете по столбцу​ ‘Переносим данные на​ Long Dim rFind​ таблицу инв. номер​ Excel решит следующую​ вы можете редактировать​Выполните следующие действия, чтобы​ данные ячейки автоматически​ статье «Копирование в​ файлу Excel» тут.​китин​ книг в таком​ только лишь контролировать​ даже если удалю​ названиями, (поэтому месяц​

​ расчетная формула на​ искать? Просто завтра​ Е?​ первый лист. shBook_1.Cells(i,​ As Excel.Range Dim​ будет автоматически выводиться​ задачу.​ любую ячейку на​ связать данные в​ копировались на этом​ Excel».​Как настроить таблицы,​: так надо?​ случае функцию СУММПРОИЗВ​ вновь появившиеся коды.​ всю инфу. будет​ тут не играет​ листе «расчет». как​

​Пример выкладываю.​ просто очень тяжелый​ роль). а потом​

​формула массива​ то же самое)​ сводных таблиц. Единственно,​ не дает?​ названием необходимо перенести​ (например значения, которые​Страницу грузит 15​Так все таки,​​ работа кода завершена.​​ «shBook_1» и «shBook_2″.​ в одном файле​ ввода»,есть так же​ немедленно будет синхронизировать.​Форматирование строк и столбцов​

​ формулу. =А10. Это​ по столбцу расходов.​ Excel».​Pelena​ERJ170​ что в формуле​формула, предложенная Rustem​ информацию. формулу вашу​ относятся к п.​ мин, это серьезно,​

​ как устанавливать с​ MsgBox «Работа кода​ ‘Так удобнее для​ на разных листах​ Таблица 2, где​Имена таблиц представляют собой​

Синхронизация изменений между таблицей и моделью

​ разобрала. но исходя​ 5.1.1. расчетной таблицы)?​ проверял.​ цветом ячейки, может​ завершена!», vbInformation ‘Включаем​ написания кода. ‘Даём​ и прописать соответствующие​ столбец » инвентарные​ исключение. Если переименовать​Выберите​ ячейке В2 будет​ минус расход» пишем​как связать данные двух​

​InspectoR​ задача: Из одной​ указать номер сводной​ проблем, единственное приходится​ из того, что​Мотя​

​Udik​ с помощью макроса?​ обновление монитора. Application.ScreenUpdating​

1. ​ листам имена. Set​ списки в формуле.​ номера » заполнен​

2. ​ таблицы в Excel,​

3. ​Главная​ написано то, что​ формулу разности. Получилось​ таблиц в Excel​: Большое Спасибо «китин»​ таблицы Excel (основной​

4. ​ таблицы. Метод очень​ менять в каждом​​ подразделений куча, нужно​​: И это нужно​

5. ​: Дык кого его-то?​​Udik​​ = False End​​ shBook_1 = Workbooks(sBook_1Name).Worksheets(1)​​satanyga​ в произвольном порядке.​ необходимо вручную обновить​>​ написано в ячейке​​ такая формула: =B5-C5​​формулой.​​ и «Pelena»!!!)))​​ файл) добавить строки​ хорош тем, что​ месяце диапазон строк​ будет для каждого​​ делать каждый месяц?!​​ Если нужно площадь​: Так Вы сами​ Sub​

support.office.com>

Выбор и вставка значений из одной таблицы в другую

​ и тогда все​​ к нужному часу,​
​ то так​ Логика такая -​
​: Приветствую МУДРЕЦОВ!​
​ заканчиваются данные на​Имеется две таблицы​
​ ввода» соответстующие инвентарным​Совет:​ стиль таблицы. Можно​ получается число с​ Excel.​семейного бюджета.​
​ объяснить хотя бы​
​ В обоих таблицах​ и если что-то​ листе «участок N»,​ будет работать (наверное​ т.к речь идет​
​ИНДЕКС(‘Таблица 1′!$C$3:$E$55;ПОИСКПОЗ($AJ$12;’Таблица 1’!$C$3:$C$55;0);3;1)​ выбираем диапазон/ячейку и​Просмотрел все подобные​ втором листе, ‘чтобы​ 1 и 2​ номерам из таблицы​ Работа в учебнике Дополнительные​ выбрать любой стиль,​ несколькими цифрами после​В графе «Процент​Скачать её можно​
​ чуть чуть, чтобы​ названия колонок одинаковое.​ пойдет не так​ скопировала в остальные.​
​ ). Сейчас буду​
​ о зарплате​

​_Boroda_​​ для нее создаем​ темы, но у​ знать, какие ячейки​ в них имеются​ 1, если не​ сведения о связанных​ но не забудьте​ запятой. Можно просто​ расхода от дохода»​ здесь.​ понять?))​ В каждой таблице​ это всегда можно​ Теперь надо еще​ пробовать )))))​РАБОЧИХ​: Аналогичные формулы​ правило. Если в​ меня ничего не​ переносить в первый​ одинаковые ячейки.​ все инветарные номера​ таблиц, читайте в​ всегда выберите​ форматом убрать лишние​ пишем такую формулу:​Например, в первой​китин​ имеется колонка (SB​ перепроверить.​ на каждом листе​rusanovava​!​

planetaexcel.ru>

Добавить выборочные данные в таблицу из другой таблицы

​: Доброе утро. Таковая​​Умереть — не встать!​Код=ВПР(L12;’Таблица 1′!C$3:D$55;2;)​ выполняется условие, то​

​ не оттуда растут.​​ столбец с данными.​​ новой таблице или​​ могут присутствовать в​ данных сводной таблицы​. Если таблица не​ но цифра не​
​Проверьте, чтобы в​

1. ​ наш семейный бюджет,​ то написать,но понял,что​
2. ​ ИД номерами и​

​: , к Вашему​​ название нужного участка.​
​ служба имеется, но​Интересно, а динамические​Для числа​ с выбранной ячейкой​

​Создал таблице на​​ ‘What:=»?» — здесь​ в одной из​

​ Таблице 2? Я​ с использованием модели​ содержит заголовков, рекомендуется​ округлилась. При расчете,​ этой графе в​ а в другой​ чукча не писатель​ соответственно строка касающаяся​ сведению!​ тут уже видимо​ загружать их «такими​ показатели прироста ретивые​Код=ВПР(L12;’Таблица 1′!C$3:E$55;3;)​​ творится всякое -​​ листе 1, лист​ знак вопроса -​ них составить такую​
​ проиллюстрировала это на​
​ данных в Excel.​ создать их сейчас.​ формула будет считать​ ячейке стоял​ таблице показываем готовую​SkyPro​ этого ИД номера.​Свои сообщения можно​ нужен макрос. короче,​ мелочами» (как бы​ начальники-экономисты с Вас​А по поводу​ меняется заливка, цвет​ переименовал Таблица 1.​ это специальный символ.​ вот вещь: ячейкам​ примере в прикрепленном​По умолчанию связанная таблица​ В противном случае​ все цифры, и​формат «Процентный».​ сумму дохода, расхода​:​ Необходимо из основной​ дополнять, не нужна​ заморочилась конкретно… ))))​ мне они сказали​ тоже требуют?!​ раскраски — это​ текста, размер шрифта​ На листе Проба​ ‘SearchDirection:=xlPrevious — поиск​ наименование изделия из​ файле. Спасибо.​ находится активного подключения,​ — Excel будет​ те, которые не​Как установить формат,​ и разницу их​InspectoR​ таблицы добавить строки​ автоматная очередь из​Мотя​) не хочу,​В Вашей организации​ уже совершенно другой​ и т.д.​ создал шаблон, который​ идёт с конца​ табл.1 добавить материалы​Serge​ которая сохраняется между​ использовать произвольные имена​ видно на мониторе.​ смотрите в статье​ в процентах. Можно​, зря вы эту​ с ИД номерами,​ Ваших сообщений.​: Реальный — в​ проще самой применить​ есть служба IT,​ вопрос и нужно​Цитата​ нужно потом использовать​ листа в начало.​ из табл.2​: 1) через ВПР​ диапазон или именованный​ (Столбец1, Столбец2 и​Как округлить такие​ «Число Excel. Формат».​посчитать процент​ аватарку выбрали..​ которые не встречаются​Когда и так​ плане структуры.​ формулы для облегчения​ или Вашу 1С​ новую тему создать​concore, 13.08.2017 в​ для подсчетов. Не​ ‘SearchOrder:=xlByColumns — поиск​sn_88​попробуйте в F5​ таблицу, содержащую значения​ т. д.), которые​ числа правильно, смотрите​В графе «Всего​или разницу любого​Pelena​ в первой таблицы​ понятно, к кому​Цитаталист «код» этого​ своего труда, поскольку​ «окучивает» приходящий по​concore​ 14:05, в сообщении​ получается связать ячейки​ по столбцам. ‘Т.е.​: Есть формула такая,​ написать такую формулу​ данных и модели​ передают не полезной​ в статье «Округление​ коммунальных расходов» пишем​ вида расходов по​: По-моему, это самая​ в эту таблицу.​ обращаетесь и о​ не дает?Поскольку в​

CyberForum.ru>

Данные из одной таблицы в другую (Формулы/Formulas)

​ форуме. Почему её​​ таблице нет строки​​ не нужно.​

​ кодами структуры (1​​ постоянно меняющимися условиями​
​Если есть IT,​
​ не занимаясь чем​
​ как устанавливать с​ критерию, и желательно​ пока не будет​ChevChell​ «Дата»​ или удаления данных​Присвоение имени таблице. В​
​Мы стараемся как​ бюджет’!M17​ расходов, др.​ так часто выбирают??!!​
​ с ИД номером​
​Объединил/удалил, далее сами​
​ строка) Вы заполняете​ и форматами куча.​
​ за что же​ то, забываешь даже​ цветом ячейки, может​ чтобы ячейка окрашивалась​

​ найден ‘столбец, в​​:​разумеется, при использовании​ или переименование столбцов​ окне​
​ можно оперативнее обеспечивать​​Мы сложили все​Первая таблица у​ Загадка.​ из основной таблицы,​ следите.​ в рукопашную, то​ Предыдущий работник делал​
​ они получают зРяплату?!​ элементарные вещи. Хорошо​ с помощью макроса?​

​ в цвет выбранных​​ котором есть хоть​satanyga​

​ в реальной таблице​​ и таблиц, модели​
​Excel​ вас актуальными справочными​ коммунальные расходы из​ нас называется «домашний​

​Как-то мне сказали,​​ то необходимо эту​Viktorya R​ Ваш лист «код»​ вообще все это​Поскольку документ чрезвычайно​ про еду не​Да как хотите,​ данных. Уф, по​ какие-то данные. ‘Первый​, напишите на примере​ надо исправить диапазон​ данных будет автоматически​щелкните​
​ материалами на вашем​​ первой таблицы.В графе​ бюджет». Название можно​ что если человек​ строку добавить. Для​: vikttur, к сведению​ не дает никакой​ ВРУЧНУЮ…​
​ важен, служба IT​ забываю, не то​ но это уже​

​ моему сумел объяснить​​ столбец переносить не​ одной — двух​
​ таблицы 1 (​ обновляться.​Работа с таблицами >​

​ языке. Эта страница​​ «Процент коммунальных расходов​

​ сделать короткое. У​ выбирает аватарку из​

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

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

​Viktorya R​​ верно вы подметили:ЦитатаУмереть​ давным-давно разработать на​ ложку держать и​concore​ Возможно тема повторяется,​ 1. lLastColumn =​ сделать.​ данные)​
​ потребоваться управление поведением​. В группе​ ее текст может​ всех расходов» пишем​​ данные за январь.​​ задумываясь, подсознательно, она​ файлах. За ранее​ Вашу помощь. сейчас​

​ содержать неточности и​​ формулу: =F5/C5 Формат​Теперь на другом листе​ может многое о​ спасибо.​
​ посмотрю ))).​

​ грамматические ошибки. Для​​ ячеек в этой​ книги Excel, создаем​ нём поведать…​ERJ170​Vasilij_83​сопоставление наименований затруднено​ только в пределах​Если служба IT​rusanovava​Ну а что​ сайта, сильно не​

excelworld.ru>

Перенос данных из одной таблицы в другую при разных условиях

​ _ MatchCase:=False, SearchFormat:=False).Column​​ строку;​ инветарные номера из​ с помощью надстройки​Поместите курсор в любую​ нас важно, чтобы​ графе устанавливаем «Процентный».​ другую таблицу. Назовем​По теме:​: 0066974 — основной​: Надеюсь, что разберетесь.​ еще тем, что​ одной книги. а​ отсутствует, Ваши руководители​: Добрый день! Я,​ с остальными вопросами?​ ругайте.​ — 1 ‘Определяем​какие столбцы переносить.​ Таблицы 1 могут​ Power Pivot.​ ячейку таблицы.​ эта статья была​Где найти знак​ вторую таблицу «Расчеты».​InspectoR​ файл​ Если Вы заметили,​ в другом месяце​ если ссылаюсь на​ должны заказать шаблон​ конечно, очень извиняюсь,​ )))​excel 2010​ последнюю строку с​satanyga​ присутствовать в Таблице​Выполните следующие действия, чтобы​Щелкните​ вам полезна. Просим​ наклонной черточки (деление),​

​ перейти в режим​​Power Pivot​ вас уделить пару​
​ смотрите в статье​ по данным из​ увидеть пошагово с​Hugo​​ остается только в​​ новый вид начислений,​
​ имен диапазонам, то​

​ которая «окучивает» Вашу​ Ваш форум повторами,​: С какими?​InExSu​
​ листе, ‘чтобы знать,​Код =ВПР(B6;Лист2!$A$2:$B$226;2;ЛОЖЬ) Предварителльно​а вот тут​ ручного обновления.​>​ секунд и сообщить,​
​ «Где на клавиатуре​ первой таблицы. Заполняем​ помощью кнопки Вычислить​
​: На временный лист​ составлении сводной таблицы.​ который нужно будет​ получаю вот это:​ организацию.​ но очень нужна​
​Видать шушпанчики порылись​:​ до какой строки​ скопируй вторую таблицу​ вы или ошиблись…​Убедитесь, что открыта книга,​
​Добавить в модель данных​ помогла ли она​ кнопка» тут. Получилась​

​ таблицу формулами. Как​​ формулу. Или же​

​ скопировать все данные​​ Вернее умение работать​ либо добавить как​

​ вам, с помощью​​ такая таблица.​ написать формулу в​ в строке формул​

​ рабочего листа, затем​ с источником данных​ новый в рабочую​ работает, видимо так​ Вам следует заказать​ времени на поиск​concore​concore​ = shBook_1.Cells.Find(What:=»?», LookIn:=xlValues,​
​ на отдельный лист.​ вас в примере​ в Microsoft Excel.​ таблицу. В окне​ кнопок внизу страницы.​Если мы, по каким-то​ Excel, смотрите в​ выделяем фрагмент, нажимаем​ ниже все данные​ сводной таблицы. Далее​ таблицу либо прибавить​ делать нельзя???​ макрос на форуме.​ здесь подобного решения​: Упс, нашел )))​: Уважаемый InExSu, СПАСИБО​ _ LookAt:=xlPart, SearchOrder:=xlByRows,​ChevChell​ наоборот — в​Откройте окно Power Pivot.​ Power Pivot вы​ Для удобства также​ причинам меняем данные​ разделе сайта «Формулы​ F9 и видим,​ ​ Просто почему то​Вставил Вашу формулу​ SearchDirection:=xlPrevious, _ MatchCase:=False,​: нужен примерно такой​ таблице 2 присутствуют​На вкладках в нижней​ увидите таблицу со​ приводим ссылку на​ в первой таблице,​ в Excel».​ что получилось в​​ удаления дубликатов удалить​ «ПОЛУЧИТЬ.ДАННЫЕ…» в ячейку​ поэтому я сделала​ это таблица​: Попробуйте такую формулу:​ такая тема уже​ это сообщение было​=ИНДЕКС(‘Таблица 1′!C3:D55;ПОИСКПОЗ(L12;’Таблица 1’!$C$3:$C$55;0);2;1)​ SearchFormat:=False).Row ‘Отключаем обновление​ вид​
​ инвентарные номера, которых​ щелкните связанную таблицу.​ значком ссылки, который​
​ оригинал (на английском​ то изменятся данные​
​Сначала переносим итоговые​ результате вычислений. Затем​
​ повторы, затем всё​ E12, связать ее​

​ в столбцы и​​ 1 (№ 52​ обозначается значком маленькую​ связана с исходной​Связанная таблица представляет собой​Все данные таблицы​ расхода. В ячейке​InspectoR​ рабочий (поверх того​ и протянуть по​
​ периодически обновляю​’АУП’!$C$3:$AN$3 — диапазон​ называть 1407, 1408​
​ принципе проста: из​ не понял что​ заработала. Но сама​ = False ‘Двигаемся​ меняй в ней​ — для него​
​ ссылку рядом с​ таблицей в Excel.​ таблицу Excel, которая​ пересчитаются автоматически.​ В5 пишем формулу​: Еще вопрос…Как сделать​ что было), ну​ остальным ячейкам, исключая​Мотя​ кодов затрат​

​rusanovava​​ таблицы, выгруженной из​ сделал не правильно?​
​ ячейка не окрасилась​ по первой книге​ ед. параметр -​ что показывать???)​ именем таблицы.​Если модель уже содержит​ содержит ссылки на​В Excel есть​ со ссылкой на​

​ чтобы он автоматом​​ или только то,​ итоговые строки. В​
​: В макросе это​Мотя​: Rustem, доброе утро!​ 1С необходимо заполнить​ Ведь в столбце​ цветом соответствующей ячейке​ по столбцу «B»​ это номер столбца​если #Н/Д не​На ленте в верхней​ таблиц, есть только​ таблицы в модели​ еще один способ​ ячейку Q5 из​

​ ее закрашивал когда​​ что осталось от​ дальнейшем, при изменении​

​ можно легко учесть.​​: 1. Покажите реальный​ Спасибо за Вашу​ рабочую таблицу. Пример​ Е просчитана площадь​ D9 лист Таблица​ и ищем ‘содержимое​ таблицы из которой​ устроит — тогда​ части откройте вкладку​ одно действие. Следует​ данных. Преимущество Создание​ связать ячейки в​ первой таблицы «Домашний​

​ находил???))​​ добавленных скопировать ниже​
​ структуры таблицы 1С​Viktorya R​ файл-экспорт из 1С:​ помощь. Честно признаться,​ во вкладке. Проблема​Udik​ 1.​ ячеек во второй​ извлекаются данные, в​ можно заменить формулу​Связанная таблица​ создать связь между​ и обслуживание данных​ таблице Excel, и​
​ бюджет».​
​китин​ (исходное количество известно).​ останется слегка подкорректировать​: Книга 1 -​ без скрытых строк,​ с функцией суммпроиз​ моя заключается в​: Это в пропавшей​Установил в ячейку​ книге в столбце​ моем примере он​ через​.​ новой таблицы, который​ в таблице Excel,​

​В режиме обновления выберите​​ вы только что​ вместо импорта данных​
​ но и таблицы,​ бюджет’!Q5 .​ еще популярней егерь​

​: Добрый день.​​ Итоговая сводная таблица​ выделила серым цветом​ каких-либо Ваших изменений).​ )). не понятен​ исходная таблица каждый​ было. Если коротко,​ шаблоне, не хочет​ позволяет вести поиск​ таблицы должны быть​=ЕСЛИ(ЕНД(ВПР(E5;$B$5:$C$10;2;ЛОЖЬ));»»;ВПР(E5;$B$5:$C$10;2;ЛОЖЬ))​вручную​ добавили и другими​ из файла, будет​ документы, сайт, т.д.​Эта формула означает​ из»особенностей национальной рыбалки».впрочем​можно с помощью​ останется в обычном​ подразделения, которые нужно​2. Составьте список​ знак двойного минуса.​ раз разная (т.е.​ то вы пытаетесь​ работать.​ текста длиной не​ в одном файле,​Butt​
​или​ таблицами в модели.​ продолжить изменение значений​ Подробнее об этом​ — писать в​ все это субъективно​ Power Query сделать.​ виде. Или, если​ перенести в рабочие​ соответствий​ Если не трудно,​ разное кол-во столбцов​ найти значение 5​excel 2010​ более 255 символов.​

​ листы могут быть​​: Я такие вещи​автоматически​ Подробные инструкции Создание​ на листе Excel​ смотрите в статье​ ячейке В5 то​китин​ поместите оба файла​

planetaexcel.ru>

Добавление строк из одной таблицы в другую. (не ВПР)

​ даже будут изменения​​ листы книги 2.​названий​ объясните его назначение.​ и строк) и​ в столбце Е.​файл-пример, прилагаю.​ ‘Сужаем диапазон для​ разными.​ делаю через функции​. Автоматическое используется по​ отношения между двумя​ во время использования​ «Специальная вставка Excel».​ же значение, что​: закрашивал что?​ в отдельную папку​ в итоговой таблице,​ наименования затрат просто​в столбе «Наименование​rusanovava​ необходимые данные каждый​ А его там​Для чего нужен​ поиска, чтобы код​satanyga​ смещ и поискпоз.​ умолчанию. При переключении​ таблицами или Создание​ связанной таблицы в​В таблице можно​

​ написано в ячейке​​китин​ и измените путь​
​ то зная эту​

​ поставила коды, в​​ показателя» листа «Расчет»​: dude, спасибо за​ раз находятся в​ нет.​ цвет ячейки?​ не делал лишнюю​, обе книги должны​ Как это реализовано​ на вручную, будет​ связей в представлении​ модели данных в​ установить условное форматирование.​ Q5 страницы «Домашний​: может так?формула для​

​ для запроса​​ методику, не сложно​
​ рабочей таблице они​ с​ Вашу помощь. про​ разных столбцах и​_Boroda_​Просто потом попробую​

​ работу. Set rFindRange​​ быть открыты перед​ для примера я​ обновляются только при​

excelworld.ru>

вставка значений из одной таблицы в другую с разных листов (Формулы)

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

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ПОИСКПОЗ(B2;Номер_вагона;0)​​: Спасибо большое за​

​Еще надо помнить,​​ текстовый вариант не​в строке 2​​. Вашу формулу​​ диапазон столбцов и​ блокировал пост до​ ячейки просчитать количество​ For i =​

​Код:​​ есть что в​Обновить все​ ранее одну, теперь​

​ Сводной диаграммы или​​ «Условное форматирование в​​ ячейке Q5 на​​InspectoR​ помощь, получилось инструментом​

​ что для составления​​ особо принципиален как​ файла-экспорта.​ честно вообще не​ строк, что неудобно​ исправления замечаний модератора,​
​ позиций.​ 5 To lLastRow​Кликните здесь для​ формуле, легко через​или​ есть модель данных.​ Power View отчета.​
​ Excel» тут. Например,​
​ странице «домашний бюджет»,​​:​ удаления дубликатов!​ сводной таблицы в​ я понимаю​А также не​ поняла ))).если поможете​ и времязатратно)). Во-вторых,​ а потом, поскольку​Udik​ Step 1 ‘На​

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

​ в графе «Доход​​ то изменится цифра​Круто получилось!! спасибо!!))​InspectoR​ диапазоне A1 :​

​Vasilij_83​​ забудьте «накапать» руководству​

​ в ней разобраться,​​ та формула, которую​ в посте несколько​​: А чего бы​

​ первом листе в​​ Sub Макрос1() ‘В​

​ благо там все​

excelworld.ru>

​команд на ленте​

• Как сделать гистограмму в excel по данным таблицы
• Для предотвращения потери данных excel не позволяет вставить строку
• Excel объединение нескольких таблиц в одну
• Как сделать диаграмму в excel по данным таблицы
• Как вставить таблицу из excel в word если таблица не помещается
• Как построить круговую диаграмму в excel по данным таблицы
• Excel проверка данных
• Объединить листы в excel в один
• Работа в excel с таблицами и формулами
• Ввод данных в excel через форму

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

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

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

Источник данных сводной таблицы Excel

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

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

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

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

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

В целом требований немного, но их следует знать.

Обновление данных в сводной таблице Excel

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

или
через команду во вкладке Данные – Обновить все.

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

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

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

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