Формула автоматически срабатывала на весь столбец

Дата: Четверг, 21.06.2018, 18:06 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Как сделать что бы формула, написанная в верхней ячейке столбца автоматически срабатывала на весь столбец, если есть данные формулы? Формула простейшая Разность А1 и B1. Как написать эту разность формулой массива, не протягивая каждый раз до низа стлбца

Сообщение отредактировал BcjPrj - Четверг, 21.06.2018, 18:08

Дата: Четверг, 21.06.2018, 19:59 | Сообщение № 2

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Примерно так, например, в ячейке C1:
[vba]

Код

=ArrayFormula(
A1:INDEX(A:A; MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))
-
B1:INDEX(B:B; MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))
)

[/vba]
И обычно имеет смысл "расширительный" радикал поместить в отдельную ячейку и использовать ссылку на нее. Например, загоним его куда-нибудь вправо - в ячейку Z1 и не забудем также сделать его формулой массива:
[vba]

Код

=ArrayFormula(MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))

[/vba]
Тогда первая формулка становится куда компактнее!:
[vba]

Код

=ArrayFormula(
A1:INDEX(A:A; Z1)
-
B1:INDEX(B:B; Z1)
)

[/vba]

Если же вам не требуется отслеживать последнюю занятую ячейку (строку) в колонках A и B, то формула совсем простая - тупо вниз, до физического конца листа:
[vba]

Код

=ArrayFormula(A:A-B:B)

[/vba]

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 21.06.2018, 20:06

Дата: Понедельник, 25.06.2018, 12:32 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Последний вариант подошел - благодарю. А первый - так и не разобрался, где что.

Дата: Понедельник, 25.06.2018, 14:10 | Сообщение № 4

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата BcjPrj, 25.06.2018 в 12:32, в сообщении № 3 ()

А первый - так и не разобрался, где что.

Ну, разберетесь еще. Когда захотите, чтобы формула следовала за заполнением, например, столбца A. Скажем, заполнены подряд ячейки A1:A10, и результат расчета отображается в ячейках C1:C10. Заполнили следующую A11, и результат в C11 - уже тут как тут! Хотя по-прежнему введена только одна-единственная формула массива в ячейку C1.

Дата: Понедельник, 25.06.2018, 23:46 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Совсем не понятно как это получается... но получается. МИСТИКА!!!)))

Все таки решил разобраться...

=ArrayFormula(MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))

Объясните пожалуста что такое в этой формуле единица и *. И зачем искать максимальное значение (max)?

Сообщение отредактировал BcjPrj - Вторник, 26.06.2018, 00:02

Дата: Вторник, 26.06.2018, 01:22 | Сообщение № 6

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Я попробовал наглядно разобрать формулы по частям. Вот что из этого получилось:
https://docs.google.com/spreads....sharing

СпросИте, если после этого что-то еще будет непонятно.

Звездочка (*) - это, естественно, умножение. Единица (1) нужна, чтобы не уйти в 0 при абсолютно пустых колонках A и B. Вычисляется ведь номер строки (МАКСИМАЛЬНЫЙ - по самой нижней заполненной ячейке в колонках A или B), а строки с номером 0 не бывает.

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Вторник, 26.06.2018, 01:30

Дата: Вторник, 26.06.2018, 20:46 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Отдельно - разобрался. Все в таблице очень наглядно.
Пытаюсь совместить 2 формулы (что бы получить формулу массива записанного в верхней ячейке столбца). Не выходит самому.

Код

=СУММЕСЛИ('Лист1'!A3:A;A3;'Лист1'!D:D)

Код

=ArrayFormula(МАКС(1;СТРОКА(A3:A)*НЕ(ЕПУСТО(A3:A))))

написанную в отдельной ячейке B1.
В справке googl в примерах есть такая формула:

Код

ARRAYFORMULA(СУММ(ЕСЛИ(A1:A10>5; A1:A10; 0)))

. я ее переписал, но как-то не особо получилось...

Код

=ARRAYFORMULA(СУММ(ЕСЛИ('Лист1'!A3:A=A3; 'Лист1'!D:D; B1)));

Сообщение отредактировал BcjPrj - Вторник, 26.06.2018, 22:21

Manyasha

Дата: Вторник, 26.06.2018, 21:49 | Сообщение № 8

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 901 ±

Замечаний: 0% ±

Excel 2010, 2016

BcjPrj, Оформляйте формулы тегами - кнопка fx в режиме правки поста. Или можно тегом для кода оформить (кнопка #), если "формульные" косячат.

ЯД: 410013299366744 WM: R193491431804

Дата: Вторник, 26.06.2018, 23:07 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Код

=ArrayFormula(
A1:INDEX(A:A; MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))
-
B1:INDEX(B:B; MAX(1;ROW(A:B)*NOT(ISBLANK(A:B))))
)

Как преобразовать эту формулу, что бы ячейка оставалась пустой, если в строке нет значения А или В? Сейчас формула работает так, что если в одной из ячеек (А или B ) значения нет, это воспринимается формулой как ноль. А надо что бы она не могла сработать в этой строке совсем...

Это мой вариант

Код

=ЕСЛИ(И(A1="*";B1="*");ArrayFormula(A1 :ИНДЕКС(A:A; Z1)
-B1 :ИНДЕКС(B:B; Z1)
);0)

К сожалению - снова ниче не работает

Сообщение отредактировал BcjPrj - Вторник, 26.06.2018, 23:48

Дата: Среда, 27.06.2018, 12:20 | Сообщение № 10

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

BcjPrj, катастрофически нужен пример от Вас! Создайте таблицу, дайте общий доступ (хотя бы на просмотр), напишите свои простые формулы для каждой строки, чтобы было понятно, ЧТО именно Вам нужно получить. А дальше посмотрим, возможно ли это в принципе сделать через формулу массива.

Теперь теоретическая часть. Вероятно, немного расстрою вас, но дело в том, что НЕ ВСЕ абсолютно обычные формулы могут быть переделаны в формулы массива. Далеко не все! Не все функции умеют возвращать массивы. Типичный пример - функция SUM, которая всегда возвращает единственное значение общей суммы всех аргументов. Покажу на примере.

Допустим у вас есть формула:

Код

=SUM(A1;B1;C1)

И вы хотели бы ее сделать формулой массива примерно так, рассчитывая после ввода получить 10 построчных значений сумм:

Код

=ArrayFormula(SUM(A1:A10;B1:B10;C1:C10))

Но - увы! - получаете только одно-единственное значение общей суммы всех десяти строк (30 ячеек). Что с массивом, что без массива - результат будет точно такой же, как если бы вы ввели сразу формулу на весь диапазон:

Код

=SUM(A1:C10)

Что же делать? Искать обходные пути, придумывать альтернативные решения, советоваться с Сообществом (благо им за годы накоплен большой опыт поиска альтернатив). Так в случае с SUM, например, спасает использование обычного знака "+", и следующая формула уже оправдывает ожидания, возвращая 10 построчных сумм:

Код

=ArrayFormula(A1:A10+B1:B10+C1:C10)

Код

=SUM(A1;B1;C1)

Код

=ArrayFormula(SUM(A1:A10;B1:B10;C1:C10))

Код

=SUM(A1:C10)

Код

=ArrayFormula(A1:A10+B1:B10+C1:C10)

СообщениеBcjPrj, катастрофически нужен пример от Вас! Создайте таблицу, дайте общий доступ (хотя бы на просмотр), напишите свои простые формулы для каждой строки, чтобы было понятно, ЧТО именно Вам нужно получить. А дальше посмотрим, возможно ли это в принципе сделать через формулу массива.

Теперь теоретическая часть. Вероятно, немного расстрою вас, но дело в том, что НЕ ВСЕ абсолютно обычные формулы могут быть переделаны в формулы массива. Далеко не все! Не все функции умеют возвращать массивы. Типичный пример - функция SUM, которая всегда возвращает единственное значение общей суммы всех аргументов. Покажу на примере.

Допустим у вас есть формула:

Код

=SUM(A1;B1;C1)

Код

=ArrayFormula(SUM(A1:A10;B1:B10;C1:C10))

Код

=SUM(A1:C10)

Код

=ArrayFormula(A1:A10+B1:B10+C1:C10)

Автор - Gustav
Дата добавления - 27.06.2018 в 12:20

Дата: Среда, 27.06.2018, 19:17 | Сообщение № 11

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата BcjPrj, 26.06.2018 в 23:07, в сообщении № 9 ()

Как преобразовать эту формулу, что бы ячейка оставалась пустой, если в строке нет значения А или В?

Эту задачу решает такая формула:
[vba]

Код

=ArrayFormula(
IF( (ISBLANK(A1:INDEX(A:A;Z1))+ISBLANK(B1:INDEX(B:B;Z1)))>0;
"";
A1:INDEX(A:A;Z1)-B1:INDEX(B:B;Z1)))

[/vba]

Или даже такая - без явной проверки на 0, поскольку в выражении условия для IF любое число, отличное от 0, считается TRUE, и, соответственно, 0 считается FALSE:
[vba]

Код

=ArrayFormula(
IF( ISBLANK(A1:INDEX(A:A;Z1))+ISBLANK(B1:INDEX(B:B;Z1));
"";
A1:INDEX(A:A;Z1)-B1:INDEX(B:B;Z1)))

[/vba]

А вот явное указание функции OR (вместо "хакерского" использования "+", как в формулах выше) задачу не решает, поскольку функция OR возвращает одно-единственное значение, вычисленное по списку своих аргументов (т.е. как и рассмотренная выше функция SUM):
[vba]

Код

=ArrayFormula(
IF( OR(ISBLANK(A1:INDEX(A:A;Z1));ISBLANK(B1:INDEX(B:B;Z1)));
"";
A1:INDEX(A:A;Z1)-B1:INDEX(B:B;Z1)))

[/vba]

Дата: Среда, 27.06.2018, 19:25 | Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

https://docs.google.com/spreads....sharing

Посмотрите пожалуйста. На листе МОДЕЛЬ надо из обычных формул сделать формулы массива (ячейки залиты яркими цветами). На листе ПРОИЗВОДСТВО изучаю этот вопрос:
Как преобразовать эту формулу, что бы ячейка оставалась пустой, если в строке нет значения А или В? Сейчас формула работает так, что если в одной из ячеек (А или B ) значения нет, это воспринимается формулой как ноль. А надо что бы она не могла сработать в этой строке совсем...

Дата: Четверг, 28.06.2018, 11:56 | Сообщение № 13

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата BcjPrj, 27.06.2018 в 19:25, в сообщении № 12 ()

На листе МОДЕЛЬ надо из обычных формул сделать формулы массива

Ну, начнём разбираться...

Функция SUMIF (т.е. "одинарная" СУММЕСЛИ) нормально превращается в формулу массива (в отличии от "множественной" версии SUMIFS/СУММЕСЛИМН, которую не удается превратить в формулу массива - лично мне, во всяком случае, не удалось).

В качестве примера возьмем исходную формулу в ячейке B3:
[vba]

Код

=SUMIF('Производство'!A:A;A3;'Производство'!D:D)

[/vba]

и превратим ее в формулу массива:
[vba]

Код

=ArrayFormula(SUMIF('Производство'!A:A;"="&A3:INDEX(A:A;7);'Производство'!D:D))

[/vba]

Я специально добавил знак равенства в условие ("="&A3...) - для большей наглядности и для напоминания о том, что на его месте может быть любой условный оператор, например, неравенство:
[vba]

Код

=ArrayFormula(SUMIF('Производство'!A:A;"<>"&A3:A7;'Производство'!D:D))

[/vba]

Аналогичным образом можно сделать массивные SUMIF и в ячейке BA3 (сами-сами!)

Встречающиеся еще на листе "Модель" множественные функции COUNTIFS и SUMIFS подобным образом "омассивить" не удаётся, но возможны альтернативные массивные решения, о которых чуть позже.

Дата: Четверг, 28.06.2018, 13:39 | Сообщение № 14

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Если СУММЕСЛИМН формулой массива сделать нет возможности - то и остальные делать нет необходимости - все равно строку вручную протягивать каждый раз. В любом случае благодарю и жду альтернативный вариант.

Дата: Четверг, 28.06.2018, 14:43 | Сообщение № 15

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Продолжаем. И сейчас будет высший пилотаж. Не мой. Я лишь где-то это подсмотрел (в т.ч. и на нашем Форуме есть виртуозы) и иногда, если получается, применяю по шпаргалке (типа "читаю и перевожу со словарем"). В данном случае, вроде, получается.

Итак, встречаем функцию MMULT (МУМНОЖ). Следующая формула (разумеется, массивная):
[vba]

Код

=ArrayFormula(MMULT(
TRANSPOSE(--('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
))

[/vba]делает то же самое, что и уже рассмотренная формула с SUMIF для ячейки B3:
[vba]

Код

=ArrayFormula(SUMIF('Производство'!$A:$A; $A3:$A7; 'Производство'!$D:$D))

[/vba]

А вот следующая, чуть более сложная за счет дополнительного условия (...=X2), формула для ячейки X3:
[vba]

Код

=ArrayFormula(MMULT(
TRANSPOSE(('Производство'!$C:$C=X2)*('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
)-C3:C7)

[/vba]делает то же самое, что делала БЫ (если бы могла быть массивной) следующая формула с множественным SUMIFS:
[vba]

Код

=SUMIFS('Производство'!$D:$D; 'Производство'!$A:$A; $A3:$A7; 'Производство'!$C:$C; X2) - C3:C7

[/vba]

Разумеется, формула с MMULT может быть протянута из ячейки X3 в соседние правые ячейки в той же 3-й строке.

Вот как-то так. Больше, чем рассказал, ещё вряд ли смогу. Если не побоитесь применять такие формулы-монстры, то рекомендую плотно с ними повозиться, поэкспериментировать, почитать хелп, погуглить.

Итого это мы рассмотрели альтернативную и, самое главное, МАССИВНУЮ замену для SUMIFS. Чуть позже рассмотрим еще и для COUNTIFS. Подход будет практически тот же - с функцией MMULT, но с небольшим различием.

Код

=ArrayFormula(MMULT(
TRANSPOSE(--('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
))

[/vba]делает то же самое, что и уже рассмотренная формула с SUMIF для ячейки B3:
[vba]

Код

=ArrayFormula(SUMIF('Производство'!$A:$A; $A3:$A7; 'Производство'!$D:$D))

[/vba]

А вот следующая, чуть более сложная за счет дополнительного условия (...=X2), формула для ячейки X3:
[vba]

Код

=ArrayFormula(MMULT(
TRANSPOSE(('Производство'!$C:$C=X2)*('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
)-C3:C7)

[/vba]делает то же самое, что делала БЫ (если бы могла быть массивной) следующая формула с множественным SUMIFS:
[vba]

Код

=SUMIFS('Производство'!$D:$D; 'Производство'!$A:$A; $A3:$A7; 'Производство'!$C:$C; X2) - C3:C7

СообщениеПродолжаем. И сейчас будет высший пилотаж. Не мой. Я лишь где-то это подсмотрел (в т.ч. и на нашем Форуме есть виртуозы) и иногда, если получается, применяю по шпаргалке (типа "читаю и перевожу со словарем"). В данном случае, вроде, получается.

Итак, встречаем функцию MMULT (МУМНОЖ). Следующая формула (разумеется, массивная):
[vba]

Код

=ArrayFormula(MMULT(
TRANSPOSE(--('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
))

[/vba]делает то же самое, что и уже рассмотренная формула с SUMIF для ячейки B3:
[vba]

Код

=ArrayFormula(SUMIF('Производство'!$A:$A; $A3:$A7; 'Производство'!$D:$D))

[/vba]

А вот следующая, чуть более сложная за счет дополнительного условия (...=X2), формула для ячейки X3:
[vba]

Код

=ArrayFormula(MMULT(
TRANSPOSE(('Производство'!$C:$C=X2)*('Производство'!$A:$A=TRANSPOSE($A3:$A7)));
N('Производство'!$D:$D)
)-C3:C7)

[/vba]делает то же самое, что делала БЫ (если бы могла быть массивной) следующая формула с множественным SUMIFS:
[vba]

Код

=SUMIFS('Производство'!$D:$D; 'Производство'!$A:$A; $A3:$A7; 'Производство'!$C:$C; X2) - C3:C7

Автор - Gustav
Дата добавления - 28.06.2018 в 14:43

Дата: Четверг, 28.06.2018, 18:57 | Сообщение № 16

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

По ходу дум родилась очень неплохая, как мне кажется, альтернатива элементарным построчным суммам, которые в столбце W вот такие, начиная с ячейки W3 и ниже:
[vba]

Код

=SUM(L3:V3)
=SUM(L4:V4)
=SUM(L5:V5)
=SUM(L6:V6)
=SUM(L7:V7)

[/vba]
Как было показано выше, никакие попытки массивных манипуляций с диапазоном не приводят к "построчности" и следующая формула возвращает только одно значение общей суммы:
[vba]

Код

=ArrayFormula(SUM(L3:V7))

[/vba]

Но есть функция SUMIF ("одинарная"), с помощью которой удалось получить следующее массивное решение для ячейки W3:
[vba]

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:V7);IF(COLUMN(L3:V7);ROW(L3:V7)));
ROW(L3:V7);
L3:V7))

[/vba]И хоть диапазон L3:V7 теперь используется аж пять раз вместо одного - оно того стОит!

Очевидно, что для полной саморасширяемости формулы вниз нужно будет сделать саморасширяющиеся ссылки через INDEX:
[vba]

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:INDEX(V:V;7));IF(COLUMN(L3:INDEX(V:V;7));ROW(L3:INDEX(V:V;7))));
ROW(L3:INDEX(V:V;7));
L3:INDEX(V:V;7)))

[/vba]
И совсем уж окончательно еще потом заменить константу 7 на специально предусмотренную ячейку хранения номера максимальной строки (например, Z1, как в обсуждении выше).

Это всё было для ячейки W3. В ячейке AQ3 - аналогичная сумма, можете попрактиковаться самостоятельно. И за мной немного позже еще заключительный рассказ про COUNTIFS.

Код

=SUM(L3:V3)
=SUM(L4:V4)
=SUM(L5:V5)
=SUM(L6:V6)
=SUM(L7:V7)

Код

=ArrayFormula(SUM(L3:V7))

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:V7);IF(COLUMN(L3:V7);ROW(L3:V7)));
ROW(L3:V7);
L3:V7))

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:INDEX(V:V;7));IF(COLUMN(L3:INDEX(V:V;7));ROW(L3:INDEX(V:V;7))));
ROW(L3:INDEX(V:V;7));
L3:INDEX(V:V;7)))

СообщениеПо ходу дум родилась очень неплохая, как мне кажется, альтернатива элементарным построчным суммам, которые в столбце W вот такие, начиная с ячейки W3 и ниже:
[vba]

Код

=SUM(L3:V3)
=SUM(L4:V4)
=SUM(L5:V5)
=SUM(L6:V6)
=SUM(L7:V7)

Код

=ArrayFormula(SUM(L3:V7))

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:V7);IF(COLUMN(L3:V7);ROW(L3:V7)));
ROW(L3:V7);
L3:V7))

Код

=ArrayFormula(SUMIF(
IF(ROW(L3:INDEX(V:V;7));IF(COLUMN(L3:INDEX(V:V;7));ROW(L3:INDEX(V:V;7))));
ROW(L3:INDEX(V:V;7));
L3:INDEX(V:V;7)))

Автор - Gustav
Дата добавления - 28.06.2018 в 18:57

Дата: Четверг, 28.06.2018, 20:29 | Сообщение № 17

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

Дата: Пятница, 29.06.2018, 00:09 | Сообщение № 18

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Код

=ArrayFormula(
IF( ISBLANK(A1:INDEX(A:A;Z1))+ISBLANK(B1:INDEX(B:B;Z1));
"";
A1:INDEX(A:A;Z1)-B1:INDEX(B:B;Z1)))

А что такое в этой формуле Z1? Я думал это ячейка в которой мы пишем формулу-ссылку... (на мой пример не ссылаться - я, видимо, был не в адеквате)

Мы эту формулу меняем?

Код

=ArrayFormula(A1:ИНДЕКС(A:A; МАКС(1;СТРОКА(A:B)*НЕ(ЕПУСТО(A:B))))-B1:ИНДЕКС(B:B; МАКС(1;СТРОКА(A:B)*НЕ(ЕПУСТО(A:B)))))

или где?

Дата: Пятница, 29.06.2018, 06:35 | Сообщение № 19

Группа: Админы

Ранг: Участник клуба

Сообщений: 2809

Репутация: 1184 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

А что такое в этой формуле Z1? Я думал это ячейка в которой мы пишем формулу-ссылку...

"Наша песня хороша, начинай сначала!" См. еще раз сообщение №2. Это наугад взятая ячейка, в которой мы вычисляем номер самой последней заполненной строки листа, определенной по выбранным колонкам (A:B). Т.е. максимальный номер строки. Делаем мы это для того, чтобы не таскать из формулы в формулу довольно внушительный радикал вида:
[vba]

Код

MAX(1;ROW(A:B)*NOT(ISBLANK(A:B)))

[/vba]

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

Моё "правее" выше (в сообщении №2) выразилось в назначение на эту роль ячейки Z1. Вы можете выбрать любую другую, хоть на другом листе. В Excel бы на роль такой ячейки хорошо подошла бы именованная формула, созданная через механизм имен (по Ctrl+F3). В Таблицах Google пока такого механизма нет, поэтому и приходится задействовать ячейку на листе.

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

Мы эту формулу меняем?

Эту-эту.

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

А что такое в этой формуле Z1? Я думал это ячейка в которой мы пишем формулу-ссылку...

Код

MAX(1;ROW(A:B)*NOT(ISBLANK(A:B)))

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

Мы эту формулу меняем?

Эту-эту.Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 29.06.2018, 06:46

Сообщение

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

А что такое в этой формуле Z1? Я думал это ячейка в которой мы пишем формулу-ссылку...

Код

MAX(1;ROW(A:B)*NOT(ISBLANK(A:B)))

Цитата BcjPrj, 29.06.2018 в 00:09, в сообщении № 18 ()

Мы эту формулу меняем?

Эту-эту.

Автор - Gustav
Дата добавления - 29.06.2018 в 06:35

Дата: Пятница, 29.06.2018, 13:03 | Сообщение № 20

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

Сообщение отредактировал BcjPrj - Пятница, 29.06.2018, 14:37