Получить строки с датой до указанной даты в Excel

Question

Оригинальный вопрос:

Допустим, у меня есть таблица - Table 1 - которая выглядит так:

Как бы я автоматически заполнил таблицу - Table 2 - ниже?

Вторая таблица должна показывать только значения из таблицы 1, если они были доступны 30 июня. Например, A должно показывать только 10000, а не 5000.

---

Обновленный вопрос (чтобы было понятнее):

В принципе, как я могу поместить формулы в ячейки от C16 до C27 чтобы они были заполнены, как на картинке выше? Ячейки от C16 до C27 должны иметь возможность извлекать последние значения для A, B, C, D, доступные в первой таблице на даты в ячейках E16 E27 .

Например: ячейка C25 должна вернуть 15 000, поскольку это последнее доступное значение на 30 сентября. Он не должен возвращать 20000, потому что это значение было доступно только после 30 сентября.

Большое спасибо всем за вашу помощь!

Answer 1

Есть несколько более простых способов сделать это в Excel. Тем не менее, я использую LO Calc, и несколько простых формул, которые работают в Excel, не работают в Calc. Я должен был использовать метод, который мог проверить. Итак, вот решение, которое также работает в Calc.

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

Мой вывод за сентябрь 2016 года не совпадает с вашим. Потребовалось несколько минут, чтобы понять, что это потому, что вы не включили данные C и D за сентябрь в свой пример. Мой результат верен для данных.

Формулы

Начнем со вспомогательного столбца. Таблица 2 основана на именах в календарных кварталах. Вспомогательная колонка дает вам это. Формула в F2:

=CEILING(MONTH(E2)/3)&A2

Первая часть вычисляет, в каком квартале находится дата. Имя присоединяется к этому с помощью & .

Это подводит нас к сути проблемы. Формула в C16:

=IFERROR(INDEX(C$2:C$12,SUMPRODUCT(MAX((F$2:F$12=F16)*ROW(F$2:F$12)))-1),"")

Как это устроено

Я объясню формулу изнутри, прорабатывая.

• SUMPRODUCT(MAX((F$2:F$12=F16)*ROW(F$2:F$12)))

  SUMPRODUCT идентифицирует строку, содержащую последнюю запись в столбце помощника, которая соответствует имени и кварталу.

• INDEX(C$2:C$12,last_matching_row-1)

  INDEX выбирает это значение из ваших данных в столбце C, используя строку этого последнего значения. Тем не менее, местоположение указывается относительно диапазона, в котором он находится, а не фактической строки на рабочем листе, поэтому -1 корректируется для этого.

• =IFERROR(formula,"")

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

Answer 2

Мне удалось добиться желаемых результатов по следующей формуле:

=SUMIFS(B1:B6,C1:C6, "<="&TEXT(K1,"dd-mm-yyy"), A1:A6,A1)

Столбец B - это столбец значений из вашей таблицы A.

Столбец C - это столбец Date из вашей таблицы A.

"<=" & TEXT(K1, "dd-mm-yyy") равно <= 30 июня 2016 года (возможно, есть лучший способ сделать это).

Столбец A - это столбец Name из вашей таблицы A.

функция SUMIFS принимает более 1 критерия - поэтому мы суммируем значения в столбце B , где даты в столбце C меньше или равны дате в ячейке K1 , и где имена в столбце A соответствуют имени в A1 .

Прошу прощения за плохую схему моего ответа, я новичок в этом - надеюсь, это поможет.

РЕДАКТИРОВАТЬ: На основе обновленной информации, добавленной к вопросу, мой ответ не работает. Мой ответ суммирует значения, но вопрос требует только самого последнего значения.