Функция Excel 2007, последние три элемента

Question

Мой вопрос двоякий, так что терпите стену текста. Я делаю что-то вроде банковской таблицы. Я буду вводить доходы / расходы в четыре столбца (Дата / Сумма / Тип / Описание), и они мне нужны, чтобы отслеживать текущие расходы. У меня уже есть, так что, если дата не сегодня, он не будет добавлять / вычитать, пока не будет. Кроме того, у меня так две / три / четыре недели в будущем. Однако я хочу добавить кое-что к эффекту "Последние три зарплаты". Столбец "Тип" содержит только три возможных значения:"Доход", "Перевод" и "Расход". Если я смогу найти функцию, которая будет работать для одной, я уверен, что смогу легко заставить ее работать с остальными. Мне нужно, чтобы показать самую последнюю сумму "дохода". Например:

Дата Тип суммы

1 сентября 100 Доход

2 сентября 100 Расход

3 сентября 100 Доход

4 сентября 100 Расход

5 сентября 100 Доход

6 сентября 100 Доход

7 сентября 100 Доход

Допустим, сегодня 6 сентября. Я бы хотел, чтобы он показывал количество 5 сентября, 5 сентября и 3 сентября. Я не хочу, чтобы это показывало расходы, и это еще не 7 сентября. Должно быть три функции (по одной для каждого блока), так как мне заставить его выполнять самые последние, вторые по самые последние и так далее? Если я смогу заставить его работать, я могу также отредактировать, чтобы получить описание, и при необходимости переоборудовать его на расходы.

Вопрос 2:

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

Answer 1

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

Чтобы получить 3 самые последние даты дохода, вы можете использовать эту формулу массива:

=INDEX(dates,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

Куда:

даты - диапазон ячеек / диапазон столбцов, который содержит даты
типы - диапазон ячеек / столбец, который содержит "Доход", "Перевод" или "Расходы"
XXX - содержит 1 (самый последний), 2 (второй самый последний) или 3.

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

=INDEX(amounts,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

&

=INDEX(descriptions,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

В приведенном ниже примере даты относятся к A2:A100 , суммы относятся к B2:B100 , типы относятся к C2:C100 а описания относятся к D2:D100 .

Чтобы формулы работали, таблицу данных необходимо отсортировать по дате (от самой старой до самой последней). Обязательно передайте формулы, используя Ctrl + Shift + Enter. Это также предполагает наличие уникальных дат для каждого типа транзакции (т. Е. Нет нескольких записей о доходах за одну дату).

---

Если вы не хотите сортировать таблицу по дате, вот формула массива, которую вы можете попробовать:

=INDEX(dates,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

Куда:

даты - диапазон ячеек / диапазон столбцов, который содержит даты
типы - диапазон ячеек / столбец, который содержит "Доход", "Перевод" или "Расходы"
XXX - содержит 1 (самый последний), 2 (второй самый последний) или 3

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

=INDEX(amounts,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

а также

=INDEX(descriptions,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)