Недавно я на практике столкнулся с задачей подсчитать сумму расходов по разным категориям и хочу сегодня поделиться с вами несколькими вариантами решения. Допустим, исходный список расходов выглядит так:
Общая сумма затрат понятна, но хотелось бы видеть более конкретный расклад по статьям расходов.
Вообще, в списке явно просматриваются две категории – магазины и кафе. Поэтому имело бы смысл добавить третий столбец «Категория» и указать ее в каждой строке (как сейчас сделано в файле, который вы найдете в конце статьи). Но на скорую руку можно обойтись и без этого.
Я не пользуюсь русским Office, т.к. не понимаю локализованные формулы Excel. Поэтому приверженцам тотальной локализации придется потерпеть скриншоты английского интерфейса. Ссылки по теме:
Теперь к делу!
Способ 1 – Сводная таблица
Выделите ячейки с расходами (кроме итога) и выберите на ленте Вставка (Insert) – Сводная таблица (Pivot Table).
Сводная таблица откроется в новом листе. Дальше в Excel 2013 все элементарно — установите флажки полей для отображения, как показано в правой части рисунка ниже.
Как видите, сумма по каждому названию подсчитывается автоматически. В предыдущих версиях Office картина может немного отличаться, но смысл должен быть понятен.
Чтобы увидеть сумму расходов только в кафе или только в магазинах, выберите фильтром названия, соответствующие желаемой категории.
В строке GrandTotal отобразится сумма строк, выбранных фильтром. Чтобы не возиться с фильтрами вручную каждый раз, имело смысл ввести категории еще на первом листе, как я сказал выше. Но можно достичь желаемого результата прямо в сводной таблице. Читатель Павел Симаков записал для вас отличное видео о группировке данных в сводной таблице!
Я знал о сводных таблицах, но фильтры навели меня на мысль о том, нельзя ли обойтись без сводной таблицы. Оказалось, что можно!
Способ 2 – Сумма отфильтрованных значений
На исходном листе:
- Выделите столбцы с данными и выберите на ленте Данные (Data) – Фильтр (Filter). Я рекомендую выделять именно столбцы, т.к. при выделении ячеек автоматический фильтр может не сработать (коллега по работе совсем недавно впал в ступор от такого).
- Замените формулу СУММА (SUM) на ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), указав в качестве номера функции значение 9. Оно отвечает за сумму отображаемых строк.
- Выберите фильтром нужные названия или категорию, если имеется.
Вуаля! Общая сумма расходов в кафе как на ладони.
Вводя формулу SUBTOTAL, вы могли заметить, что у нее 11 функций, причем название каждой повторяется дважды (1 и 101, 2 и 102, и т.д.)
«Сотенные» функции учитывают скрытые строки — проверьте их работу самостоятельно.
Способ 3 – Форматировать как таблицу
Спасибо читателю Сергею, напомнившему в комментариях об этом простом способе. Он подробно описан в статье Николая Павлова (MVP Excel) Умные таблицы. Кстати, они тоже используют формулу SUBTOTAL :)
Книга Excel для экспериментов ниже, и да, можно поиграть прямо тут или на SkyDrive! Кстати, язык Excel в данном случае будет соответстовать языку вашего браузера.
А что вы в последний раз считали в Excel? :) Напишите в комментариях!
Вадим, прошу прощения за оффтоп, но ведете бюджет в Excel ?
Юрий, нет, не веду, хотя раньше частично вел. Тут просто надо было подсчитать один момент. А в записи просто понятный пример.
Каждый месяц считаю таблички в 30-40 тысяч строк и 30-100 столбцов.
Без сводной таблицы никак.
Сергей, да, я на работе тоже использую сводные таблицы (и глубже, чем показано тут).
Vadim Sterkin,
Продолжая начатый уже оффтоп, Вадим, где ты ведёшь, если вообще занимаешься этим, свой бюджет? ;)
Виктор, я не веду домашний бюджет, сорри :)
Вадим, по моему мнению, вести домашний бюджет в Excel не очень удобно. Я пользуюсь программой Money IQ на iPhone. Все быстро, удобно и всегда под рукой. Также есть куча похожих программ для iPhone, для Android и для Windows Phone.
Никита, конечно, для узих задач специализированное ПО всегда лучше универсального. Но показанным способом можно ведь считать не только бюджет :) Это ж просто пример.
Часто приходится выполнять подобные действия, но я использую «Форматировать как таблицу», включаю «Строка итогов» и вуаля!
Мне кажется так быстрее, чем у вас. Более того при моем способе интересно закрепляется верхняя строка при длинном списке.
Сергей, точно! Это отличный вариант, о котором я забыл, хотя читал статью Николая Павлова (MVP Excel) Умные таблицы.
Я бы не сказал, что это быстрее, чем сводная таблица, но попроще, наверное. Что же касается формулы SUBTOTAL, то именно она используется в умных таблицах :)
Добавил способ в статью.
Viktor Golub,
Прошу прощения что отвечаю на вопрос, который был задан не мне.
Просто хочу поделится программой, которую долго выбирал среди кучи подобных и веду в ней бюджет. http://www.youneedabudget.com Стоит на оф.сайте 60$, но на распродаже стим купил за 200 руб (можно запускать без стима). Есть пробный период. Описание почитайте http://gagadget.com/software/2013-02-06-zapiski-makovoda-prilozhenie-ynab-4-dlya-upravleniya-lichnyimi-finansami/
Ключевой момент для меня был наличие синхронизации между ПК и смартфоном. Может и вам подойдет :-)
Так скоро мы дойдем до азбуки.
Без обид, Вадим, про Windows у Вас получается куда лучше….
Сергей, не вопрос, но отмечу пару моментов.
1. Не все задачи эффективно решаются средствами Windows. Да, в Windows есть калькулятор, но в Excel считать удобнее :)
2. Если данная задача для вас слишком проста, это не значит, что я пишу о ПО хуже, чем о Windows. Пишу-то я одинаково.
Веду учёт всех расходов в Excel 2007. По столбцам виды расходов, счёт в Сбербанке, доходы (зарплата, пенсия и прочие доходы). Веду учёт уже более 3-х лет. По строкам даты. Ввожу только приход и расход по категориям. Все остальные итоги выводятся автоматически. Подсчёт ведётся и ежедневный и помесячный. Спасибо за новую тему.
Василий, а специализированным ПО не пробовали пользоваться? Или Excel устраивает для вашей задачи?
Расчетов в Excel делаю не много, в основном сбор, отображение выборочной информации и ссылки на детальные описания (похоже на маленькие БД без макросов). Что-то типа такого:
=HYPERLINK("["&Dienstblatt!$A$4&"]Strahlhalle!"&ADRESSE(VERGLEICH(INDIREKT(ADRESSE(ZEILE();SPALTE()-1));Strahlhalle!D:D;0);1);ZEICHEN(117)) =WENN(INDIREKT(VERKETTEN("'";INDIREKT(ADRESSE(ZEILE();1));"'";"!";ADRESSE(15;2)))=0;"";INDIREKT(VERKETTEN("'";INDIREKT(ADRESSE(ZEILE();1));"'";"!";ADRESSE(15;2)))) =VERKETTEN(LINKS(I10;SUCHEN(" ";I10));TEIL(I10;SUCHEN(" ";I10)+1;1);". ";TEIL(I10;SUCHEN(" ";I10;SUCHEN(" ";I10)+1)+1;1);".")Теперь по теме статьи. Вы, безусловно, вправе решать, о чем писать в своем блоге — это даже не обсуждается. Но на мой взгляд количество задач, которые может выполнить Excel, и их специфичность делает работу по описанию решения какой-то конкретной задачи не очень востребованной. Для этого есть форумы посвященные Excel’ю (типа вопрос — ответ). А Ваши статьи по оптимизации и решению проблем с Windows читаю регулярно и нахожу в них очень много нужной информации. Спасибо.
Вадим, спасибо за ответ — у вас немецкий Excel, как я понял :)
Аха, это уже намного лучше сформулировано, чем у Сергея выше, поэтому отвечу подробнее.
О выборе тем
Подавляющее большинство записей блога делится на две категории:
1. «Решения задач» с которыми сталкиваюсь я лично или другие люди (вопросы по почте, форумы). И тематика тут не ограничивается ОС Windows. В частности, я люблю Excel и очень активно использую его на работе. А задачи в нем решаю по мере их поступления.
2. «Как это работает», т.е. технологии, концецпии и т.п. В основом — Windows, но также и ПО (и не забывайте про серию об SSD).
Есть еще «Специально для некоторых постоянных читателей» (вело), и да, ими тоже выражали мне недовольство те, кто не катает :)
О тайном знании
Понятно, что лично вас (и многих других) интересует тайное знание (ТЗ) о Windows, которое я доставляю время от времени. Но должен ли я его доставлять в каждой записи? Нет, конечно.
ТЗ, как правило, содержится в материалах о новых продуктах и технологиях, например, в Windows 8 — я пишу о ней (в т.ч. и потому, что пользуюсь ОС сам — см. пункт 1 выше).
Окей, по памяти цитирую письмо, полученное от читателя блога пару недель назад:
Ответом, кстати, была ссылка на статьи о 7, немалая часть которых была написана в то время, когда большинство еще пользовалось XP :)
ТЗ не нужно большинству, им нужно что-то простое и распространенное. Хороший пример — запись о настройке Hyper-V. За исключением пары нюансов, там КО из разряда Next-Next-Next. Однако эта запись является одной из наиболее востребованных посетителями из поисковиков.
О специализированных ресурсах и конкретных задачах
Ресурсы «Вопрос-Ответ» есть по каждой теме, но
1. Нужно их знать.
2. Там должен быть ответ на ваш вопрос.
Да, его можно задать, но зачем, если ответ уже есть в моей рубрике Вопрос — Ответ? :)
Наверное, рассказы о решениях конкретных задач не очень нужны постоянным читателям. В лучшем случае они просто берут их на заметку.
Но они очень нужны тем, кто ищет решения в поисковиках. Не надо сбрасывать со счетов хвосты, и да, я исследую ключевые слова, принимая решение о публикации записи.
Посетители из поисковиков не самоцель, но я рассчитываю на то, что они вольются в постоянную аудиторию и научатся чему-то полезному — в этом и цель.
Хороший пример — запись об ошибке 05 после установки обновлений. Решение описано на тысячах сайтов, наверное, и я не хотел писать в блоге КО. Однако потом подумал, что с учетом относительно высокой позиции блога в поисковиках, такая запись будет полезна, если я изложу в ней причины ошибки и поделюсь тем, о чем не пишет никто (удаление с помощью DISM помогло как минимум одному человеку :).
Результат публикации?
Заметьте, что с этой страницы покидают блог на 5% реже, чем в среднем по сайту.
Таким образом, при подборе тем для записей и определении глубины освещаемого материала я учитываю разные критерии, и не все они очевидны со стороны. И да, я постоянно экспериментирую с тематикой статей, учитывая в том числе и пожелания читателей :)
Не соглашусь, возможно для кого-то приложения из пакета Office — открытая книга, но для очень многих (и, к сожалению, меня) их функционал остается не освоенным полностью.
Спасибо, Вадим, за Ваш труд! Уже около года остаюсь постоянным читателем блога, чему очень рад! В дискуссиях практически не участвую, так как мои познания и опыт еще очень скромны. Думаю я такой далеко не один. С энтузиазмом читаю свежие и более ранние записи на любые темы и в каждой масса полезного и интересного. Здорово просто, что Вы есть и вкладываете силы и время в этот ресурс. Очень помогает в работе и повседневных делах. Извините за излишнее отступление от темы :)
Excel мне помогал при расчетах планируемых и фактических затрат на свадьбу и небольшое строительство. на работе ведем журнал ремонтов. Удобно и наглядно.
Евгений, спасибо, что читаете мой блог (в т.ч. предыдущие записи — там много интересного:) и поддерживаете идею этой записи. И я очень рад, что она подвигла вас на отправку первого (судя по имени/e-mail) комментария!
Вовлечение читателей в обсуждение — это стратегическая задача развития блога, и барьер «первого коммментария» преодолевают далеко не все постоянные читатели.
Вадим, спасибо!
Позволю себе дополнить. Кроме функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в версиях Excel выше 2007 есть еще более замечательная функция «АГРЕГАТ», у которой гораздо больший выбор аргументов и их сочетаний. Хотя, конечно, для решения такой несложной задачи можно обойтись и без нее.
И еще, при использовании «Умных таблиц», не нужно в строке итогов вводить формулы. На вкладке «Конструктор», которая появляется при форматировании диапазона в таблицу, в группе «Параметры стилей таблиц» есть флажок «Строка итогов». При его активации, появляется строка с ячейками с раскрывающимся списком. Таким образом можно выбрать любую функцию.
Аркадий, спасибо за дополнение! Да, в умных таблицах формулы вводить не надо, но заметь, что по умолчанию там используется тот же SUBTOTAL, о чем я и написал.
Вадим, не знаю в курсе ты или нет. Просто в сводной таблице можно полученные категории сгруппировать вручную магазины в группу «магазины», кафе в «кафе». Перед этим придется потаскать поля, чтобы поля одной группы были рядом, а потом выделяется эта группа, alt+shift+[стрелка вправо]. И так далее. Преимущество в том, что можно видеть сразу суммы по всем группам и не ковыряться каждый раз в фильтрах.
сорри, если получилось заумным тоном ботаника, мол, я умный… Не хотел никого обидеть :)
Павел, я не совсем понял идею, если честно. Возьмите файл^^, запишите видео, киньте ссылку :)
А так, ковыряться в фильтрах каждый раз неудобно, я согласен. Поэтому я и написал, что имеет смысл сразу категории ввести (на первом листе), тогда сводная таблица все четко покажет.
Vadim Sterkin,
Записал видео, вот ссылка, как просили: http://youtu.be/N2jEe55xfgA
Сорри, что без голоса, у меня все спят :)
Там в экселе встроенных видов группировки много: даты можно группировать помесячно (пишет первые три буквы месяца), произвольное количество дней (например, понедельно), поквартально, по годам и т.п. Цифры можно группировать интервалами (например, 1-100; 101-200; 201-300… и т.п.).
Редко этой штукой пользуюсь, потому что, действительно, проще добавить на листе с исходными данными нужный столбец. Так надежнее. Но если надо быстро просмотреть данные группами, то вполне себе оправдывается такой вариант с группировкой.
Павел, теперь я понял, что вы там перетаскивали :) Отличное видео! Я добавил его в статью :)
Отличное видео и способ группировки, но честно, чтобы использовать возможности сводной, ей изначально немного не доставало данных. Нужно ввести доп. столбец, тогда ни фильтрация, ни группировка не понадобятся — сводная сама все сделает. Вот ссылка на файл http://yadi.sk/d/PEdPVpv5ALzkX
Аркадий, да, я об этом сразу и написал. Более того, первый черновик статьи содержал шаг «Добавление категорий» :) Убрал я его, находясь под влиянием того, что в моем (реальном) случае подсчет нужно было сделать всего один раз, причем на планшете! Поэтому операция «сводная таблица -> фильтр в ней» вела к цели быстрее всего ;)
Но если таблица обновляется / дополняется, категории сильно упрощают жизнь. Я добавил столбец «Категории» в файл-пример — желающие могут сравнить, насколько проще решается задача, если позаботиться о них заранее.
Вадим, каюсь, предисловие читал не внимательно. В «примерном» файле нужно еще автофильтр распространить на столбец «категория», иначе, при сортировке могут быть неточности.
All,
Знаете, чем особенно интересна эта запись? Тем, что в обсуждении читатели активно демонстрируют свои навыки работы, причем их решения не хуже или даже лучше описанных в материале.
Здесь две причны:
1. Введение столбца «Категории»^^ позволяет продемонстрировать более эффективный способ решения, что редко бывает, когда я пишу о Windows :)
2. Разнообразие способов решения задачи позволяет показать те, что не описаны в статье. Второе тоже не часто бывает в моем блоге, потому что я, как правило, полностью раскрываю тему.
И, кстати, с точки зрения вовлечения аудитории в обсуждение — это не всегда правильно, потому что лишает людей возможности выразить себя. Поэтому некоторые гуру онлайн-маркетинга советуют иногда не раскрывать тему полностью (и при этом они же могут советовать делать глубокие исследовательские статьи:)
Периодически анализирую детализацию из личного кабинета Билайна
Они, наконец, поля с балансом стали форматировать как Числовые и операция по созданию сводной таблицы занимает 5 секунд.
Удобно, что если я вижу аномальную цифру по услуге, можно двойным кликом вывести дополнительные данные: номер исходящего звонка или дату.
Артем, кстати, Николай Павлов (MVP Excel) в июле опубликовал статью/видео Анализ детализации мобильной связи (Билайн). И он там преобразует в число сначала :) Видимо, до билайна только недавно дошло, что нужно упростить клиентам жизнь.
Василий, заинтересовал ваш способ учета расходов в Excel! Не могли бы Вы скинуть в качестве примера вашу таблицу? Я и сам веду учет в Excel, но не так сложно как у Вас, хотел бы сделать учет доходов/расходов более умным.
Бюджет и айфон как-то у меня не сочетаются.
Спасибо за статью, прям как по заказу в самую тему, веду учет в экселе экспортируя данные из coinkeeper на ios, и как раз выборка нужна была…как то пытался, но руки не доходили , а тут еще и несколькими способами)
Вчера как раз прикрутил график , а сегодня будет и по категориям)
Евгений, спасибо что отписал и тут помимо почты — рад, что статья пригодилась и вовремя.
Если действительно надо посчитать сумму один раз для конкретного параметра повторяющегося несколько раз, то я пользуюсь формулой «суммесли()».
Я бы третий столбец озаглавил бы «Магазин», где ставил бы единички против магазинов, чётвёртый — аналогично «Кафе». Для подсчёта магазинов использовал формулу с СУММПРОИЗВ(), считающую векторное произведение столбцов с суммами и единичками (пустые ячейки эквивалентны 0). Для кафе — аналогично.
Столбец с единичками для кафе можно описать как =1-С3, т.к. столбцы взаимоисключающие.
Такое вот фильтрующее свойство дельта-функции. :-)
Александр, а если у вас 10 тысяч строк, тоже 1/0 ставить будете? :)
С такими таблицами (на 10 тысяч строк) не сталкивался. Приходилось работать только с таблицами, где 1/0 хватало за глаза.