Всем привет! файл с примером: https://docs.google.com/spreads....sharing Суть такая: в столбец С вводится число, а в столбце D рассчитывается количество пустых ячеек до верхней непустой ячейки в столбце С. Для чего: чтобы потом введенное число в столбец С поделить на количество пустых строк (рассчитанное в столбце D) и ввести это поделенное число во все пустые строки.
Не могу подсчитать количество пустых строк до верхней записи.
Быть может, кто-то предложит более простой вариант заполнения? либо поможет высчитать пустые строки сверху Блгодарю!
Всем привет! файл с примером: https://docs.google.com/spreads....sharing Суть такая: в столбец С вводится число, а в столбце D рассчитывается количество пустых ячеек до верхней непустой ячейки в столбце С. Для чего: чтобы потом введенное число в столбец С поделить на количество пустых строк (рассчитанное в столбце D) и ввести это поделенное число во все пустые строки.
Не могу подсчитать количество пустых строк до верхней записи.
Быть может, кто-то предложит более простой вариант заполнения? либо поможет высчитать пустые строки сверху Блгодарю!zavik121
Посмотрел задание и таблицу... и точек соприкосновения обнаружил немного... Если вам, как мне показалось, нужна формула для столбца B, дающая такие же результаты, что и в столбце G, то вот формула для ячейки B3 (и дальнейшего протягивания вниз):[vba]
[/vba]т.е. сумма блока из колонки C делится на разность номеров строк: последняя строка текущего блока - последняя строка предыдущего блока.
Как видно, я использовал свою любимую гугловскую функцию FILTER. Вполне возможно, что матерые экселисты, не использующую эту функцию, предложат другое решение (на функциях ПОИСКПОЗ или ВПР).
Посмотрел задание и таблицу... и точек соприкосновения обнаружил немного... Если вам, как мне показалось, нужна формула для столбца B, дающая такие же результаты, что и в столбце G, то вот формула для ячейки B3 (и дальнейшего протягивания вниз):[vba]
[/vba]т.е. сумма блока из колонки C делится на разность номеров строк: последняя строка текущего блока - последняя строка предыдущего блока.
Как видно, я использовал свою любимую гугловскую функцию FILTER. Вполне возможно, что матерые экселисты, не использующую эту функцию, предложат другое решение (на функциях ПОИСКПОЗ или ВПР).
Хмм... это для чего оно у Вас не подходит? Все три формулы, которые я привел в сообщении №2, проверены и работоспособны. Они предназначены для помещения в ячейку B3 и затем копирования вниз. При этом в каждой ячейке ниже будет содержаться своя копия этой формулы и, соответственно, свой результат вычисления по своей копии формулы.
Если же Вы пытаетесь сочинить формулу массива, которую можно поместить в одном экземпляре только в самую верхнюю ячейку диапазона - B3, и чтобы при этом ячейки ниже содержали собственные результаты вычислений по этой единственной формуле, то не уверен, что в данной ситуации это достижимо, так как формула содержит вертикальные вычисления по столбцу C. Во всяком случае, у меня не получилось создать такую формулу...
Хмм... это для чего оно у Вас не подходит? Все три формулы, которые я привел в сообщении №2, проверены и работоспособны. Они предназначены для помещения в ячейку B3 и затем копирования вниз. При этом в каждой ячейке ниже будет содержаться своя копия этой формулы и, соответственно, свой результат вычисления по своей копии формулы.
Если же Вы пытаетесь сочинить формулу массива, которую можно поместить в одном экземпляре только в самую верхнюю ячейку диапазона - B3, и чтобы при этом ячейки ниже содержали собственные результаты вычислений по этой единственной формуле, то не уверен, что в данной ситуации это достижимо, так как формула содержит вертикальные вычисления по столбцу C. Во всяком случае, у меня не получилось создать такую формулу...Gustav
Оказалось, достижимо! Из раздумий над этой темой вынес простое эмпирическое правило достижимости/возможности создания единственной формулы массива для верхней ячейки диапазона, вычисляющей самостоятельные результаты для каждой ячейки ниже. Правило формулируется так: если решение задачи может быть сведено к использованию функции/-ций ВПР(VLOOKUP), то создание единственной формулы массива для верхней ячейки диапазона возможно. Эта возможность естественным образом следует из соответствующей "массивной" способности самой функции ВПР(VLOOKUP).
При этом решение можно оформить как в виде двух формул массива, первая из которых создаст в выделенном диапазоне "справочник для ВПР", а вторая затем обратится к этому "справочнику" один или несколько раз; так и в виде одной формулы массива, которая будет включать в себя (возможно, несколько раз) одни и те же динамические фрагменты формулы массива для "справочника ВПР" из первого случая.
Поскольку единственная формула массива для второго случая (с неоднократным повторением фрагментов) предположительно будет выглядеть достаточно громоздкой в рамках данного обсуждения, ниже будет рассмотрен только первый случай - с двумя формулами. Имея полный расклад по двум формулам, далее желающие могут попробовать самостоятельно собрать единственную формулу-монстра для второго случая.
Итак, сначала построим первую формулу - "справочник". Для этого предварительно создадим в файле новый пустой рабочий лист "ДляВПР". Далее последовательно будем помещать в ячейку A1 этого листа формулы ниже, наблюдать за возвращаемыми каждый раз значениями и комментировать их. [vba]
[/vba] Как видно, только что приведенная формула возвращает непустые значения столбца C, которые вкупе с соответствующими номерами строк представляют собой достаточный набор данных для решения нашей задачи. Мои формулы выше в этой теме говорят о том, что можно взять любое число из колонки C (ниже заголовка "Ввод") и разделить его на разность номеров строк текущей и предыдущей строк. Для числа 300 текущей строкой будет 5, а предыдущей - 2. Неудобство заключается в том, что числа 5 и 2 находятся хоть и в соседних, но в разных строках. А для удобства наших вычислений хотелось бы их видеть в одной строке.
Ниже будут проделаны последовательные манипуляции формулами, приводящие наши данные к удобному виду. Для начала добавим к уже имеющимся данным третий столбец - последовательную нумерацию строк результата (не путать с номерами строк рабочего листа из первого столбца): [vba]
[/vba] Чтобы числа 2 и 5 оказались в одной строке "справочника для ВПР" (но в разных столбцах), нужно фактически продублировать первую колонку, после чего вертикально "сдвинуть" на одну строку первый дубль относительно второго. Для этого вычислим ниже два поднабора (подмножества) от исходного набора данных: первый - без последней строки, второй - без первой: [vba]
[/vba] Оба поднабора содержат одинаковое количество строк (на одну меньше общего набора). Это даёт нам возможность горизонтально "состыковать" оба поднабора, взяв из первого - только первую колонку, а из второго - первые две колонки: [vba]
[/vba] Получившийся новый набор данных и представляет собой "справочник для ВПР". Его колонки можно озаглавить как "Предыдущая строка", "Текущая строка" и "Текущая сумма" соответственно. Напомню, что последняя формула предназначена для ячейки A1 листа "ДляВПР".
Наконец, используя полученные выше результаты, построим вторую формулу массива (довольно простую) - для ячейки B3 листа "Лист1" (функция ARRAY_CONSTRAIN обеспечивает развертывание формулы ниже на нужное количество строк): [vba]
Оказалось, достижимо! Из раздумий над этой темой вынес простое эмпирическое правило достижимости/возможности создания единственной формулы массива для верхней ячейки диапазона, вычисляющей самостоятельные результаты для каждой ячейки ниже. Правило формулируется так: если решение задачи может быть сведено к использованию функции/-ций ВПР(VLOOKUP), то создание единственной формулы массива для верхней ячейки диапазона возможно. Эта возможность естественным образом следует из соответствующей "массивной" способности самой функции ВПР(VLOOKUP).
При этом решение можно оформить как в виде двух формул массива, первая из которых создаст в выделенном диапазоне "справочник для ВПР", а вторая затем обратится к этому "справочнику" один или несколько раз; так и в виде одной формулы массива, которая будет включать в себя (возможно, несколько раз) одни и те же динамические фрагменты формулы массива для "справочника ВПР" из первого случая.
Поскольку единственная формула массива для второго случая (с неоднократным повторением фрагментов) предположительно будет выглядеть достаточно громоздкой в рамках данного обсуждения, ниже будет рассмотрен только первый случай - с двумя формулами. Имея полный расклад по двум формулам, далее желающие могут попробовать самостоятельно собрать единственную формулу-монстра для второго случая.
Итак, сначала построим первую формулу - "справочник". Для этого предварительно создадим в файле новый пустой рабочий лист "ДляВПР". Далее последовательно будем помещать в ячейку A1 этого листа формулы ниже, наблюдать за возвращаемыми каждый раз значениями и комментировать их. [vba]
[/vba] Как видно, только что приведенная формула возвращает непустые значения столбца C, которые вкупе с соответствующими номерами строк представляют собой достаточный набор данных для решения нашей задачи. Мои формулы выше в этой теме говорят о том, что можно взять любое число из колонки C (ниже заголовка "Ввод") и разделить его на разность номеров строк текущей и предыдущей строк. Для числа 300 текущей строкой будет 5, а предыдущей - 2. Неудобство заключается в том, что числа 5 и 2 находятся хоть и в соседних, но в разных строках. А для удобства наших вычислений хотелось бы их видеть в одной строке.
Ниже будут проделаны последовательные манипуляции формулами, приводящие наши данные к удобному виду. Для начала добавим к уже имеющимся данным третий столбец - последовательную нумерацию строк результата (не путать с номерами строк рабочего листа из первого столбца): [vba]
[/vba] Чтобы числа 2 и 5 оказались в одной строке "справочника для ВПР" (но в разных столбцах), нужно фактически продублировать первую колонку, после чего вертикально "сдвинуть" на одну строку первый дубль относительно второго. Для этого вычислим ниже два поднабора (подмножества) от исходного набора данных: первый - без последней строки, второй - без первой: [vba]
[/vba] Оба поднабора содержат одинаковое количество строк (на одну меньше общего набора). Это даёт нам возможность горизонтально "состыковать" оба поднабора, взяв из первого - только первую колонку, а из второго - первые две колонки: [vba]
[/vba] Получившийся новый набор данных и представляет собой "справочник для ВПР". Его колонки можно озаглавить как "Предыдущая строка", "Текущая строка" и "Текущая сумма" соответственно. Напомню, что последняя формула предназначена для ячейки A1 листа "ДляВПР".
Наконец, используя полученные выше результаты, построим вторую формулу массива (довольно простую) - для ячейки B3 листа "Лист1" (функция ARRAY_CONSTRAIN обеспечивает развертывание формулы ниже на нужное количество строк): [vba]
[/vba]Не знаю, насколько уместно здесь слово "упростить", но количество вызовов VLOOKUP, тем не менее, уменьшается до двух, подогревая назойливое желание запихнуть-таки всё (т.е. и "справочник для ВПР" - теперь уже только 2 раза) в одну мега-формулу...
Последнюю формулу можно немного сократить, заменив разность VLOOKUP в знаменателе на вызов функции MMULT: [vba]
[/vba]Не знаю, насколько уместно здесь слово "упростить", но количество вызовов VLOOKUP, тем не менее, уменьшается до двух, подогревая назойливое желание запихнуть-таки всё (т.е. и "справочник для ВПР" - теперь уже только 2 раза) в одну мега-формулу...Gustav
А вообще для кардинального избавления от вычислений во второй формуле можно стратегически перенести их в первую, используя мощь функции QUERY, которую применим "вокруг" имеющейся первой формулы, с одновременной оптимизацией "справочника для ВПР" - оставим в нём только первый столбец, по которому ищем, и вычисленный столбец окончательного результата (чтобы не тащить слагаемые по отдельности во вторую формулу): [vba]
[/vba]Опять напоминаю, что это была первая формула - для ячейки A1 листа "ДляВПР". Вторая же формула, которая предназначена для ячейки B3 листа "Лист1", после выполненной модификации первой - упрощается до безобразия: [vba]
[/vba]Здесь для наглядности чуть изменено форматирование (см.дальше зачем), а также аргумент функции MAX представлен по-другому, чтобы избавиться от ссылки на лист "ДляВПР".
На этом можно и остановиться (если лист "ДляВПР" не мешает - его ведь можно сделать совсем маленьким, удалив свободные строки и столбцы, а сам лист скрыть). А можно, наконец, подставить первую формулу во вторую: благо, это нужно сделать только в одном ее месте, а именно вместо ссылки 'ДляВПР'!A:B : [vba]
[/vba] После этого лист "ДляВПР" становится больше ненужным. А мы становимся непомерно гордыми от создания такой крутой мега-формулы - одна последовательность функций QUERY({FILTER(FILTER чего стоит!
А вообще для кардинального избавления от вычислений во второй формуле можно стратегически перенести их в первую, используя мощь функции QUERY, которую применим "вокруг" имеющейся первой формулы, с одновременной оптимизацией "справочника для ВПР" - оставим в нём только первый столбец, по которому ищем, и вычисленный столбец окончательного результата (чтобы не тащить слагаемые по отдельности во вторую формулу): [vba]
[/vba]Опять напоминаю, что это была первая формула - для ячейки A1 листа "ДляВПР". Вторая же формула, которая предназначена для ячейки B3 листа "Лист1", после выполненной модификации первой - упрощается до безобразия: [vba]
[/vba]Здесь для наглядности чуть изменено форматирование (см.дальше зачем), а также аргумент функции MAX представлен по-другому, чтобы избавиться от ссылки на лист "ДляВПР".
На этом можно и остановиться (если лист "ДляВПР" не мешает - его ведь можно сделать совсем маленьким, удалив свободные строки и столбцы, а сам лист скрыть). А можно, наконец, подставить первую формулу во вторую: благо, это нужно сделать только в одном ее месте, а именно вместо ссылки 'ДляВПР'!A:B : [vba]
[/vba] После этого лист "ДляВПР" становится больше ненужным. А мы становимся непомерно гордыми от создания такой крутой мега-формулы - одна последовательность функций QUERY({FILTER(FILTER чего стоит! Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
