Финансовый анализ в Excel с примером

Жизнь человека из мира технологий — это невероятная комбинация цифр, показателей, которые периодически требуют систематизации. Чтобы осуществить последнее, приходится использовать особые технические инструменты. Наша статья докажет, что сводные таблицы в 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 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

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

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

Список задач

Даже самая светлая голова при ясном уме и твёрдой памяти однажды даст сбой, и вы что-то забудете. Хорошо, если это будет покупка корма для рыбок, поздравление тёщи с Днём матери или поливка фиалки вашей второй половинки. Они побулькают, пошипят и поквакают, а ваша совесть останется чистой. Но вдруг вы не оплатите предмет первой необходимости — интернет? Вам будет стыдно смотреть на себя в зеркало. И в тот злополучный день вы сломаетесь и пообещаете составлять списки дел. А пока вы будете мучиться выбором среди электронных планировщиков, попробуйте начать с простого перечня задач.

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

Бюджет путешествия

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

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

Инвентарный список

Что объединяет пожар, потоп, кражу и приезд родни погостить «на недельку»? Правильно, высокая вероятность утраты целостности вашего имущества.

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

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

Список контактов

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

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

Диаграмма Ганта

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

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

Разумеется, мощности Excel позволяют создавать эти самые диаграммы. Их конёк — наглядность и доступность.

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

Семейное древо

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

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

График дежурств

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

Впишите имена домочадцев в ячейки шаблона, распределив работы по дням недели до и после полудня. И не забудьте повесить распечатку на самое популярное место в квартире — холодильник. Теперь никто не сошлётся на свою забывчивость.

Журнал техобслуживания

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

Журнал пробега

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

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

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

Ежедневник

Только у членов общества, живущих по принципу «с утра выпил — день свободен», перечень дел заканчивается с открытием близлежащего магазина. Остальным же приходится крутиться порой не хуже, чем белке в колесе, упорно укладываясь в ограниченные временные рамки. И дабы не забыть в суматохе свой список планов, люди предпочитают фиксировать его в ежедневниках. Предложенный шаблон хорош тем, что позволяет разбивать каждый трудовой час на 15-минутные отрезки.

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *