Отбор уникальных значений по условию: кроме (за исключением)

Дата: Среда, 03.06.2020, 12:31 | Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Здравствуйте уважаемые товарищи помогающие!

Полное название темы, которое не поместилось в окно написания темы: «Отбор уникальных значений по условию: кроме (за исключением) определённого слова или нескольких слов формулой»

Дано: в диапазоне A18:P59 таблица: «Реестр чеков Бензин А – 92 с 7 Май по 20 Май 2020»
В диапазоне G21:G58 определённые слова – текст.
Задача:
1 формула: необходимо в диапазон ячеек D155:D181 отобрать уникальные значения из диапазона G21:G58 кроме (за исключением) слова «ВСЕГО», то есть что бы в диапазоне ячеек D155:D181 в результате не было слова «ВСЕГО».

2 формула: как написать формулу отбора уникальных значений в диапазон ячеек D155:D181 из диапазона G21:G58 кроме (за исключением) не одного, а нескольких 2 (двух) и больше слов. То есть что бы в эту формулу можно было бы писать не одно, а несколько слов. Например: в диапазоне ячеек G21:G58 слова «ВСЕГО», «ВМЕСТЕ», «ИТОГ» и так далее. И что бы в диапазоне ячеек D155:D181 не было этих слов.

Я написал в диапазон ячеек D155:D181 формулу массива: сочетание клавиш CTRL + SHIFT + ВВОД

Код

{=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($G$21:$G$58);"";СЧЁТЕСЛИ(D$154:$D154;$G$21:$G$58));0));"")}

из сайта Отбор уникальных значений (убираем повторы из списка) в EXCEL. Вот ссылка: https://excel2.ru/article....s-excel
на основе формулы

Код

{=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($A$5:$A$19);"";СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0));"")}

Скажите, пожалуйста, правильно ли я написал эту формулу?
Меня интересует, правильно ли я указал в формуле диапазон ячеек (D$154:$D154;

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

Вот ссылки похожих тем: 1. Отбор значений из массива: http://www.excelworld.ru/forum/2-21683-1
2. Отбор уникальных значений в EXCEL с условиями: https://excel2.ru/article....oviyami

Мне кажется, а может я, и ошибаюсь в формулу необходимо написать
ЕСЛИ$G$21:$G$58<>«ВСЕГО»)) или ЕСЛИ$G$21:$G$58=«ВСЕГО»))
только я не могу правильно написать эту формулу.
Помогите мне, пожалуйста.
С уважением и наилучшими пожеланиями.

Код

{=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($G$21:$G$58);"";СЧЁТЕСЛИ(D$154:$D154;$G$21:$G$58));0));"")}

Код

{=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($A$5:$A$19);"";СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0));"")}

К сообщению приложен файл: __________-_-__.xlsx (120.8 Kb)

Сообщение отредактировал Никанор - Среда, 03.06.2020, 14:42

СообщениеЗдравствуйте уважаемые товарищи помогающие!

Полное название темы, которое не поместилось в окно написания темы: «Отбор уникальных значений по условию: кроме (за исключением) определённого слова или нескольких слов формулой»

Дано: в диапазоне A18:P59 таблица: «Реестр чеков Бензин А – 92 с 7 Май по 20 Май 2020»
В диапазоне G21:G58 определённые слова – текст.
Задача:
1 формула: необходимо в диапазон ячеек D155:D181 отобрать уникальные значения из диапазона G21:G58 кроме (за исключением) слова «ВСЕГО», то есть что бы в диапазоне ячеек D155:D181 в результате не было слова «ВСЕГО».

2 формула: как написать формулу отбора уникальных значений в диапазон ячеек D155:D181 из диапазона G21:G58 кроме (за исключением) не одного, а нескольких 2 (двух) и больше слов. То есть что бы в эту формулу можно было бы писать не одно, а несколько слов. Например: в диапазоне ячеек G21:G58 слова «ВСЕГО», «ВМЕСТЕ», «ИТОГ» и так далее. И что бы в диапазоне ячеек D155:D181 не было этих слов.

Я написал в диапазон ячеек D155:D181 формулу массива: сочетание клавиш CTRL + SHIFT + ВВОД

Код

{=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($G$21:$G$58);"";СЧЁТЕСЛИ(D$154:$D154;$G$21:$G$58));0));"")}

Код

{=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;ПОИСКПОЗ(0;ЕСЛИ(ЕПУСТО($A$5:$A$19);"";СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0));"")}

Автор - Никанор
Дата добавления - 03.06.2020 в 12:31

Дата: Среда, 03.06.2020, 18:23 | Сообщение № 2

Группа: Друзья

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

Сообщений: 1649

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

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

2013 Win, 365 Mac

Никанор, здравствуйте. Если правильно понял. Попробуйте в D155 формулу массива

Код

=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;НАИБОЛЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($G$21:$G$58;$G$21:$G$58;);)*($G$21:$G$58<>"ВСЕГО")=СТРОКА($A$21:$A$58)-20;СТРОКА($A$21:$A$58)-20);СТРОКА(A1)));"")

Проверьте на реальном файле.

Делай нормально и будет нормально!

Дата: Четверг, 04.06.2020, 10:27 | Сообщение № 3

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Здравствуйте уважаемый Алексей!
Большое спасибо за ответ!

Цитата Che79, 03.06.2020 в 18:23, в сообщении № 2 ()

Если правильно понял.

Да Вы правильно поняли. Я проверил и на файле примере __________-_-__.xlsx(120.8 Kb) и на реальном файле.
формула правильно «работает» даёт правильный результат – так как я и хотел кроме (за исключением) слова «ВСЕГО».
Можно задать ещё Вам вопросы?
1 – ый вопрос из 3 – ёх вопросов: В таблице в диапазоне ячеек G21:G58 слова идут в столбце в такой последовательности:
Виброплита AVANT AP – 95
Воздуходувка STIHL BR 3__ 0
Бензокоса STIHL

А Результат в таблице в диапазоне ячеек D155:D181 слова идут в столбце не в такой последовательности как в в диапазоне ячеек G21:G58 а в следующей:
Бензокоса STIHL
Воздуходувка STIHL BR 3__ 0
Виброплита AVANT AP – 95
1 – ый вопрос из 3 – ёх вопросов: можно изменить эту готовую, правильную формулу, что бы в диапазоне ячеек D155:D181 была такая же последовательность, как и в 1 – ой таблице в диапазоне ячеек G21:G58?
Хочу подчеркнуть это не принципиально, если нельзя этого сделать, то не надо терять время, ведь Ваша формула даёт правильный результат – так как я и хотел.

2 – ой вопрос из 3 – ёх вопросов: Правильно ли я ответил на свой 2 – ой вопрос из моего сообщения Дата: Среда, 03.06.2020, 12:31 | Сообщение № 1 изменил Вашу формулу. То есть что бы в эту формулу можно было бы писать не одно, а несколько слов.
Получилась следующая формула с 2 – мя словами которые не считаются: "ВСЕГО" и "ИТОГ"

Код

=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;НАИБОЛЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($G$21:$G$58;$G$21:$G$58;);)*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")=СТРОКА($A$21:$A$58)-20;СТРОКА($A$21:$A$58)-20);СТРОКА(A1)));"")

А вот получилась следующая формула с 3 – мя словами которые не считаются: "ВСЕГО", "ИТОГ" и "ВМЕСТЕ"

Код

=ЕСЛИОШИБКА(ИНДЕКС($G$21:$G$58;НАИБОЛЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ($G$21:$G$58;$G$21:$G$58;);)*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($G$21:$G$58<>"ВМЕСТЕ")=СТРОКА($A$21:$A$58)-20;СТРОКА($A$21:$A$58)-20);СТРОКА(A2)));"")

Я для этих формул, что бы было больше 1 – го слова добавлял в формулу *($G$21:$G$58<>"ИТОГ") *($G$21:$G$58<>"ВМЕСТЕ").
Также можно добавлять и другие слова правильно?

3 – ий вопрос из 3 – ёх вопросов: в ячейке I113 стоит формула

Код

=ЕСЛИ(C113<>"";СУММЕСЛИ($C$21:$C$58;C113;$I$21:$I$58);"")

и ниже в столбце «протянута» – скопирована эта формула.
Эта формула даёт Результат: количество литров с учётом слова «ВСЕГО».
Вопрос: как изменить эту формулу что бы она она давала Результат кроме (за исключением) слова «ВСЕГО» из диапазона ячеек A18:P59?

Здравствуйте уважаемый Алексей!
Большое спасибо за ответ!

Цитата Che79, 03.06.2020 в 18:23, в сообщении № 2 ()

Если правильно понял.

Код

А вот получилась следующая формула с 3 – мя словами которые не считаются: "ВСЕГО", "ИТОГ" и "ВМЕСТЕ"

Код

=ЕСЛИ(C113<>"";СУММЕСЛИ($C$21:$C$58;C113;$I$21:$I$58);"")

Сообщение отредактировал Никанор - Четверг, 04.06.2020, 10:48

СообщениеЗдравствуйте уважаемый Алексей!
Большое спасибо за ответ!

Цитата Che79, 03.06.2020 в 18:23, в сообщении № 2 ()

Если правильно понял.

Код

А вот получилась следующая формула с 3 – мя словами которые не считаются: "ВСЕГО", "ИТОГ" и "ВМЕСТЕ"

Код

=ЕСЛИ(C113<>"";СУММЕСЛИ($C$21:$C$58;C113;$I$21:$I$58);"")

Автор - Никанор
Дата добавления - 04.06.2020 в 10:27

Дата: Четверг, 04.06.2020, 11:01 | Сообщение № 4

Группа: Друзья

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

Сообщений: 1649

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

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

2013 Win, 365 Mac

Если вкратце, то
1. Замените в формуле НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ()
2. Да, верно. Такая конструкция исключает ВСЕ указанные критерии
3. Не понял вопрос. У Вас СУММЕСЛИ() подсчитывает по каждому критерию отдельно. Не будет в столбце С "ВСЕГО" - не будет и суммы по нему...

Делай нормально и будет нормально!

Дата: Четверг, 04.06.2020, 11:46 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Алексей!

Большое спасибо за ответ!
И вкратце я всё понял.
По 1 – му вопросу: Заменил в формуле НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ()
Результат такой как я и хотел.

Да 2 – ой вопрос может, задан не очень понятно.
Уточняю 2 – ой вопрос – пишу по-другому.
В 1 – ой таблице в столбце С в диапазоне ячеек C21:C58 «№ чека»
В 1 – ой таблице в столбце I в диапазоне ячеек I21:I58 «Количество,литров»
Во 2 – ой таблице в столбце C в диапазоне ячеек C113:C139 формула «отбирает уникальные значения из 1 – ой таблицы из диапазона ячеек C21:C58. Эта формула даёт правильный результат.
Нужна формула в диапазон ячеек I113:I139 что бы считала количество литров по каждому чеку за исключением слова «ВСЕГО» из диапазона ячеек G21:G58.

Объясню по подробнее:
Например, по чеку № 7785 есть 3 (три) строки:
Ячейка С21 – чек 7785 – Виброплита AVANT AP – 95 (ячейка G21) – 2 литры (ячейка I21)
Ячейка С22 – чек 7785 – Воздуходувка STIHL BR 3__ 0 (ячейка G22) – 3 литры (ячейка I22)
Ячейка С23 – чек 7785 – ВСЕГО (ячейка G23) – 5 литров (ячейка I23)
В ячейке I113 Результат 2 литры + 3 литры + 5 литров = 10 литров.
А нужен результат в ячейку I113: 2 литры + 3 литры 5 литров. За исключением ячейки G23 (там слово «ВСЕГО») 5 литров.

А по чеку № 7822 есть 1 (одна) строка:
Ячейка С24 – чек 7822 – Бензокоса STIHL (ячейка G24) – 10 литров (ячейка I24)
В ячейке I114 Результат 10 литров – правильный Результат, так как в столбце G24 нет слова «ВСЕГО».
Поэтому и мне нужна формула в столбец I в диапазон ячеек I113:I139 что бы не считала слово «ВСЕГО» из столбца G диапазона ячеек G22:G58.

Алексей!
Скажите, пожалуйста, я понятно сформулировал вопрос?

[p.s.] Может в этом случае подойдёт изменённая формула _Boroda – Александра из темы: Применение Формулы СУММЕСЛИН со несколькими условиями.
Дата: Среда, 19.07.2017, 12:38 | Сообщение № 2
Вот ссылка: http://www.excelworld.ru/forum/2-34559-1
Или формула sboy – Сергея из темы: суммпроизв с 1 условием Дата: Вторник, 30.05.2017, 12:36 | Сообщение № 2
Вот ссылка: http://www.excelworld.ru/forum/2-33939-1

Только я не знаю как их изменить – написать.

Сообщение отредактировал Никанор - Четверг, 04.06.2020, 15:37

СообщениеАлексей!

Большое спасибо за ответ!
И вкратце я всё понял.
По 1 – му вопросу: Заменил в формуле НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ()
Результат такой как я и хотел.

Да 2 – ой вопрос может, задан не очень понятно.
Уточняю 2 – ой вопрос – пишу по-другому.
В 1 – ой таблице в столбце С в диапазоне ячеек C21:C58 «№ чека»
В 1 – ой таблице в столбце I в диапазоне ячеек I21:I58 «Количество,литров»
Во 2 – ой таблице в столбце C в диапазоне ячеек C113:C139 формула «отбирает уникальные значения из 1 – ой таблицы из диапазона ячеек C21:C58. Эта формула даёт правильный результат.
Нужна формула в диапазон ячеек I113:I139 что бы считала количество литров по каждому чеку за исключением слова «ВСЕГО» из диапазона ячеек G21:G58.

Объясню по подробнее:
Например, по чеку № 7785 есть 3 (три) строки:
Ячейка С21 – чек 7785 – Виброплита AVANT AP – 95 (ячейка G21) – 2 литры (ячейка I21)
Ячейка С22 – чек 7785 – Воздуходувка STIHL BR 3__ 0 (ячейка G22) – 3 литры (ячейка I22)
Ячейка С23 – чек 7785 – ВСЕГО (ячейка G23) – 5 литров (ячейка I23)
В ячейке I113 Результат 2 литры + 3 литры + 5 литров = 10 литров.
А нужен результат в ячейку I113: 2 литры + 3 литры 5 литров. За исключением ячейки G23 (там слово «ВСЕГО») 5 литров.

А по чеку № 7822 есть 1 (одна) строка:
Ячейка С24 – чек 7822 – Бензокоса STIHL (ячейка G24) – 10 литров (ячейка I24)
В ячейке I114 Результат 10 литров – правильный Результат, так как в столбце G24 нет слова «ВСЕГО».
Поэтому и мне нужна формула в столбец I в диапазон ячеек I113:I139 что бы не считала слово «ВСЕГО» из столбца G диапазона ячеек G22:G58.

Алексей!
Скажите, пожалуйста, я понятно сформулировал вопрос?

[p.s.] Может в этом случае подойдёт изменённая формула _Boroda – Александра из темы: Применение Формулы СУММЕСЛИН со несколькими условиями.
Дата: Среда, 19.07.2017, 12:38 | Сообщение № 2
Вот ссылка: http://www.excelworld.ru/forum/2-34559-1
Или формула sboy – Сергея из темы: суммпроизв с 1 условием Дата: Вторник, 30.05.2017, 12:36 | Сообщение № 2
Вот ссылка: http://www.excelworld.ru/forum/2-33939-1

Только я не знаю как их изменить – написать.

Автор - Никанор
Дата добавления - 04.06.2020 в 11:46

Дата: Четверг, 04.06.2020, 18:32 | Сообщение № 6

Группа: Друзья

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

Сообщений: 1649

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

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

2013 Win, 365 Mac

Может, так? В I113

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58)

+ФЯ 0;; для скрытия нулей

Делай нормально и будет нормально!

Дата: Четверг, 04.06.2020, 21:23 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Алексей!
Большое спасибо!
Ваша формула в Сообщение № 6 Дата: Четверг, 04.06.2020, 18:32 даёт правильный результат.

1 – вопрос: А ещё для скрытия нулей можно её записать так:

Код

=ЕСЛИ(B113<>"";СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58);"")

Правильно?

2 – ой вопрос:
Скажите, пожалуйста, правильно ли я написал другие варианты формул для ячейки I113?
1 – ый вариант формула массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

Её написал на основе формулы из сайта Планета Excel.
Вот ссылка https://www.planetaexcel.ru/techniques/2/167/
Из файла: conditional-calcs.xlsx, «Лист Мастер суммирования», на основе этой формулы:

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

2 – ой вариант формула массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

Добавил в формулу =ЕСЛИ(B113<>"";
Что бы ЕСЛИ не заполнена – ПУСТАЯ ячейка – РЕЗУЛЬТАТ был ПУСТАЯ ЯЧЕЙКА, а не 0 (ноль).
Её написал на основе формулы из сайта Планета Excel.
Вот ссылка https://www.planetaexcel.ru/techniques/2/167/
Из файла: conditional-calcs.xlsx «Лист Мастер суммирования», на основе этой формулы:

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

3 – ий вариант формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

Её написал на основе формулы из сайта Мир эксель. Вот ссылка http://www.excelworld.ru/forum/2-34559-1
Сообщение № 2 Дата: Среда, 19.07.2017, 12:38 _Boroda_ – Александра на основе этой формулы:

Код

=СУММПРОИЗВ($C$10:$F$11*($B$10:$B$11=$B4)*($C$9:$F$9=K$1))

Эта формула находится в файле 8572075_1.xls(31.0 Kb) в ячейке K4. И в диапазоне ячеек K3:M4 аналогичные формулы, скопированные и «протянутые» из ячейки K4.
У меня 3 – ий вариант формулы очень короткий короче чем у Вас.

Я писал формулы интуитивно – «Методом проб и подстановок», ведь я не специалист, поэтому и спрашиваю у Вас: правильный результат они будут давать, особенно третий вариант?

P. S. Они дают правильный результат в файле примере и в реальном – рабочем файле только я сомневаюсь в их правильности, так как повторюсь, писал их «Интуитивно – наугад» «Методом проб и подстановок».
Поэтому и спрашиваю у Вас.

Код

=ЕСЛИ(B113<>"";СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58);"")

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

2 – ой вариант формула массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

3 – ий вариант формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

Код

=СУММПРОИЗВ($C$10:$F$11*($B$10:$B$11=$B4)*($C$9:$F$9=K$1))

Сообщение отредактировал китин - Понедельник, 15.06.2020, 15:28

СообщениеАлексей!
Большое спасибо!
Ваша формула в Сообщение № 6 Дата: Четверг, 04.06.2020, 18:32 даёт правильный результат.

1 – вопрос: А ещё для скрытия нулей можно её записать так:

Код

=ЕСЛИ(B113<>"";СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58);"")

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

2 – ой вариант формула массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

Код

{=СУММ(ЕСЛИ($B$2:$B$26="Григорьев";ЕСЛИ($A$2:$A$26="Копейка";$D$2:$D$26;0);0))}

3 – ий вариант формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

Код

=СУММПРОИЗВ($C$10:$F$11*($B$10:$B$11=$B4)*($C$9:$F$9=K$1))

Автор - Никанор
Дата добавления - 04.06.2020 в 21:23

Дата: Четверг, 04.06.2020, 22:46 | Сообщение № 8

Группа: Друзья

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

Сообщений: 1649

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

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

2013 Win, 365 Mac

1. Правильно. Единственный нюанс - скрытый 0 (ноль) это число, а "" в формуле- это текст. Учитывайте это в последующих операциях.
2. Правильный. Вы же проверили все на реальном файле?
Никанор, а не пробовали вести всё это хозяйство в виде Справочников / плоских таблиц с выводом результатов в сводную? Не удобнее ли будет?

Делай нормально и будет нормально!

Дата: Пятница, 05.06.2020, 10:37 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Здравствуйте уважаемый Алексей!
Большое спасибо за ответы на мои вопросы и советы.

1 – ый Ваш ответ – совет: - скрытый 0 (ноль) это число, а "" в формуле- это текст. Учитывайте это в последующих операциях.

Мой ответ: Я привык, что бы не было текста или чисел, а была ПУСТАЯ ячейка вписывать в формулу – шаблон нужную формулу. Вот в такую формулу – шаблон я вписываю нужную формулу:

Код

=ЕСЛИ(B113<>"";Формула;"")

Теперь буду учитывать в последующих операциях, что «скрытый 0 (ноль) это число, а "" в формуле- это текст.
1 – ый вопрос: Иногда такая формула выдаёт ошибку. Это наверное из-за кавычек ""? Правильно?

2 – ой Ваш ответ – вопрос: Вы же проверили все на реальном файле?
Мой ответ: В Сообщение № 7 Дата: Четверг, 04.06.2020, 21:23 | я написал синим шрифтом P.S. – Post Scriptum (Пост Скриптум).

3 – ий Ваш ответ – вопрос: Не удобнее ли будет вести всё это хозяйство в виде Справочников / плоских таблиц с выводом результатов в сводную?

Мой ответ: Я не пробовал вести всё это хозяйство в виде Справочников / плоских таблиц с выводом результатов в сводную. Я не знаю как это делать. Но, наверное, это было бы удобнее.
Я при печати страницы вывожу 3 (три) таблицы на лист.
Если можно дайте пожалуйста ссылку где можно прочитать в Интернете о «Сводная таблица».

Алексей!
Позвольте задать ещё Вам вопросы.
2 – ой вопрос: Правильно ли я написал варианты формул с добавлением другого слова для примера слово «ИТОГ».

Если правильно, то также можно добавлять в формулу и другие слова?

1 – ый вариант формула массива: сочетание клавиш CTRL + SHIFT + ВВОД с добавлением слово «ИТОГ»:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)));"")}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

2 – ой вариант формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД с добавлением слово «ИТОГ»: :

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)))}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

3 – ий вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($C$21:$C$58=C113))

Её я написал на основании формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

4 – ый вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($G$21:$G$58<>"")*$I$21:$I$58)

Её я написал на основании Вашей формулы из Сообщение № 6: Дата: Четверг, 04.06.2020, 18:32 |

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58)

Эти формулы дают правильный результат в файле примере и в реальном – рабочем файле только я сомневаюсь в их правильности, так как повторюсь, писал их «Интуитивно – наугад» «Методом проб и подстановок». Поэтому и спрашиваю у Вас – специалиста.

3 – ий вопрос: Эти формулы будут давать правильный результат при написании разными буквами заглавными и строчными, например:
«Всего» или «ВсЕгО» или «вСЕго» или «ВСЕГО» или как – то по другому?

Код

=ЕСЛИ(B113<>"";Формула;"")

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)));"")}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

2 – ой вариант формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД с добавлением слово «ИТОГ»: :

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)))}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

3 – ий вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($C$21:$C$58=C113))

Её я написал на основании формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

4 – ый вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($G$21:$G$58<>"")*$I$21:$I$58)

Её я написал на основании Вашей формулы из Сообщение № 6: Дата: Четверг, 04.06.2020, 18:32 |

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58)

Сообщение отредактировал Никанор - Пятница, 05.06.2020, 17:24

СообщениеЗдравствуйте уважаемый Алексей!
Большое спасибо за ответы на мои вопросы и советы.

1 – ый Ваш ответ – совет: - скрытый 0 (ноль) это число, а "" в формуле- это текст. Учитывайте это в последующих операциях.

Мой ответ: Я привык, что бы не было текста или чисел, а была ПУСТАЯ ячейка вписывать в формулу – шаблон нужную формулу. Вот в такую формулу – шаблон я вписываю нужную формулу:

Код

=ЕСЛИ(B113<>"";Формула;"")

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)));"")}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=ЕСЛИ(B113<>"";СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0));"")}

2 – ой вариант формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД с добавлением слово «ИТОГ»: :

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($G$21:$G$58<>"ИТОГ";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0)))}

Её я написал на основании формулы массива: сочетание клавиш CTRL + SHIFT + ВВОД:

Код

{=СУММ(ЕСЛИ($G$21:$G$58<>"ВСЕГО";
ЕСЛИ($C$21:$C$58=C113;$I$21:$I$58;0);0))}

3 – ий вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($C$21:$C$58=C113))

Её я написал на основании формулы:

Код

=СУММПРОИЗВ($I$21:$I$58*($G$21:$G$58<>"ВСЕГО")*($C$21:$C$58=C113))

4 – ый вариант формулы с добавлением слово «ИТОГ»:

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"ИТОГ")*($G$21:$G$58<>"")*$I$21:$I$58)

Её я написал на основании Вашей формулы из Сообщение № 6: Дата: Четверг, 04.06.2020, 18:32 |

Код

=СУММПРОИЗВ(($C$21:$C$58=C113)*($C$21:$C$58<>"")*($G$21:$G$58<>"ВСЕГО")*($G$21:$G$58<>"")*$I$21:$I$58)

Автор - Никанор
Дата добавления - 05.06.2020 в 10:37

Дата: Пятница, 05.06.2020, 11:10 | Сообщение № 10

Группа: Друзья

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

Сообщений: 1649

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

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

2013 Win, 365 Mac

1. Относительно ошибок из-за кавычек надо смотреть каждый случай в отдельности
2. При добавлении новых условий/ критериев в формулу смотрите за синтаксисом (Excel подскажет) и тестируйте на реальном файле
3. Про Сводные таблицы - посмотрите, например, эту тему с Вашим участием. И, конечно, поисковики Вам в помощь.
4. "ВСЕГО" или "вСеГО" - регистр не принципиален. Но, "ВСЕГО" и "ВСЕГО " для Excel "две большие разницы"

Будьте с этим внимательны.

Делай нормально и будет нормально!

Дата: Пятница, 05.06.2020, 11:18 | Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Алексей!
Большое Вам спасибо за ответы на мои многочисленные надоедливые вопросы!

Я сразу не обратил внимание, какая разница между "ВСЕГО" и "ВСЕГО ". Показалось что это одно и тоже.
А потом заметил что после второго "ВСЕГО " стоит пробел. Да на это нужно обращать внимание. Я это замечал что для Excel это «две большие разницы».

Сообщение отредактировал Никанор - Пятница, 05.06.2020, 17:26

Дата: Пятница, 12.06.2020, 15:34 | Сообщение № 12

Группа: Проверенные

Ранг: Обитатель

Сообщений: 495

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

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

Excel 2007

Здравствуйте уважаемый Алексей!

Цитата Che79, 04.06.2020 в 22:46, в сообщении № 8 ()

Никанор, а не пробовали вести всё это хозяйство в виде Справочников / плоских таблиц с выводом результатов в сводную? Не удобнее ли будет?

Спасибо за идею о сводных таблицах! Будет удобнее.
Иногда нужно посмотреть на вопрос или проблему свежим взглядом так сказать постороннего человека. И можно увидеть решение по – иному, не так как ставился первоначально вопрос. Как Вы в моём случае заметили, что можно сделать Сводные таблицы.
Я так и сделал. Если вкратце, я сделал две таблицы. В первую таблицу вношу данные только чеков. А во вторую данные по механизмам: сколько литров, на какой механизм использовано бензина, из какого чека.
Если кому интересно могу приложить файл.

[offtop]Мне Ваш подход и идея напомнила высказывание старшего лейтенанта Шарапова из романа «Эра Милосердия» Братьев Вайнеров и кинофильма «Место встречи изменить нельзя» Станислава Говорухина.
Вот цитата из романа «Эра Милосердия» Братьев Вайнеров:
- Илья Сергеич, я действительно в милиции недавно, и опыта нет никакого, и в
юриспруденции этой самой я не очень, но... вот какая штука. Я, когда разведротой командовал,
любил к наблюдателю нового человека подсылать - старый ему видимую обстановку
докладывал, а тот свежим глазом проверял. И, представьте, очень удачно это порой получалось,
потому что у наблюдателя от целого дня напряженного всматривания глаз, что называется,
замыливался; он, чего и не было, видел и, наоборот, не замечал порой того, что внове
появлялось. Понимаете?
«Эра Милосердия» – читать книгу.
[/offtop]

Здравствуйте уважаемый Алексей!

Цитата Che79, 04.06.2020 в 22:46, в сообщении № 8 ()

К сообщению приложен файл: 1457181.gif (69.0 Kb) · 9756491.gif (69.3 Kb)

Сообщение отредактировал Никанор - Пятница, 12.06.2020, 16:10