Как в таблице Excel

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

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

Сводная таблица в Excel

Для примера используем таблицу реализации товара в разных торговых филиалах.

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

Самое рациональное решение – это создание сводной таблицы в Excel:

  1. Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
  2. В меню «Вставка» выбираем «Сводная таблица».
  3. Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
  4. Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

  • Первая строка заданного для сведения данных диапазона должна быть заполнена.
  • В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
  • В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.



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

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

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

Создадим отчет с помощью мастера сводных таблиц:

  1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
  2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
  3. Следующий этап – «создать поля». «Далее».
  4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
  5. Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
  6. Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).

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

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

А вот такой отчет можно сделать, если перетащить поля между разными областями:

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

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

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

Проверка правильности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

2.2.5.2. Ввод данных в список Excel 2003

Ввод данных в список Excel можно осуществлять двумя способами:

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

Ввод данных непосредственно в строку списка Excel рассмотрен в предыдущем разделе. В этом разделе рассмотрим ввод, удаление и изменение данных в списке с помощью формы.

«Форма» — это окно диалога, предназначенное для удобного ввода, удаления и поиска данных в списках. Для создания полей в форме редактор Excel использует заголовки списка.

Для отображения формы на экране необходимо в меню «Данные» выбрать команду «Форма».
Рис. 1.

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

Вставка записей в список с помощью формы:

  • Выделите заголовки списка Excel для создания полей в форме;
  • Выполните команду «Данные» — «Форма»;
  • Введите данные новой записи;
  • Нажмите кнопку «Добавить» для добавления записи или «Вернуть», чтобы отменить добавление записи;
  • Нажмите кнопку «Закрыть».

Изменение записей в списке Excel с помощью формы:

Удаление записи из списка Excel с помощью формы:

Поиск данных в списке Excel с помощью Формы:

Далее …>>> 2.2.5.3. Сортировка строк и столбцов

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

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

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

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

  1. Выделите ячейки, которые необходимо преобразовать в таблицу. В нашем случае мы выделим диапазон ячеек A1:D7.
  2. На вкладке Главная в группе команд Стили нажмите команду Форматировать как таблицу.
  3. В раскрывающемся меню выберите стиль таблицы.
  4. Появится диалоговое окно, в котором Excel уточняет диапазон будущей таблицы.
  5. Если она содержит заголовки, установите опцию Таблица с заголовками, затем нажмите OK.
  6. Диапазон ячеек будет преобразован в таблицу в выбранном стиле.

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

Изменение таблиц в Excel

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

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

Чтобы внести дополнительные данные в таблицу Excel, необходимо изменить ее размерность, т.е. добавить новые строки или столбцы. Существует два простых способа сделать это:

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

Изменение стиля

  1. Выделите любую ячейку таблицы.
  2. Затем откройте вкладку Конструктор и найдите группу команд Стили таблиц. Нажмите на иконку Дополнительные параметры, чтобы увидеть все доступные стили.
  3. Выберите желаемый стиль.
  4. Стиль будет применен к таблице.

Изменение параметров

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

  1. Выделите любую ячейку таблицы.
  2. На вкладке Конструктор в группе команд Параметры стилей таблиц установите или снимите флажки с требуемых опций. Мы включим опцию Строка итогов, чтобы добавить итоговую строку в таблицу.
  3. Таблица изменится. В нашем случае внизу таблицы появилась новая строка с формулой, которая автоматически вычисляет сумму значений в столбце D.

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

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

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

  1. Выделите любую ячейку таблицы и перейдите на вкладку Конструктор.
  2. В группе команд Сервис выберите команду Преобразовать в диапазон.
  3. Появится диалоговое окно с подтверждением. Нажмите Да.
  4. Таблица будет преобразована в обычный диапазон, однако, данные и форматирование сохранятся.

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

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

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

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

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

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

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

Для начала добавьте таблицу. Чтобы продемонстрировать больше возможностей формул в таблицах Word, я возьму список покупок: наименование, количество, цена, сумма.

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

Выберите Формула и функцию Product(left). Она позволяет перемножать ячейки слева.

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

Чтобы пересчитать цифры, выделяем весь столбец и пересчитываем поля по F9.

В итоговой ячейке нам понадобится формула Sum(Above). Она суммирует цифры в ячейках над ней.

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

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