Как создавать категории в Excel

Обновлено: 02.07.2024

Зачем использовать подкатегории личного бюджета (см. видео ниже)

При создании личного бюджета необходимо выбрать категории бюджета, включая категории бюджета доходов и бюджетов расходов. Многие бюджетные приложения и бюджетные таблицы для управления финансами имеют настройку основной категории и подкатегории. Каждая подкатегория является подмножеством основной категории (иногда называемой родительской категорией или основной категорией). Например, у вас может быть основная категория под названием «Еда», а в категории «Еда» будет подкатегория «Продовольственные товары», категория «Рестораны и рестораны» и категория «Кафе». Это позволяет вам получить промежуточные итоги для каждой подкатегории (Бакалеи, Рестораны и Кофейни) и общий итог для основной категории (Еда). В других бюджетных приложениях есть только категории без возможности создания подкатегорий, что некоторым людям может показаться проще при создании бюджета и отслеживании своих расходов.

Как создавать подкатегории, группируя категории

Шаблоны Excel, Georges Budget for Excel v14 и Georges Excel Checkbook Register v7 позволяют создавать собственные категории и редактировать список категорий. В списке категорий вы можете создавать только категории доходов и категории расходов (но не основные категории и подкатегории). Однако в разделе настраиваемых отчетов сводной таблицы обоих шаблонов Excel вы можете сгруппировать свои настраиваемые категории, создав таким образом отчет по основной категории/подкатегории. Например, если вы создали в своем бюджете элементы списка категорий под названием «Автокредит», «Автомобильный бензин», «Автострахование» и «Ремонт автомобиля», вы можете создать настраиваемый отчет сводной таблицы, показывающий промежуточные итоги для этих категорий. Затем вы можете сгруппировать (и разгруппировать) эти категории и получить основную общую сумму этих категорий и назвать ее Total Auto Costs. Посмотрите пример видео ниже о том, как сгруппировать и разгруппировать элементы сводной таблицы в приложении управления финансами и приложении чековой книжки, чтобы создать отчет по основной категории и подкатегории.

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

Видео: сводная таблица: групповые категории бюджета

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

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

Поскольку при выборе категории нам нужно отобразить список подкатегорий, мы добавим три столбца: SC1, SC2 и SC3. Здесь нужно ввести пункты, обозначающие подкатегории.Списки, которые мы создаем с опцией Проверка данных, всегда смотрят на диапазон ячеек, из которых берутся значения, и наша основная задача — создать один такой список диапазонов, который будет меняться в зависимости от выбранного категория. Для этого мы создадим формулу, использующую массивы:

После ввода формулы необходимо ее подтвердить, нажав CTRL+SHIFT+ENTER, иначе Excel сообщит об ошибке. Далее путем копирования формулы заполняем диапазон значений, которые будут извлекаться в списке подкатегорий. В ячейках, где Excel находит значения, он их отображает, иначе возвращает 0. Поэтому нам нужно добавить еще одну формулу, которая заменяет все «нули» на «пустые» символы:

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

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

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

Создайте список элементов, сгруппированных по категориям (модели телефонов в группах брендов). Сначала вставьте название бренда, а затем под ним каждую модель. Добавьте несколько пробелов перед названием каждой модели. Это визуально указывает, какие модели принадлежат какому бренду.
Чтобы быстро вставить пробелы, попробуйте функцию REPT или VBA.

Выберите ячейку, в которую вы хотите вставить раскрывающийся список (B1), и на ленте выберите Данные > Проверка данных.

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

Выберите диапазон элементов списка и их категории, затем нажмите клавишу ВВОД (или щелкните стрелку справа).

Вернувшись в окно "Проверка данных", нажмите "ОК" для подтверждения.

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

Выпадающие категории в Google Таблицах

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

Сегодня я покажу вам, как определить категории в ваших данных менее чем за 5 минут с помощью надстройки интеллектуального анализа данных Microsoft Excel. Предполагается, что вы уже установили надстройку интеллектуального анализа данных для Excel и настроили надстройку так, чтобы она указывала на экземпляр SQL Server со службами Analysis Services, к которым у вас есть права доступа.

Для примера мы будем использовать учебную электронную таблицу, которую можно найти в Codeplex по адресу:

Наша цель — выявить скрытые категории или группировки в наших данных.

Прочтите ниже, если у вас не получается просмотреть пошаговое видео.

Шаг 1. Определение категорий

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

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

Шаг 2. Чтение отчета о категориях

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

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

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

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

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

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

Третий раздел называется «Профили категорий». Это берет столбец, а затем показывает каждую категорию в этом столбце.

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

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

Билли Декер

Билли Декер — консультант StatSlice Systems. Он получил двойную степень по физике и математике в Техасском университете в Остине и получил степень магистра физики в Техасском университете в Арлингтоне. Ранее он работал в Global Technical Services в качестве старшего аналитика по обучению и в Bell Hellicopter в качестве инструктора по обучению. Его технический опыт включает, помимо прочего, SQL, SAP, Business Objects, QlikView и Sharepoint.

Читайте также: