Я искал и не мог понять, почему мои формулы не работают.
Прежде чем опубликовать свои формулы, мне нужно объяснить, что я пытаюсь сделать, и это немного сложно (по крайней мере, это единственный способ, который я могу придумать).
**Фон**
Мне нужно создать электронную таблицу по бюджету моего подразделения (с сортировкой по категориям). В настоящее время, когда я загружаю доходы/расходы из нашей финансовой системы, она просто выдает строки данных (RAW DATA), отсортированные по дате.
Цель 1- Экспортированные НЕОБРАБОТАННЫЕ ДАННЫЕ автоматически сортируются по категориям, а затем в таблице наглядно отображается, сколько средств было потрачено в каждой категории.ДОСТИГНУТО
Цель 2- Экспортированные НЕОБРАБОТАННЫЕ ДАННЫЕ сортируются по категориям и показывают ТОЛЬКО определенные месяцы на основе выбора пользователя.ЗАСТРЯВШИЙ
ПРИМЕР ЭКСПОРТА НЕОБРАБОТАННЫХ ДАННЫХ:
ТАБЛИЦА: Данные 2015 г.
Date | Description of Expense | Amount | Codes
Jan-15 | Workshop for employee | 100000 | WRKS
Jan-15 | Workshop for employee | 100000 | WRKS
Feb-15 | Sponsors for employee | 200000 | SPON
Feb-15 | Sponsors for employee | 100000 | SPON
Feb-15 | Workshop for employee | 300000 | WRKS
Mar-15 | Sponsors for employee | 100000 | SPON
Если использовать приведенную выше таблицу в качестве примера НЕОБРАБОТАННЫХ ДАННЫХ (под названием «Data2015»), которые я получаю при экспорте из наших финансовых систем (которые я преобразовал в таблицу в Excel).
Для достижения цели 1 я создал новую таблицу (под названием «Categories») с описаниями категорий.
ТАБЛИЦА: Категории
Categories | Codes
Workshop | WRKS
Sponsors | SPON
Затем создали еще одну таблицу (под названием «Бюджет»), как показано ниже:
ТАБЛИЦА: Бюджет
Categories | Codes | Amount
Workshop | WRKS | 500000
Sponsors | SPON | 400000
Формула, которую я использовал для столбца «Сумма» в таблице «Бюджет», выглядит следующим образом:
=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])
Итак, эта формула помогает мне достичь Цели 1.
В своей попытке достичь Цели 2 я попробовал следующее.
Создана таблица для сбора месяцев, которые пользователь хочет видеть:
ТАБЛИЦА: Условия
Month | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015
Пользователь ставит "x" в столбце X, если он хочет увидеть расходы за этот конкретный месяц.
Я использовал эту формулу в столбце Background:
=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")
Я хочу затем дополнительно отфильтровать месяцы по категориям, как и раньше.
Так похоже на таблицу категорий выше, но для определенных месяцев на основе того, что пользователь хочет видеть.
Мне удалось заставить это работать, но только для 1 строки, а не для всей таблицы.
Я бы хотел, чтобы вы пересмотрели мою формулу и дали мне знать, что я делаю неправильно или, может быть, даже моя логика в достижении цели 2 неверна. Ваше понимание будет очень полезным, и я извиняюсь за очень запутанную ситуацию, я не могу придумать более простого способа объяснить.
Я создал еще одну электронную таблицу, которая будет имитировать таблицу Data2015 со следующей формулой
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
Month_View! — это место, гдеТАБЛИЦА: Условиянаходится и $D$5 - это янв-15, $D$6 - это фев-15 и т. д.
Эта формула в значительной степени заполняет таблицу, если она обнаруживает, что A5 (столбец, где находится дата) совпадает сТАБЛИЦА: Данные 2015 г.Столбец «Дата» (в той же строке, отсюда и @).
До этого момента все было хорошо, все работало, пока в ячейке A5 было "Jan-15" или "Feb-15". Чтобы заполнить эту ячейку в соответствии с предпочтениями пользователя, я использовал следующую формулу (которая не дала желаемого результата):
=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")
Итак, для меня эта формула означает следующее:
ЕСЛИ Дата вДанные2015таблица соответствует одной из дат/значений (в формате «д/мм/гггг») вСостояниестолбец с названием «Фон» (который будет отображаться только в том случае, если пользователь поставит «x» в названии месяца, который он хочет видеть), затем заполните эту ячейку значением изДанные2015Столбец «Дата».
Если ячейка затем заполнится «15 января» или «01.01.2015», то остальная часть таблицы будет заполнена, что я затем могу использовать для дальнейшей фильтрации по категориям с помощью формулы, которую я использовал для достижения цели 1.
Но ячейка не заполняется "Jan-15" или "1/01/2015", вместо этого она показывает пустую или #VALUE!
Кажется, она ссылается наСостояниетаблица с @, а мне она не нужна.
Как бы это ни было запутанно... Если вы можете понять, чего я пытаюсь добиться, любое понимание или обсуждение поможет. Возможно, мой мозг просто слишком перегружен на данном этапе.
решение1
Я бы использовал встроенную в Excel функциональность таблиц — формулы не нужны. Выберите диапазон и нажмите Ctrl-T, чтобы начать.
Это включит фильтры в верхней строке — вы/ваши пользователи сможете использовать их для фильтрации дат по годам и/или месяцам.
Для функциональности подсчета итогов добавьте строку «Итоги» (используя ленту «Инструменты таблицы» / «Конструктор»), и в этой строке вы можете выбрать, какую агрегацию вы хотите для каждого столбца (сумма, количество и т. д.). Это автоматически подстроится под вашу фильтрацию.
решение2
Хорошо, я решил эту проблему!
Вот формула, которую я использовал:
=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))
*P.S.: Извините, если ссылки отличаются от примеров, приведенных в моем вопросе.
Лист Data2015BG1 содержит формулы, которые определяют, есть ли в столбце Trigger "x" или нет, и представляют необходимые данные на основе выбранного месяца. Формула, которую я использовал, следующая:
=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")
Надеюсь, это имеет смысл, я бы выложил фотографии, чтобы наглядно показать, чего именно мне удалось добиться, потому что по сути я сделал все проще для использования, но у меня пока недостаточно репутации, чтобы выкладывать изображения! Может быть, в следующий раз.
Фильтры могли бы работать, но я создаю это для людей, у которых, по сути, нет большого опыта работы с Excel, так что даже просить их фильтровать — это немного перебор (... я знаю, я знаю...)