Как присвоить категории в excel

Как присвоить категории в excel

В этой статье рассказывается о присвоении значения или категории на основе указанного диапазона чисел в Excel. Например, если ежемесячные продажи человека находятся в диапазоне от 0 до 100, показатели продаж в этом месяце будут оцениваться как Плохие, если между 101 и 500, оцениваются как Удовлетворительные, если между 501 и 1000, оцениваются как Хорошо, а если более 1000 , оценивается как отлично. Методы, описанные в этой статье, могут помочь вам с этой задачей.

Назначение значения или категории на основе диапазона чисел с помощью формул

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

Формула 1: Использование формулы с комбинацией функции ЕСЛИ и функции И

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

=IF(AND(B2>=0,B2<=100),»Poor»,IF(AND(B2>100,B2<=500),»Fair»,IF(AND(B2>500,B2<=1000),»Good»,IF(AND(B2>1000),»Excellent»,0))))

Заметки: если заданное число не входит в указанный диапазон чисел, формула вернет результат как 0.

Формула 2: использование функции VLOOKUP, чтобы сделать это

Функция ВПР также может помочь решить эту проблему.

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

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

=VLOOKUP(B2,$E$2:$F$5,2)

Открыть чтобы узнать больше о функции ВПР.

Примечание: В формуле $E$2:$F$5 — это диапазон таблиц, который мы создали на шаге 1.

Присвоить категорию данным на основе диапазона

В Excel, как мне взять числа из одного столбца и присвоить их категории в другом? Например, если я устанавливаю диапазоны, которые соответствуют определенным словам 1-10 = Начальный, 11-20 = Средний и т.д. Как бы я взял значения 2, 5, 9, 19, 15, 3 и назначил бы их соответствующим категориям в соответствующий столбец?

2 ответа 2

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

Ключевые особенности этой формулы:

1. Массив категорий является первым аргументом функции INDEX . Если вы хотите больше категорий, включите их здесь, разделенные запятыми, каждая из которых заключена в кавычки.
2. Массив нижних границ диапазона категорий является вторым аргументом функции MATCH . Количество значений в массиве должно соответствовать количеству категорий. Каждое значение является нижней границей определенного диапазона категорий.
3. A2 — это адрес значения, которое вы классифицируете ( 2 — это первое значение в данных вашего примера).

Если бы вы расширили свои категории, включив в них другую категорию, например «Расширенные» для значений 21+, формула будет

Обратите внимание, что оба массива расширены, но все остальное остается прежним.

Как присвоить категории в excel

Добрый день, друзья!
Опять обращаюсь за помощью.
Есть массив с данными по продажам. Массив более 100 000 строк
Клиентов больше 500.
Необходимо каждому клиенту присвоить категорию «важности» A, B, C, D в зависимости от заданной суммы его всех покупок за определенный месяц определенного года, чтобы потом можно было в сводной таблице наблюдать динамику продаж категорий клиентов за нужный мне период.

Буду признателен за хелп.

Добрый день, друзья!
Опять обращаюсь за помощью.
Есть массив с данными по продажам. Массив более 100 000 строк
Клиентов больше 500.
Необходимо каждому клиенту присвоить категорию «важности» A, B, C, D в зависимости от заданной суммы его всех покупок за определенный месяц определенного года, чтобы потом можно было в сводной таблице наблюдать динамику продаж категорий клиентов за нужный мне период.

Буду признателен за хелп. Сапожник

Сообщение Добрый день, друзья!
Опять обращаюсь за помощью.
Есть массив с данными по продажам. Массив более 100 000 строк
Клиентов больше 500.
Необходимо каждому клиенту присвоить категорию «важности» A, B, C, D в зависимости от заданной суммы его всех покупок за определенный месяц определенного года, чтобы потом можно было в сводной таблице наблюдать динамику продаж категорий клиентов за нужный мне период.

Буду признателен за хелп. Автор — Сапожник
Дата добавления — 07.05.2017 в 09:46

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

Но я вспомнил одну деталь — есть некоторые клиенты, которых я рассматриваю как одного, в силу некоторых обстоятельств, например Петров и Иванов это братья, а значит товар ушел в одну и ту же «семью», которую я назову, к примеру «Петров-Иванов». А вот Сидоров — это клиент, покупающий от своего имени, он останется Сидоровым, но под условным названием «Прочие». Но считать их как участников групп я хочу так — 1) Петров-Иванов, 2) Сидоров
То есть, по отдельности Петров и Иванов могут относиться к категории С или Д и т.д., но это было бы некорректно, а вот в сумме они уже могут стать категорией А, что уже будет правильно в учете их как покупательской силы.

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

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

Но я вспомнил одну деталь — есть некоторые клиенты, которых я рассматриваю как одного, в силу некоторых обстоятельств, например Петров и Иванов это братья, а значит товар ушел в одну и ту же «семью», которую я назову, к примеру «Петров-Иванов». А вот Сидоров — это клиент, покупающий от своего имени, он останется Сидоровым, но под условным названием «Прочие». Но считать их как участников групп я хочу так — 1) Петров-Иванов, 2) Сидоров
То есть, по отдельности Петров и Иванов могут относиться к категории С или Д и т.д., но это было бы некорректно, а вот в сумме они уже могут стать категорией А, что уже будет правильно в учете их как покупательской силы.

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

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

Но я вспомнил одну деталь — есть некоторые клиенты, которых я рассматриваю как одного, в силу некоторых обстоятельств, например Петров и Иванов это братья, а значит товар ушел в одну и ту же «семью», которую я назову, к примеру «Петров-Иванов». А вот Сидоров — это клиент, покупающий от своего имени, он останется Сидоровым, но под условным названием «Прочие». Но считать их как участников групп я хочу так — 1) Петров-Иванов, 2) Сидоров
То есть, по отдельности Петров и Иванов могут относиться к категории С или Д и т.д., но это было бы некорректно, а вот в сумме они уже могут стать категорией А, что уже будет правильно в учете их как покупательской силы.

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

Заранее признателен! Автор — Сапожник
Дата добавления — 07.05.2017 в 12:06

Как сделать зависимые выпадающие списки в ячейках Excel

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

Пример создания зависимого выпадающего списка в ячейке Excel

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

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

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

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

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

Список категорий и подкатегорий в зависимом выпадающем списке Excel

Признаюсь, что в предложенном мной варианте домашнего бюджета я ограничиваюсь только категорией, поскольку для меня такого разделения расходов вполне достаточно (название расходов / доходов рассматривается как подкатегория). Однако, если вам нужно разделить их на подкатегории, то метод, который я описываю ниже, будет идеальным. Смело используйте!

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

Зависимый выпадающий список подкатегорий

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

Рабочая исходная таблица Excel

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

Можно было бы также использовать таблицы с первого изображения. Разумеется, формулы были бы разными. Однажды даже я нашел в сети такое решение, но оно мне не понравилось, потому что там была фиксированная длина списка: а значит, иногда список содержал пустые поля, а иногда и не отображал все элементы. Конечно, я могу избежать этого ограничения, но признаюсь, что мне больше нравится мое решение, поэтому к тому решению я больше не возвращался.

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
3. В качестве типа данных выберите «Список».
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Раскрывающийся список для категории.

3. Создание зависимого выпадающего списка для подкатегории

Сейчас будет весело. Создавать списки мы умеем — только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите «Данные» / «Проверка данных», а в качестве типа данных — «Список».

В источник списка введите следующую формулу:

Вид окна «Проверка вводимых значений»:

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

В нашем примере диапазон будет перемещаться по столбцу Подкатегория в рабочей таблице (G2:H15). Перемещение начнем от ячейки H2, которая также является первым аргументом нашей функции. В формуле ячейку H2 записали как абсолютную ссылку, потому что предполагаю, что мы будем использовать раскрывающийся список во многих ячейках.

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

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

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?