Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск последней непустой ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Поиск последней непустой ячейки (Формулы/Formulas)
Поиск последней непустой ячейки
zavik121 Дата: Суббота, 25.06.2022, 15:34 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Всем привет!
файл с примером: https://docs.google.com/spreads....sharing
Суть такая: в столбец С вводится число, а в столбце D рассчитывается количество пустых ячеек до верхней непустой ячейки в столбце С.
Для чего: чтобы потом введенное число в столбец С поделить на количество пустых строк (рассчитанное в столбце D) и ввести это поделенное число во все пустые строки.

Не могу подсчитать количество пустых строк до верхней записи.

Быть может, кто-то предложит более простой вариант заполнения? либо поможет высчитать пустые строки сверху
Блгодарю!
 
Ответить
СообщениеВсем привет!
файл с примером: https://docs.google.com/spreads....sharing
Суть такая: в столбец С вводится число, а в столбце D рассчитывается количество пустых ячеек до верхней непустой ячейки в столбце С.
Для чего: чтобы потом введенное число в столбец С поделить на количество пустых строк (рассчитанное в столбце D) и ввести это поделенное число во все пустые строки.

Не могу подсчитать количество пустых строк до верхней записи.

Быть может, кто-то предложит более простой вариант заполнения? либо поможет высчитать пустые строки сверху
Блгодарю!

Автор - zavik121
Дата добавления - 25.06.2022 в 15:34
Gustav Дата: Суббота, 25.06.2022, 18:00 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2077
Репутация: 827 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Посмотрел задание и таблицу... и точек соприкосновения обнаружил немного... :)
Если вам, как мне показалось, нужна формула для столбца B, дающая такие же результаты, что и в столбце G, то вот формула для ячейки B3 (и дальнейшего протягивания вниз):[vba]
Код
=INDEX(FILTER(C3:C;C3:C<>"");1) /
(INDEX(FILTER(ROW(C3:C);C3:C<>"");1) -
INDEX(SORT(FILTER(ROW(C$1:C2);C$1:C2<>"");1;FALSE);1))
[/vba]т.е. сумма блока из колонки C делится на разность номеров строк: последняя строка текущего блока - последняя строка предыдущего блока.

Как видно, я использовал свою любимую гугловскую функцию FILTER. Вполне возможно, что матерые экселисты, не использующую эту функцию, предложат другое решение (на функциях ПОИСКПОЗ или ВПР).

[p.s.]Мой вариант без фильтра:[/p.s.][vba]
Код
=ArrayFormula(INDEX(C3:C;MATCH(TRUE;C3:C<>"";0)) /
(MATCH(TRUE;C3:C<>"";0)+ROW(C3)-1 -
MAX(ROW(C$1:C2)*(C$1:C2<>""))))
[/vba]
или чуть-чуть подсократив:[vba]
Код
=ArrayFormula(INDEX(C3:C;MATCH(;-(C3:C="");)) /
(MATCH(;-(C3:C="");)+ROW(C2) -
MAX(ROW(C$1:C2)*(C$1:C2<>""))))
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 26.06.2022, 01:15
 
Ответить
СообщениеПосмотрел задание и таблицу... и точек соприкосновения обнаружил немного... :)
Если вам, как мне показалось, нужна формула для столбца B, дающая такие же результаты, что и в столбце G, то вот формула для ячейки B3 (и дальнейшего протягивания вниз):[vba]
Код
=INDEX(FILTER(C3:C;C3:C<>"");1) /
(INDEX(FILTER(ROW(C3:C);C3:C<>"");1) -
INDEX(SORT(FILTER(ROW(C$1:C2);C$1:C2<>"");1;FALSE);1))
[/vba]т.е. сумма блока из колонки C делится на разность номеров строк: последняя строка текущего блока - последняя строка предыдущего блока.

Как видно, я использовал свою любимую гугловскую функцию FILTER. Вполне возможно, что матерые экселисты, не использующую эту функцию, предложат другое решение (на функциях ПОИСКПОЗ или ВПР).

[p.s.]Мой вариант без фильтра:[/p.s.][vba]
Код
=ArrayFormula(INDEX(C3:C;MATCH(TRUE;C3:C<>"";0)) /
(MATCH(TRUE;C3:C<>"";0)+ROW(C3)-1 -
MAX(ROW(C$1:C2)*(C$1:C2<>""))))
[/vba]
или чуть-чуть подсократив:[vba]
Код
=ArrayFormula(INDEX(C3:C;MATCH(;-(C3:C="");)) /
(MATCH(;-(C3:C="");)+ROW(C2) -
MAX(ROW(C$1:C2)*(C$1:C2<>""))))
[/vba]

Автор - Gustav
Дата добавления - 25.06.2022 в 18:00
zavik121 Дата: Понедельник, 27.06.2022, 09:57 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Посмотрел задание и таблицу... и точек соприкосновения обнаружил немного...

Благодарю Вас! Предложенный Вами вариант подходит как нельзя кстати! :)
 
Ответить
Сообщение
Посмотрел задание и таблицу... и точек соприкосновения обнаружил немного...

Благодарю Вас! Предложенный Вами вариант подходит как нельзя кстати! :)

Автор - zavik121
Дата добавления - 27.06.2022 в 09:57
zavik121 Дата: Понедельник, 27.06.2022, 10:57 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Единственное, что для arrayformula не подходит выражение ROW(C3), не соображу как побороть <_<
 
Ответить
СообщениеЕдинственное, что для arrayformula не подходит выражение ROW(C3), не соображу как побороть <_<

Автор - zavik121
Дата добавления - 27.06.2022 в 10:57
Gustav Дата: Вторник, 28.06.2022, 15:14 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2077
Репутация: 827 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
для arrayformula не подходит выражение ROW(C3)
Хмм... это для чего оно у Вас не подходит? Все три формулы, которые я привел в сообщении №2, проверены и работоспособны. Они предназначены для помещения в ячейку B3 и затем копирования вниз. При этом в каждой ячейке ниже будет содержаться своя копия этой формулы и, соответственно, свой результат вычисления по своей копии формулы.

Если же Вы пытаетесь сочинить формулу массива, которую можно поместить в одном экземпляре только в самую верхнюю ячейку диапазона - B3, и чтобы при этом ячейки ниже содержали собственные результаты вычислений по этой единственной формуле, то не уверен, что в данной ситуации это достижимо, так как формула содержит вертикальные вычисления по столбцу C. Во всяком случае, у меня не получилось создать такую формулу...


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
для arrayformula не подходит выражение ROW(C3)
Хмм... это для чего оно у Вас не подходит? Все три формулы, которые я привел в сообщении №2, проверены и работоспособны. Они предназначены для помещения в ячейку B3 и затем копирования вниз. При этом в каждой ячейке ниже будет содержаться своя копия этой формулы и, соответственно, свой результат вычисления по своей копии формулы.

Если же Вы пытаетесь сочинить формулу массива, которую можно поместить в одном экземпляре только в самую верхнюю ячейку диапазона - B3, и чтобы при этом ячейки ниже содержали собственные результаты вычислений по этой единственной формуле, то не уверен, что в данной ситуации это достижимо, так как формула содержит вертикальные вычисления по столбцу C. Во всяком случае, у меня не получилось создать такую формулу...

Автор - Gustav
Дата добавления - 28.06.2022 в 15:14
zavik121 Дата: Вторник, 28.06.2022, 17:04 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

понял благодарю!
 
Ответить
Сообщениепонял благодарю!

Автор - zavik121
Дата добавления - 28.06.2022 в 17:04
Gustav Дата: Понедельник, 04.07.2022, 01:10 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2077
Репутация: 827 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
не уверен, что в данной ситуации это достижимо

Оказалось, достижимо! Из раздумий над этой темой вынес простое эмпирическое правило достижимости/возможности создания единственной формулы массива для верхней ячейки диапазона, вычисляющей самостоятельные результаты для каждой ячейки ниже. Правило формулируется так: если решение задачи может быть сведено к использованию функции/-ций ВПР(VLOOKUP), то создание единственной формулы массива для верхней ячейки диапазона возможно. Эта возможность естественным образом следует из соответствующей "массивной" способности самой функции ВПР(VLOOKUP).

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

Поскольку единственная формула массива для второго случая (с неоднократным повторением фрагментов) предположительно будет выглядеть достаточно громоздкой в рамках данного обсуждения, ниже будет рассмотрен только первый случай - с двумя формулами. Имея полный расклад по двум формулам, далее желающие могут попробовать самостоятельно собрать единственную формулу-монстра для второго случая.

Итак, сначала построим первую формулу - "справочник". Для этого предварительно создадим в файле новый пустой рабочий лист "ДляВПР". Далее последовательно будем помещать в ячейку A1 этого листа формулы ниже, наблюдать за возвращаемыми каждый раз значениями и комментировать их.
[vba]
Код
=FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"")

2    Ввод
5    300
10    5000
16    800
22    8835
[/vba]
Как видно, только что приведенная формула возвращает непустые значения столбца C, которые вкупе с соответствующими номерами строк представляют собой достаточный набор данных для решения нашей задачи. Мои формулы выше в этой теме говорят о том, что можно взять любое число из колонки C (ниже заголовка "Ввод") и разделить его на разность номеров строк текущей и предыдущей строк. Для числа 300 текущей строкой будет 5, а предыдущей - 2. Неудобство заключается в том, что числа 5 и 2 находятся хоть и в соседних, но в разных строках. А для удобства наших вычислений хотелось бы их видеть в одной строке.

Ниже будут проделаны последовательные манипуляции формулами, приводящие наши данные к удобному виду. Для начала добавим к уже имеющимся данным третий столбец - последовательную нумерацию строк результата (не путать с номерами строк рабочего листа из первого столбца):
[vba]
Код
={FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))}

2    Ввод    1
5    300    2
10    5000    3
16    800    4
22    8835    5
[/vba]
Чтобы числа 2 и 5 оказались в одной строке "справочника для ВПР" (но в разных столбцах), нужно фактически продублировать первую колонку, после чего вертикально "сдвинуть" на одну строку первый дубль относительно второго. Для этого вычислим ниже два поднабора (подмножества) от исходного набора данных: первый - без последней строки, второй - без первой:
[vba]
Код
=FILTER({FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))};
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>"))

2    Ввод    1
5    300    2
10    5000    3
16    800    4
[/vba]
[vba]
Код
=FILTER({FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))};
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)

5    300    2
10    5000    3
16    800    4
22    8835    5
[/vba]
Оба поднабора содержат одинаковое количество строк (на одну меньше общего набора). Это даёт нам возможность горизонтально "состыковать" оба поднабора, взяв из первого - только первую колонку, а из второго - первые две колонки:
[vba]
Код
={FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}

2    5    300
5    10    5000
10    16    800
16    22    8835
[/vba]
Получившийся новый набор данных и представляет собой "справочник для ВПР". Его колонки можно озаглавить как "Предыдущая строка", "Текущая строка" и "Текущая сумма" соответственно. Напомню, что последняя формула предназначена для ячейки A1 листа "ДляВПР".

Наконец, используя полученные выше результаты, построим вторую формулу массива (довольно простую) - для ячейки B3 листа "Лист1" (функция ARRAY_CONSTRAIN обеспечивает развертывание формулы ниже на нужное количество строк):
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;3) / (
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;2) -
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;1))
;MAX('ДляВПР'!B:B)-ROW(C2);1))
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 04.07.2022, 01:29
 
Ответить
Сообщение
не уверен, что в данной ситуации это достижимо

Оказалось, достижимо! Из раздумий над этой темой вынес простое эмпирическое правило достижимости/возможности создания единственной формулы массива для верхней ячейки диапазона, вычисляющей самостоятельные результаты для каждой ячейки ниже. Правило формулируется так: если решение задачи может быть сведено к использованию функции/-ций ВПР(VLOOKUP), то создание единственной формулы массива для верхней ячейки диапазона возможно. Эта возможность естественным образом следует из соответствующей "массивной" способности самой функции ВПР(VLOOKUP).

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

Поскольку единственная формула массива для второго случая (с неоднократным повторением фрагментов) предположительно будет выглядеть достаточно громоздкой в рамках данного обсуждения, ниже будет рассмотрен только первый случай - с двумя формулами. Имея полный расклад по двум формулам, далее желающие могут попробовать самостоятельно собрать единственную формулу-монстра для второго случая.

Итак, сначала построим первую формулу - "справочник". Для этого предварительно создадим в файле новый пустой рабочий лист "ДляВПР". Далее последовательно будем помещать в ячейку A1 этого листа формулы ниже, наблюдать за возвращаемыми каждый раз значениями и комментировать их.
[vba]
Код
=FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"")

2    Ввод
5    300
10    5000
16    800
22    8835
[/vba]
Как видно, только что приведенная формула возвращает непустые значения столбца C, которые вкупе с соответствующими номерами строк представляют собой достаточный набор данных для решения нашей задачи. Мои формулы выше в этой теме говорят о том, что можно взять любое число из колонки C (ниже заголовка "Ввод") и разделить его на разность номеров строк текущей и предыдущей строк. Для числа 300 текущей строкой будет 5, а предыдущей - 2. Неудобство заключается в том, что числа 5 и 2 находятся хоть и в соседних, но в разных строках. А для удобства наших вычислений хотелось бы их видеть в одной строке.

Ниже будут проделаны последовательные манипуляции формулами, приводящие наши данные к удобному виду. Для начала добавим к уже имеющимся данным третий столбец - последовательную нумерацию строк результата (не путать с номерами строк рабочего листа из первого столбца):
[vba]
Код
={FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))}

2    Ввод    1
5    300    2
10    5000    3
16    800    4
22    8835    5
[/vba]
Чтобы числа 2 и 5 оказались в одной строке "справочника для ВПР" (но в разных столбцах), нужно фактически продублировать первую колонку, после чего вертикально "сдвинуть" на одну строку первый дубль относительно второго. Для этого вычислим ниже два поднабора (подмножества) от исходного набора данных: первый - без последней строки, второй - без первой:
[vba]
Код
=FILTER({FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))};
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>"))

2    Ввод    1
5    300    2
10    5000    3
16    800    4
[/vba]
[vba]
Код
=FILTER({FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"") \
SEQUENCE(COUNTIF('Лист1'!C:C;"<>"))};
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)

5    300    2
10    5000    3
16    800    4
22    8835    5
[/vba]
Оба поднабора содержат одинаковое количество строк (на одну меньше общего набора). Это даёт нам возможность горизонтально "состыковать" оба поднабора, взяв из первого - только первую колонку, а из второго - первые две колонки:
[vba]
Код
={FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}

2    5    300
5    10    5000
10    16    800
16    22    8835
[/vba]
Получившийся новый набор данных и представляет собой "справочник для ВПР". Его колонки можно озаглавить как "Предыдущая строка", "Текущая строка" и "Текущая сумма" соответственно. Напомню, что последняя формула предназначена для ячейки A1 листа "ДляВПР".

Наконец, используя полученные выше результаты, построим вторую формулу массива (довольно простую) - для ячейки B3 листа "Лист1" (функция ARRAY_CONSTRAIN обеспечивает развертывание формулы ниже на нужное количество строк):
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;3) / (
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;2) -
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;1))
;MAX('ДляВПР'!B:B)-ROW(C2);1))
[/vba]

Автор - Gustav
Дата добавления - 04.07.2022 в 01:10
Gustav Дата: Вторник, 05.07.2022, 09:58 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2077
Репутация: 827 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Последнюю формулу можно немного сократить, заменив разность VLOOKUP в знаменателе на вызов функции MMULT:
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;3) /
MMULT(VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;{2\1});{1;-1})
;MAX('ДляВПР'!B:B)-ROW(C2);1))
[/vba]Не знаю, насколько уместно здесь слово "упростить", но количество вызовов VLOOKUP, тем не менее, уменьшается до двух, подогревая назойливое желание запихнуть-таки всё (т.е. и "справочник для ВПР" - теперь уже только 2 раза) в одну мега-формулу...


Мой tip box - яд 41001663842605
 
Ответить
СообщениеПоследнюю формулу можно немного сократить, заменив разность VLOOKUP в знаменателе на вызов функции MMULT:
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(
VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;3) /
MMULT(VLOOKUP(ROW(C3:C)-1;'ДляВПР'!A:C;{2\1});{1;-1})
;MAX('ДляВПР'!B:B)-ROW(C2);1))
[/vba]Не знаю, насколько уместно здесь слово "упростить", но количество вызовов VLOOKUP, тем не менее, уменьшается до двух, подогревая назойливое желание запихнуть-таки всё (т.е. и "справочник для ВПР" - теперь уже только 2 раза) в одну мега-формулу...

Автор - Gustav
Дата добавления - 05.07.2022 в 09:58
Gustav Дата: Вторник, 05.07.2022, 21:10 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2077
Репутация: 827 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
А вообще для кардинального избавления от вычислений во второй формуле можно стратегически перенести их в первую, используя мощь функции QUERY, которую применим "вокруг" имеющейся первой формулы, с одновременной оптимизацией "справочника для ВПР" - оставим в нём только первый столбец, по которому ищем, и вычисленный столбец окончательного результата (чтобы не тащить слагаемые по отдельности во вторую формулу):
[vba]
Код
=QUERY({FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}
;"select Col1, Col3/(Col2-Col1) label Col3/(Col2-Col1) ''")

2    100
5    1000
10    133,3333333
16    1472,5
[/vba]Опять напоминаю, что это была первая формула - для ячейки A1 листа "ДляВПР". Вторая же формула, которая предназначена для ячейки B3 листа "Лист1", после выполненной модификации первой - упрощается до безобразия:
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(VLOOKUP(ROW(C3:C)-1;
'ДляВПР'!A:B
;2);MAX(ROW(C3:C)*(C3:C<>""))-ROW(C2);1))
[/vba]Здесь для наглядности чуть изменено форматирование (см.дальше зачем), а также аргумент функции MAX представлен по-другому, чтобы избавиться от ссылки на лист "ДляВПР".

На этом можно и остановиться (если лист "ДляВПР" не мешает - его ведь можно сделать совсем маленьким, удалив свободные строки и столбцы, а сам лист скрыть). А можно, наконец, подставить первую формулу во вторую: благо, это нужно сделать только в одном ее месте, а именно вместо ссылки 'ДляВПР'!A:B :
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(VLOOKUP(ROW(C3:C)-1;
    QUERY({FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
    SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
    FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
    SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}
    ;"select Col1, Col3/(Col2-Col1) label Col3/(Col2-Col1) ''")
;2);MAX(ROW(C3:C)*(C3:C<>""))-ROW(C2);1))
[/vba]
После этого лист "ДляВПР" становится больше ненужным. А мы становимся непомерно гордыми от создания такой крутой мега-формулы - одна последовательность функций QUERY({FILTER(FILTER чего стоит! yes


Мой tip box - яд 41001663842605
 
Ответить
СообщениеА вообще для кардинального избавления от вычислений во второй формуле можно стратегически перенести их в первую, используя мощь функции QUERY, которую применим "вокруг" имеющейся первой формулы, с одновременной оптимизацией "справочника для ВПР" - оставим в нём только первый столбец, по которому ищем, и вычисленный столбец окончательного результата (чтобы не тащить слагаемые по отдельности во вторую формулу):
[vba]
Код
=QUERY({FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}
;"select Col1, Col3/(Col2-Col1) label Col3/(Col2-Col1) ''")

2    100
5    1000
10    133,3333333
16    1472,5
[/vba]Опять напоминаю, что это была первая формула - для ячейки A1 листа "ДляВПР". Вторая же формула, которая предназначена для ячейки B3 листа "Лист1", после выполненной модификации первой - упрощается до безобразия:
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(VLOOKUP(ROW(C3:C)-1;
'ДляВПР'!A:B
;2);MAX(ROW(C3:C)*(C3:C<>""))-ROW(C2);1))
[/vba]Здесь для наглядности чуть изменено форматирование (см.дальше зачем), а также аргумент функции MAX представлен по-другому, чтобы избавиться от ссылки на лист "ДляВПР".

На этом можно и остановиться (если лист "ДляВПР" не мешает - его ведь можно сделать совсем маленьким, удалив свободные строки и столбцы, а сам лист скрыть). А можно, наконец, подставить первую формулу во вторую: благо, это нужно сделать только в одном ее месте, а именно вместо ссылки 'ДляВПР'!A:B :
[vba]
Код
=ArrayFormula(ARRAY_CONSTRAIN(VLOOKUP(ROW(C3:C)-1;
    QUERY({FILTER(FILTER(ROW('Лист1'!C:C);'Лист1'!C:C<>"");
    SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) < COUNTIF('Лист1'!C:C;"<>")) \
    FILTER(FILTER({ROW('Лист1'!C:C)\'Лист1'!C:C};'Лист1'!C:C<>"");
    SEQUENCE(COUNTIF('Лист1'!C:C;"<>")) > 1)}
    ;"select Col1, Col3/(Col2-Col1) label Col3/(Col2-Col1) ''")
;2);MAX(ROW(C3:C)*(C3:C<>""))-ROW(C2);1))
[/vba]
После этого лист "ДляВПР" становится больше ненужным. А мы становимся непомерно гордыми от создания такой крутой мега-формулы - одна последовательность функций QUERY({FILTER(FILTER чего стоит! yes

Автор - Gustav
Дата добавления - 05.07.2022 в 21:10
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Поиск последней непустой ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!