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

Вход

Регистрация

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

 

= Мир MS Excel/Посчитать количество товара в заказе по названию - Мир MS Excel

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

[size=12]]Добрый день!

помогите доработать формулу или др. вариант решения в таблице

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

Дорогие форумчане, поздравлю вас с наступающим Новым годом, очень надеюсь, что и в этот раз вы мне поможете.

https://docs.google.com/spreads....sharing
 
Ответить
Сообщение[size=12]]Добрый день!

помогите доработать формулу или др. вариант решения в таблице

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

Дорогие форумчане, поздравлю вас с наступающим Новым годом, очень надеюсь, что и в этот раз вы мне поможете.

https://docs.google.com/spreads....sharing
Автор - bonnylee
Дата добавления - 24.12.2021 в 14:54
bonnylee Дата: Пятница, 24.12.2021, 16:05 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

https://docs.google.com/spreadsheets/d/1FGuIIFt0H9FKkOnAGyQBRT2okvo3n20vWk20CIuo64Y/edit#gid=496235214

правильная ссылка
 
Ответить
Сообщениеhttps://docs.google.com/spreadsheets/d/1FGuIIFt0H9FKkOnAGyQBRT2okvo3n20vWk20CIuo64Y/edit#gid=496235214

правильная ссылка
Автор - bonnylee
Дата добавления - 24.12.2021 в 16:05
Gustav Дата: Суббота, 25.12.2021, 01:31 | Сообщение № 3
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Вот ведь, оказывается, не зря несколько дней назад потратил время здесь на вторую формулу (хоть вы ее и не просили). Следуя же основным принципам той формулы, сегодня родилось похожее творение - для ячейки E5 листа "ИНВ_Н":
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; '(23122021)'!$C:$C >= $C5; '(23122021)'!$C:$C < $D5+1);
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2))
[/vba]
Ну, а сейчас должно захватить дух - эта формула (достаточный монстр!) может еще и саморасширяться по строке! Для этого всего лишь надо дописать к одинокому адресу E$2 вторую границу диапазона - E$2:L$2:
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; '(23122021)'!$C:$C >= $C5; '(23122021)'!$C:$C < $D5+1);
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2:L$2))
[/vba]
Формулу также надо ввести в ячейку E5 и обязательно удалить формулы правее этой ячейки, т.е. очистить диапазон F5:L5, чтобы вводимая формула могла саморасшириться по пятой строке.

[p.s.]В предыдущей формуле два условия по датам для FILTER можно заменить одним, используя функцию ISBETWEEN (перевод, если сделают, наверное, будет какой-нибудь "емежду"):[/p.s.]
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; ISBETWEEN('(23122021)'!$C:$C; $C5; $D5+1; 1;0));
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2:L$2))
[/vba]Не могу сказать, что получается сильно короче, а главное - не получается сильно понятнее (ибо надо изучить параметры). Но если новые функции добавляют, значит, "это кому-нибудь нужно" и, значит, надо их изучать и использовать.

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

Сообщение отредактировал Gustav - Воскресенье, 26.12.2021, 00:53
 
Ответить
СообщениеВот ведь, оказывается, не зря несколько дней назад потратил время здесь на вторую формулу (хоть вы ее и не просили). Следуя же основным принципам той формулы, сегодня родилось похожее творение - для ячейки E5 листа "ИНВ_Н":
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; '(23122021)'!$C:$C >= $C5; '(23122021)'!$C:$C < $D5+1);
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2))
[/vba]
Ну, а сейчас должно захватить дух - эта формула (достаточный монстр!) может еще и саморасширяться по строке! Для этого всего лишь надо дописать к одинокому адресу E$2 вторую границу диапазона - E$2:L$2:
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; '(23122021)'!$C:$C >= $C5; '(23122021)'!$C:$C < $D5+1);
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2:L$2))
[/vba]
Формулу также надо ввести в ячейку E5 и обязательно удалить формулы правее этой ячейки, т.е. очистить диапазон F5:L5, чтобы вводимая формула могла саморасшириться по пятой строке.

[p.s.]В предыдущей формуле два условия по датам для FILTER можно заменить одним, используя функцию ISBETWEEN (перевод, если сделают, наверное, будет какой-нибудь "емежду"):[/p.s.]
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( VLOOKUP(
TRANSPOSE( SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; ISBETWEEN('(23122021)'!$C:$C; $C5; $D5+1; 1;0));
" \([^()]*\)";""); " + ";"|")
); "|") );
'СПР'!$A:$B;2;0); "Не найдено"); E$2:L$2))
[/vba]Не могу сказать, что получается сильно короче, а главное - не получается сильно понятнее (ибо надо изучить параметры). Но если новые функции добавляют, значит, "это кому-нибудь нужно" и, значит, надо их изучать и использовать.
Автор - Gustav
Дата добавления - 25.12.2021 в 01:31
bonnylee Дата: Воскресенье, 26.12.2021, 16:17 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Gustav, почему то первый мой ответ с благодарностью не отправился (( хочу сказать большое спасибо )))
Появилось несколько вопросов по формуле
1. в формуле фильтр используется <D5+1 почему не <=D5
2. зачем удаляются символы " \([^()]*\)"
3. зачем функцией трансп меняем столбцы и строки местами

А так же, в формулу мне нужно добавить условие, чтобы номер заказа не был равен НЕТ. меняю суммесли на суммесимн и добавляю в конец '(23122021)'!B:B"<>"&$F$1. формула выдает ошибку Синтаксическая ошибка в формуле. как быть?
 
Ответить
СообщениеGustav, почему то первый мой ответ с благодарностью не отправился (( хочу сказать большое спасибо )))
Появилось несколько вопросов по формуле
1. в формуле фильтр используется <D5+1 почему не <=D5
2. зачем удаляются символы " \([^()]*\)"
3. зачем функцией трансп меняем столбцы и строки местами

А так же, в формулу мне нужно добавить условие, чтобы номер заказа не был равен НЕТ. меняю суммесли на суммесимн и добавляю в конец '(23122021)'!B:B"<>"&$F$1. формула выдает ошибку Синтаксическая ошибка в формуле. как быть?
Автор - bonnylee
Дата добавления - 26.12.2021 в 16:17
Gustav Дата: Воскресенье, 26.12.2021, 20:26 | Сообщение № 5
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
1. в формуле фильтр используется <D5+1 почему не <=D5
Потому что на листе "(23122021)" даты заказа содержат дату и время, а условия на листе "ИНВ_Н" - только дату. Поэтому если задать верхнюю границу периода условием "<= 14.03.21", то на это условие за 14-е число можно будет поймать только полуночный заказ с датой "14.03.21 00:00:00". А при условии "< 14.03.21 + 1(день)", т.е. фактически "< 15.03.21" - все заказы за 14-е число вплоть до момента времени "14.03.21 23:59:59"

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
2. зачем удаляются символы " \([^()]*\)"
Это не "символы для удаления", а шаблон регулярного выражения (нужно погуглить про них, если раньше не сталкивались). С помощью этого шаблона функция REGEXREPLACE вычищает (виртуально) из ячеек текст в скобках, включая сами скобки, т.е. было ' "Русское Лубок" (арт. 2000000002057)' - стало ' "Русское Лубок"'.

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
3. зачем функцией трансп меняем столбцы и строки местами
Чтобы на листе "СПР" пользоваться функцией ВПР (VLOOKUP), поскольку данные для поиска на этом листе расположены "вертикально" в двух столбцах, а массив, возвращаемый комбинацией функций SPLIT/JOIN, имеет горизонтальную ориентацию. Можно не разворачивать этот горизонтальный массив и использовать функцию ГПР (HLOOKUP), но тогда придётся развернуть (виртуально) данные листа "СПР", т.е. функция TRANSPOSE просто переедет в другое место формулы (см. пример ниже).

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
'(23122021)'!B:B"<>"&$F$1
Это условие в рассматриваемом примере проще всего внести в функцию FILTER (наряду с условиями по датам).

Итого, с заменой направления виртуального поиска и с внесением дополнительного условия в FILTER, наша формула приобретает (или может приобрести) следующий вид:
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( HLOOKUP(
SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; ISBETWEEN('(23122021)'!$C:$C; $C5; $D5+1; 1;0); '(23122021)'!$B:$B <> $F$1);
" \([^()]*\)";""); " + ";"|")
); "|");
TRANSPOSE('СПР'!$A:$B);2;0); "Не найдено"); E$2:L$2))
[/vba]
В дополнение к этим объяснениям посмотрите по ссылке из сообщения №3 мои комментарии по работе второй формулы из предыдущей вашей темы. Там довольно подробно всё описано.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
1. в формуле фильтр используется <D5+1 почему не <=D5
Потому что на листе "(23122021)" даты заказа содержат дату и время, а условия на листе "ИНВ_Н" - только дату. Поэтому если задать верхнюю границу периода условием "<= 14.03.21", то на это условие за 14-е число можно будет поймать только полуночный заказ с датой "14.03.21 00:00:00". А при условии "< 14.03.21 + 1(день)", т.е. фактически "< 15.03.21" - все заказы за 14-е число вплоть до момента времени "14.03.21 23:59:59"

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
2. зачем удаляются символы " \([^()]*\)"
Это не "символы для удаления", а шаблон регулярного выражения (нужно погуглить про них, если раньше не сталкивались). С помощью этого шаблона функция REGEXREPLACE вычищает (виртуально) из ячеек текст в скобках, включая сами скобки, т.е. было ' "Русское Лубок" (арт. 2000000002057)' - стало ' "Русское Лубок"'.

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
3. зачем функцией трансп меняем столбцы и строки местами
Чтобы на листе "СПР" пользоваться функцией ВПР (VLOOKUP), поскольку данные для поиска на этом листе расположены "вертикально" в двух столбцах, а массив, возвращаемый комбинацией функций SPLIT/JOIN, имеет горизонтальную ориентацию. Можно не разворачивать этот горизонтальный массив и использовать функцию ГПР (HLOOKUP), но тогда придётся развернуть (виртуально) данные листа "СПР", т.е. функция TRANSPOSE просто переедет в другое место формулы (см. пример ниже).

Цитата bonnylee, 26.12.2021 в 16:17, в сообщении № 4 ()
'(23122021)'!B:B"<>"&$F$1
Это условие в рассматриваемом примере проще всего внести в функцию FILTER (наряду с условиями по датам).

Итого, с заменой направления виртуального поиска и с внесением дополнительного условия в FILTER, наша формула приобретает (или может приобрести) следующий вид:
[vba]
Код
=ArrayFormula(COUNTIF( IFERROR( HLOOKUP(
SPLIT( JOIN("|";
SUBSTITUTE( REGEXREPLACE(
FILTER('(23122021)'!$D:$D; ISBETWEEN('(23122021)'!$C:$C; $C5; $D5+1; 1;0); '(23122021)'!$B:$B <> $F$1);
" \([^()]*\)";""); " + ";"|")
); "|");
TRANSPOSE('СПР'!$A:$B);2;0); "Не найдено"); E$2:L$2))
[/vba]
В дополнение к этим объяснениям посмотрите по ссылке из сообщения №3 мои комментарии по работе второй формулы из предыдущей вашей темы. Там довольно подробно всё описано.
Автор - Gustav
Дата добавления - 26.12.2021 в 20:26
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Посчитать количество товара в заказе по названию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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