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

Вход

Регистрация

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

 

= Мир MS Excel/Вытянуть коллич. из заказа, если в одной ячейке несколько шт - Мир MS Excel

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

Добрый день! помогите настроить формулу. deal на листе свод нужно подтянуть данные в зависимости от даты и от наименования продукции, это я могу)), но проблема в том, что массив заказы в каждой ячейке имеет несколько уникальных артиклей (которые подтягиваются через переходник).
пробовала счетеслимн, не получилось, формулу Query изучала, но применить ее пока не ни разу не могу ((

https://docs.google.com/spreads....sharing
 
Ответить
СообщениеДобрый день! помогите настроить формулу. deal на листе свод нужно подтянуть данные в зависимости от даты и от наименования продукции, это я могу)), но проблема в том, что массив заказы в каждой ячейке имеет несколько уникальных артиклей (которые подтягиваются через переходник).
пробовала счетеслимн, не получилось, формулу Query изучала, но применить ее пока не ни разу не могу ((

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

Автор - bonnylee
Дата добавления - 11.12.2021 в 21:20
Gustav Дата: Воскресенье, 12.12.2021, 02:47 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2141
Репутация: 841 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Формула для ячейки D7 на листе "свод" - формула копируема (протягиваема) на остальные зеленые ячейки этого листа:
[vba]
Код
=ArrayFormula(SUM(COUNTIFS('заказы'!$B:$B;"*"&FILTER('переходник'!$A:$A; 'переходник'!$B:$B=D$4)&"*";'заказы'!$A:$A;">="&$A7;'заказы'!$A:$A;"<="&$B7)))
[/vba]
ОГРАНИЧЕНИЕ: Предполагается уникальность артикула в одной ячейке, т.е. конкретный артикул может встретиться в одной ячейке только один раз (возможно, среди других артикулов). Если в одной ячейке один и тот же артикул встретится более одного раза, то формула все равно учтет его только один раз.

[p.s.]ПРЕОДОЛЕНИЕ ОГРАНИЧЕНИЯ[/p.s.]
Следующая формула, введенная в ту же ячейку D7 на листе "свод", позволяет посчитать несколько одинаковых артикулов в одной ячейке листа "заказы":
[vba]
Код
=ArrayFormula(SUM(COUNTIF(FILTER('переходник'!$A:$A; 'переходник'!$B:$B=D$4);
TRANSPOSE(SPLIT(JOIN(" ";TRIM(REGEXREPLACE(
FILTER('заказы'!$B:$B;'заказы'!$A:$A>=$A7;'заказы'!$A:$A<=$B7);"\D";" ")));" ")))))
[/vba]
Здесь сначала по датам выбираются нужные ячейки листа "заказы" (без учета артикулов). Далее эти ячейки очищаются от "мусора", оставляя в них только артикулы, разделенные пробелом. Затем с помощью JOIN все ячейки сливаются в одну длинную строку, которая содержит все артикулы (из нескольких строк "заказов" за период), разделенные пробелом. После этого с помощью SPLIT по пробелу строка превращается в горизонтальный массив, который затем разворачивается вертикально с помощью TRANSPOSE. Таким образом, в считающую функцию COUNTIF поступают два вертикальных массива: массив артикулов для выбранной категории с листа "переходник" и "очищенный" массив артикулов, встретившихся во всех строках "заказов" за заданный период. Окончательно функция SUM "собирает" все частные результаты COUNTIF по элементам "очищенного" массива артикулов.


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 12.12.2021, 04:39
 
Ответить
СообщениеФормула для ячейки D7 на листе "свод" - формула копируема (протягиваема) на остальные зеленые ячейки этого листа:
[vba]
Код
=ArrayFormula(SUM(COUNTIFS('заказы'!$B:$B;"*"&FILTER('переходник'!$A:$A; 'переходник'!$B:$B=D$4)&"*";'заказы'!$A:$A;">="&$A7;'заказы'!$A:$A;"<="&$B7)))
[/vba]
ОГРАНИЧЕНИЕ: Предполагается уникальность артикула в одной ячейке, т.е. конкретный артикул может встретиться в одной ячейке только один раз (возможно, среди других артикулов). Если в одной ячейке один и тот же артикул встретится более одного раза, то формула все равно учтет его только один раз.

[p.s.]ПРЕОДОЛЕНИЕ ОГРАНИЧЕНИЯ[/p.s.]
Следующая формула, введенная в ту же ячейку D7 на листе "свод", позволяет посчитать несколько одинаковых артикулов в одной ячейке листа "заказы":
[vba]
Код
=ArrayFormula(SUM(COUNTIF(FILTER('переходник'!$A:$A; 'переходник'!$B:$B=D$4);
TRANSPOSE(SPLIT(JOIN(" ";TRIM(REGEXREPLACE(
FILTER('заказы'!$B:$B;'заказы'!$A:$A>=$A7;'заказы'!$A:$A<=$B7);"\D";" ")));" ")))))
[/vba]
Здесь сначала по датам выбираются нужные ячейки листа "заказы" (без учета артикулов). Далее эти ячейки очищаются от "мусора", оставляя в них только артикулы, разделенные пробелом. Затем с помощью JOIN все ячейки сливаются в одну длинную строку, которая содержит все артикулы (из нескольких строк "заказов" за период), разделенные пробелом. После этого с помощью SPLIT по пробелу строка превращается в горизонтальный массив, который затем разворачивается вертикально с помощью TRANSPOSE. Таким образом, в считающую функцию COUNTIF поступают два вертикальных массива: массив артикулов для выбранной категории с листа "переходник" и "очищенный" массив артикулов, встретившихся во всех строках "заказов" за заданный период. Окончательно функция SUM "собирает" все частные результаты COUNTIF по элементам "очищенного" массива артикулов.

Автор - Gustav
Дата добавления - 12.12.2021 в 02:47
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Вытянуть коллич. из заказа, если в одной ячейке несколько шт (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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