Таблица эксель заполненная

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Исходные данные

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

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

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

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

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

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

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

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

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

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

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

P.S. Углубленные знания о сводных таблицах можно получить в

Назначение таблицы Счет - вычислить сумму покупки (заказа).

Для диапазонов C5:C14 и E5:E14, а также для ячеек E16 и E17 следует установить формат Денежный.

Таблица Ведомость в Excel

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

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

Формулы нужно ввести в ячейки E3 (=C3*D3) и F3 (=E3*0,87) и затем, используя операцию быстрого заполнения, записать формулы в остальные ячейки таблицы.

Как сделать прайс-лист в Excel

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

Расчет электроэнергии в Excel

Таблица Электроэнергия предназначена для расчета стоимости электроэнергии в месяц.

Расчет выполняется на основании показаний электросчетчика. В ячейку нужно ввести показания счетчика на момент начала использования таблицы, например на 1 января 2015.

Расход электроэнергии вычисляется как разница между текущим и предыдущим показаниями счетчика. В ячейки C6 и E6 надо ввести, соответственно, формулы =ЕСЛИ(B6 <> "";B6-B5;"") и =ЕСЛИ(C6 <> "";C6*D6;""). Затем, используя операцию заполнения диапазона ячеек , заполнить формулами ячейки столбцов C и E.

Пример построения диаграмм в Excel

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

Таблица расчета калорийности продуктов

Мы не задумываемся, сколько калорий потребляем за завтраком, обедом или ужином. Справиться с этим можно очень просто: на каждой упаковке с продуктами указывается калорийность того или иного продукта в 100 г, а количество съеденных продуктов можно взять приблизительно или взвесить перед едой. Составим такую таблицу. Количество калорий в конкретном продукте будем вычислять по формуле. Например, содержимое ячейки E3 будет вычисляться по формуле =C3/100*D3. Тогда выделив ячейки с E3 по E10, можно заполнить эти ячейки формулами. Для этого на вкладке Главная -> Редактирование следует выбрать Заполнить -> вниз. Ячейки заполнятся формулами. Чтобы в ячейке E11 получить общее количество калорий, необходимо ввести формулу =СУММ(ЕЗ:Е10).

В этой таблице видно, сколько калорий мы получаем, когда съедаем определенное количество фруктов или овощей. В таблице приведены сведения в расчете на средние размеры. Формулы находятся только в последнем столбце, в ячейках с F7 по F17. Для того чтобы посчитать калории для яблока, следует в ячейку F7 вписать формулу =C7*C$4+D7*D$4+E7*E$4. Таким образом, белки, углеводы и жиры, содержащиеся в одном яблоке, умножаем на соответствующее количество килокалорий и складываем их между собой. Знак $ в адресе ячейки ставим для того, чтобы при автоматическом заполнении формулами не произошло ошибки.

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

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене - $50 и определенный % книг по более низкой цене - $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа "что если" в Excel .

Таблица данных с одной переменной.

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

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А .

3. Выберите диапазон A12:B17 .

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке Данные , кликните на Анализ "что если" и выберите Таблица данных из списка.

5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4 .

Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

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

6. Нажмите ОК.

Результат.

Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.

Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.

Таблица данных с двумя переменными.

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

1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).

2. Внесите различные варианты высокой цены в строку 12 .

3. Введите различные проценты в столбце А .

4. Выберите диапазон A12:D17 .

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

5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.

6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7 .

7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4 .

Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.

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

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

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

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

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

Свойства ячейки

Ячейками в Excel называют область пересечения строки и столбца.

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

Форматирование таблицы

Эта функция применяется ко всем ячейкам, то есть к самой таблице. В программе есть встроенная библиотека шаблонов, которая экономит время на оформление внешнего вида.

Формулы

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

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

Вставка объектов

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

Рецензирование

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

Примечания

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

Настройка внешнего вида

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

Если кому-то не удобно пользоваться сеткой, ее можно отключить.

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

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

Заголовки

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