Задача данных в Excel

Question

У меня есть огромный набор данных (тысяча строк записей) в Excel (столбцы - месяцы, клиенты, расходы). Я должен найти тех клиентов, которые тратят по крайней мере, скажем, 100 долларов США, в каждом месяце.

Есть ли быстрый способ решения? Я попробовал сводную таблицу, но безуспешно. Может быть, я должен загрузить набор данных в Access, а затем выполнить запрос? Есть идеи?

пример

Answer 1

Я бы подумал, что вы могли бы сделать сводку со строками в качестве клиентов, столбцами в месяцах, значениями в виде суммы расходов. Из этого, добавьте нормальную формулу столбца в конце, который имеет MIN столбцов ежемесячных расходов клиента (убедитесь, что ссылки относятся только к адресу ячейки, а не к GETPIVOTDATA ссылка). Из-за пробелов в значениях вам может понадобиться использовать версию массива MIN (например, =MIN(--(B2:M2)) и введите с помощью Ctrl+Shift+Enter, а не просто Enter). Фильтрация вашего MIN выше вашего лимита дает вам ваших клиентов.

Если вам придется делать это часто, доступ может быть проще.

Answer 2

Используйте вспомогательный столбец с вашей сводной таблицей.

Формула для D2 = =IF(SUMIFS($C$2:$C$19,$B$2:$B$19,B2,$A$2:$A$19,A2)>100,"Y","")

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

Answer 3

Вот еще одно решение, которое, я думаю, вы пытаетесь получить.

Скажите, что ваше Имя клиента находится в столбце C, а их сумма потрачена в D. Вы можете использовать формулу массива (вводится с помощью CTRL + SHIFT + ENTER), которая вернет список имен, где расходы превышают 100 долларов:

=IFERROR(INDEX(C$1:C$4,SMALL(IF(D$1:D$4>100,ROW(C$1:C$4)-ROW(C$1)+1),ROWS(C$1:C1))),"")

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

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