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

Вход

Регистрация

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

 

= Мир MS Excel/Формула автоматически срабатывала на весь столбец - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Формула автоматически срабатывала на весь столбец (Формулы/Formulas)
Формула автоматически срабатывала на весь столбец
BcjPrj Дата: Четверг, 21.06.2018, 18:06 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал BcjPrj - Четверг, 21.06.2018, 18:08
 
Ответить
СообщениеКак сделать что бы формула, написанная в верхней ячейке столбца автоматически срабатывала на весь столбец, если есть данные формулы? Формула простейшая Разность А1 и B1. Как написать эту разность формулой массива, не протягивая каждый раз до низа стлбца

Автор - BcjPrj
Дата добавления - 21.06.2018 в 18:06
Gustav Дата: Четверг, 21.06.2018, 19:59 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Примерно так, например, в ячейке 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
 
Ответить
СообщениеПримерно так, например, в ячейке 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]

Автор - Gustav
Дата добавления - 21.06.2018 в 19:59
BcjPrj Дата: Понедельник, 25.06.2018, 12:32 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - BcjPrj
Дата добавления - 25.06.2018 в 12:32
Gustav Дата: Понедельник, 25.06.2018, 14:10 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

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


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
А первый - так и не разобрался, где что.

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

Автор - Gustav
Дата добавления - 25.06.2018 в 14:10
BcjPrj Дата: Понедельник, 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
 
Ответить
СообщениеСовсем не понятно как это получается... но получается. МИСТИКА!!!)))

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

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

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

Автор - BcjPrj
Дата добавления - 25.06.2018 в 23:46
Gustav Дата: Вторник, 26.06.2018, 01:22 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

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

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


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Вторник, 26.06.2018, 01:30
 
Ответить
СообщениеЯ попробовал наглядно разобрать формулы по частям. Вот что из этого получилось:
https://docs.google.com/spreads....sharing

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

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

Автор - Gustav
Дата добавления - 26.06.2018 в 01:22
BcjPrj Дата: Вторник, 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
 
Ответить
СообщениеОтдельно - разобрался. Все в таблице очень наглядно.
Пытаюсь совместить 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 в 20:46
Manyasha Дата: Вторник, 26.06.2018, 21:49 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 2180
Репутация: 886 ±
Замечаний: 0% ±

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


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеBcjPrj, Оформляйте формулы тегами - кнопка fx в режиме правки поста. Или можно тегом для кода оформить (кнопка #), если "формульные" косячат.

Автор - Manyasha
Дата добавления - 26.06.2018 в 21:49
BcjPrj Дата: Вторник, 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
 
Ответить
Сообщение
Код
=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:07
Gustav Дата: Среда, 27.06.2018, 12:20 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

начинал с Excel 4.0...
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)


Мой tip box - яд 41001663842605
 
Ответить
Сообщение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)

Автор - Gustav
Дата добавления - 27.06.2018 в 12:20
Gustav Дата: Среда, 27.06.2018, 19:17 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

Эту задачу решает такая формула:
[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]


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Как преобразовать эту формулу, что бы ячейка оставалась пустой, если в строке нет значения А или В?

Эту задачу решает такая формула:
[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]

Автор - Gustav
Дата добавления - 27.06.2018 в 19:17
BcjPrj Дата: Среда, 27.06.2018, 19:25 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

Автор - BcjPrj
Дата добавления - 27.06.2018 в 19:25
Gustav Дата: Четверг, 28.06.2018, 11:56 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

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

Функция 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 подобным образом "омассивить" не удаётся, но возможны альтернативные массивные решения, о которых чуть позже.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
На листе МОДЕЛЬ надо из обычных формул сделать формулы массива

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

Функция 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 подобным образом "омассивить" не удаётся, но возможны альтернативные массивные решения, о которых чуть позже.

Автор - Gustav
Дата добавления - 28.06.2018 в 11:56
BcjPrj Дата: Четверг, 28.06.2018, 13:39 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - BcjPrj
Дата добавления - 28.06.2018 в 13:39
Gustav Дата: Четверг, 28.06.2018, 14:43 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

Итак, встречаем функцию 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, но с небольшим различием.


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

Итак, встречаем функцию 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, но с небольшим различием.

Автор - Gustav
Дата добавления - 28.06.2018 в 14:43
Gustav Дата: Четверг, 28.06.2018, 18:57 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

начинал с Excel 4.0...
По ходу дум родилась очень неплохая, как мне кажется, альтернатива элементарным построчным суммам, которые в столбце 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.


Мой tip box - яд 41001663842605
 
Ответить
СообщениеПо ходу дум родилась очень неплохая, как мне кажется, альтернатива элементарным построчным суммам, которые в столбце 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.

Автор - Gustav
Дата добавления - 28.06.2018 в 18:57
BcjPrj Дата: Четверг, 28.06.2018, 20:29 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
%)
надеюсь только, что скопировав эти формулы и подставив свои значения - обрету счастье.
 
Ответить
Сообщение%)
надеюсь только, что скопировав эти формулы и подставив свои значения - обрету счастье.

Автор - BcjPrj
Дата добавления - 28.06.2018 в 20:29
BcjPrj Дата: Пятница, 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)))))


или где?
 
Ответить
Сообщение
Код
=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)))))


или где?

Автор - BcjPrj
Дата добавления - 29.06.2018 в 00:09
Gustav Дата: Пятница, 29.06.2018, 06:35 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1606
Репутация: 641 ±
Замечаний: 0% ±

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

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

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

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

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

Эту-эту.


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Пятница, 29.06.2018, 06:46
 
Ответить
Сообщение
А что такое в этой формуле Z1? Я думал это ячейка в которой мы пишем формулу-ссылку...

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

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

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

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

Эту-эту.

Автор - Gustav
Дата добавления - 29.06.2018 в 06:35
BcjPrj Дата: Пятница, 29.06.2018, 13:03 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал BcjPrj - Пятница, 29.06.2018, 14:37
 
Ответить
СообщениеЯ подумал что именно ячейка Z1 должна определять по какую строку будет срабатывать формула, следовательно и все изменения в ней...
Нет? нельзя только ее подкорректировать так? Я просто как представил что все эти монстры придется править...

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

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