☰

Запрос данных Excel

tag-icon • tag-icon microsoft-excel worksheet-function date
Запрос данных Excel

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

Date       Category    Amount
03-15-21   Auto        500.00
02-12-21   House       375.00

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

Как извлечь данные по категориям и месяцам?

Я посмотрел на sumifs, sumproduct, index match, не нашел решения. Буду признателен за любые указания.

решение1

Сводные таблицы! Выберите всю таблицу, нажмите «Вставить» > «Сводная таблица». Следуйте подсказкам, пока не появится боковая панель «Поля сводной таблицы». Теперь вам нужно перетащить поля в нужные поля ниже. Вы можете поиграться, чтобы получить то, что вам нравится. Но для начала,

Оставьте Фильтры пустыми. Перетащите Дату в раздел «Столбцы». Перетащите Категорию в раздел «Строки». Перетащите Сумма в раздел «Значения».

Если Excel правильно распознает ваши даты, сводная таблица должна автоматически группироваться по месяцам. Если нет, вы также можете вставить новый столбец B с заголовком Month и добавить формулу =EOMONTH(A2,0)в B2. Используйте Month вместо Date в сводной таблице. Вам необходимо обновить сводную таблицу, если вы добавляете столбец после создания сводной таблицы в первую очередь. Если EOMONTH по-прежнему выдает ошибку, убедитесь, что ваш формат даты соответствует настройкам вашего регионального формата даты.

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

решение2

Вы можете использовать PivotTable напрямую:

введите описание изображения здесь

решение3

Это решает проблему:

введите описание изображения здесь

Как это работает:

  • Формула в Y34 для вспомогательных данных:

    =TEXT(V34,"mmmm")

  • Формула массива (CSE) в Z34 для уникального названия месяца:

    {=IFERROR(INDEX($Y$34:$Y$49,MATCH(0,COUNTIF($Z$33:Z33,$Y$34:$Y$49),0)),"")}

  • Формула массива (CSE) в AA33 для уникального имени категории:

    {=IFERROR(INDEX($W$34:$W$49,MATCH(0,COUNTIF($Z$33:Z33,$W$34:$W$49),0)),"")}

Примечание.

  • Закончите формулу массива с помощьюCtrl+Shift+Enter.

  • Для большей аккуратности в дальнейшем вы можете скрыть вспомогательные данные в столбце Y.

  • Формула в ячейке AA34:

    =SUMPRODUCT((TEXT($V$34:$V$49,"MMMM")=$Z34)*($W$34:$W$49=AA$33)*($X$34:$X$49))

Формула заполнения по всему диапазону.

При необходимости измените ссылки на ячейки в формуле.

Связанный контент