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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма по двум критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма по двум критериям (Формулы/Formulas)
Сумма по двум критериям
RAN Дата: Понедельник, 01.04.2019, 13:57 | Сообщение № 1
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Мяв!
Сейчас использую формулу вида
Код
=СУММЕСЛИМН($L:$L;$J:$J;$A3;$K:$K;B$1)

Все хорошо считает, одна беда - этих формул в файле 365*50 штук, и данных в базе уже 13000 строк, так что пересчет длится около 2 минут.
Сокращение диапазона со столбца до 200000 строк, если и дало сокращение времени, то на какие-то жалкие 5 секунд.
Задача - подсчитать загрузку каждого из станков за определенную дату.
В базе на одну дату для одного станка может быть не определенное количество записей.
Общее количество записей в базе увеличится, думаю, тысяч до 50.
После ввода данных база сортируется по дате и станку.
Есть-ли альтернативный шустрый вариант формулы?
К сообщению приложен файл: 2588582.xlsx (10.7 Kb)

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМяв!
Сейчас использую формулу вида
Код
=СУММЕСЛИМН($L:$L;$J:$J;$A3;$K:$K;B$1)

Все хорошо считает, одна беда - этих формул в файле 365*50 штук, и данных в базе уже 13000 строк, так что пересчет длится около 2 минут.
Сокращение диапазона со столбца до 200000 строк, если и дало сокращение времени, то на какие-то жалкие 5 секунд.
Задача - подсчитать загрузку каждого из станков за определенную дату.
В базе на одну дату для одного станка может быть не определенное количество записей.
Общее количество записей в базе увеличится, думаю, тысяч до 50.
После ввода данных база сортируется по дате и станку.
Есть-ли альтернативный шустрый вариант формулы?
Автор - RAN
Дата добавления - 01.04.2019 в 13:57
китин Дата: Понедельник, 01.04.2019, 14:04 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Андрей а диапазоны так и задаются в формуле
Код
$L:$L
?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеАндрей а диапазоны так и задаются в формуле
Код
$L:$L
?
Автор - китин
Дата добавления - 01.04.2019 в 14:04
and_evg Дата: Понедельник, 01.04.2019, 14:17 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 452
Репутация: 78 ±
Замечаний: 0% ±

Excel 2007
RAN, А сводная таблица не подойдет?
 
Ответить
СообщениеRAN, А сводная таблица не подойдет?
Автор - and_evg
Дата добавления - 01.04.2019 в 14:17
_Boroda_ Дата: Понедельник, 01.04.2019, 14:56 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно усечь диапазоны по датам и по заполнению таблицы. Не знаю, насколько это поможет на реальной таблице
Код
=СУММЕСЛИ(ИНДЕКС($K:$K;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)):ИНДЕКС($K:$K;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)+СЧЁТЕСЛИ($J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));$A3)-1);B$1;ИНДЕКС($L:$L;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)):ИНДЕКС($L:$L;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)+СЧЁТЕСЛИ($J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));$A3)-1))

Можно внутри усеченных по датам диапазонов усечь еще и по наименованию, тогда формула увеличится раза в три, а диапазон расчета уменьшится.
Если первая формула поможет, то можно будет и вторую потом написать
От ошибок в ЕСЛИОШИБКА обернуть можно

Еще помогли бы допячейки. Не знаю, можно их использовать или нет

Часть формул можно в имена засунуть, но это немного замедлит работу
К сообщению приложен файл: 2588582_1.xlsx (11.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеМожно усечь диапазоны по датам и по заполнению таблицы. Не знаю, насколько это поможет на реальной таблице
Код
=СУММЕСЛИ(ИНДЕКС($K:$K;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)):ИНДЕКС($K:$K;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)+СЧЁТЕСЛИ($J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));$A3)-1);B$1;ИНДЕКС($L:$L;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)):ИНДЕКС($L:$L;ПОИСКПОЗ($A3;$J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));)+СЧЁТЕСЛИ($J$1:ИНДЕКС($J:$J;СЧЁТЗ(K:K));$A3)-1))

Можно внутри усеченных по датам диапазонов усечь еще и по наименованию, тогда формула увеличится раза в три, а диапазон расчета уменьшится.
Если первая формула поможет, то можно будет и вторую потом написать
От ошибок в ЕСЛИОШИБКА обернуть можно

Еще помогли бы допячейки. Не знаю, можно их использовать или нет

Часть формул можно в имена засунуть, но это немного замедлит работу
Автор - _Boroda_
Дата добавления - 01.04.2019 в 14:56
Russel Дата: Понедельник, 01.04.2019, 16:16 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
RAN, согласен с and_evg, Вам нужно смотреть в сторону сводных таблиц.
К сообщению приложен файл: 2917402.xlsx (15.2 Kb)

QIWI 9173973973
 
Ответить
СообщениеRAN, согласен с and_evg, Вам нужно смотреть в сторону сводных таблиц.
Автор - Russel
Дата добавления - 01.04.2019 в 16:16
bmv98rus Дата: Понедельник, 01.04.2019, 17:35 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
RAN, В целом я за сводную и если нужно форматировать по иному , то , то
Код
=GETPIVOTDATA("время";$Q$1;"дата";$A3;"наим";B$1)

см Лист1 (2)
но может и такое подойдет и ускорит
Код
=SUMIF(INDEX($K:$K;MATCH($A3;$J:$J;)):INDEX($K:$K;MATCH($A3;$J:$J));B$1;INDEX($L:$L;MATCH($A3;$J:$J;)):INDEX($L:$L;MATCH($A3;$J:$J)))

см Лист1 хотя мне кажется это вариант Александра без изысков
Саша, мне кажется все многочисленные каунты в результате компенсируют выгоду от уменьшения диапазона. Можно в половину уменьшить,
Код
=SUMIF(INDEX($K:$K;MATCH($A3;$J:$J;)):INDEX($K:$K;MATCH($A3;$J$1:INDEX($J:$J;COUNT($J:$J)+2)));B$1;INDEX($L:$L;MATCH($A3;$J:$J;)):INDEX($L:$L;MATCH($A3;$J$1:INDEX($J:$J;COUNT($J:$J)+2))))

а лучше COUNT($J:$J)+2 в отдельную ячейку для разового расчета.
К сообщению приложен файл: Copy_of_783.xlsx (23.9 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 01.04.2019, 17:47
 
Ответить
СообщениеRAN, В целом я за сводную и если нужно форматировать по иному , то , то
Код
=GETPIVOTDATA("время";$Q$1;"дата";$A3;"наим";B$1)

см Лист1 (2)
но может и такое подойдет и ускорит
Код
=SUMIF(INDEX($K:$K;MATCH($A3;$J:$J;)):INDEX($K:$K;MATCH($A3;$J:$J));B$1;INDEX($L:$L;MATCH($A3;$J:$J;)):INDEX($L:$L;MATCH($A3;$J:$J)))

см Лист1 хотя мне кажется это вариант Александра без изысков
Саша, мне кажется все многочисленные каунты в результате компенсируют выгоду от уменьшения диапазона. Можно в половину уменьшить,
Код
=SUMIF(INDEX($K:$K;MATCH($A3;$J:$J;)):INDEX($K:$K;MATCH($A3;$J$1:INDEX($J:$J;COUNT($J:$J)+2)));B$1;INDEX($L:$L;MATCH($A3;$J:$J;)):INDEX($L:$L;MATCH($A3;$J$1:INDEX($J:$J;COUNT($J:$J)+2))))

а лучше COUNT($J:$J)+2 в отдельную ячейку для разового расчета.
Автор - bmv98rus
Дата добавления - 01.04.2019 в 17:35
RAN Дата: Понедельник, 01.04.2019, 18:19 | Сообщение № 7
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Получение этой суммы - не самоцель. Это значение выступает в качестве исходного для других формул (допячейка). Поэтому, как тут прикрутить сводную, слабо представляю. Еще допячеек навесить не проблема.
В целом задача - получить на отдельном листе гистограммы ежедневной загрузки оборудования, при этом, если в четверг было напланировано работы на 3 дня, часть работы переползает на пятницу, а остальное - вообще на следующую неделю, и в гистограмме отображается свободный день - вторник.
Доберусь до дома, погляжу предложенные варианты.
PS Макросы, сводная таблица и сводная диаграмма в файле в наличии.

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеПолучение этой суммы - не самоцель. Это значение выступает в качестве исходного для других формул (допячейка). Поэтому, как тут прикрутить сводную, слабо представляю. Еще допячеек навесить не проблема.
В целом задача - получить на отдельном листе гистограммы ежедневной загрузки оборудования, при этом, если в четверг было напланировано работы на 3 дня, часть работы переползает на пятницу, а остальное - вообще на следующую неделю, и в гистограмме отображается свободный день - вторник.
Доберусь до дома, погляжу предложенные варианты.
PS Макросы, сводная таблица и сводная диаграмма в файле в наличии.
Автор - RAN
Дата добавления - 01.04.2019 в 18:19
_Boroda_ Дата: Понедельник, 01.04.2019, 18:29 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А что ты макросом тогда не тянешь? И вообще всё макросами не делаешь?

Похоже учетку RAN взломали :D

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА что ты макросом тогда не тянешь? И вообще всё макросами не делаешь?

Похоже учетку RAN взломали :D
Автор - _Boroda_
Дата добавления - 01.04.2019 в 18:29
bmv98rus Дата: Понедельник, 01.04.2019, 18:45 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 01.04.2019 в 18:29, в сообщении № 8 ()
RAN взломали
март закончился, день дурака - вот и придуривается котяра.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата _Boroda_, 01.04.2019 в 18:29, в сообщении № 8 ()
RAN взломали
март закончился, день дурака - вот и придуривается котяра.
Автор - bmv98rus
Дата добавления - 01.04.2019 в 18:45
RAN Дата: Понедельник, 01.04.2019, 23:24 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
В порядке поступления
Событийный макрос на такой объем, тоже не медовый пряник.
В таких случаях предпочитаю не сложные формулы. Ежели бы потребовалась формула уровня "штурм", даже не задумываясь, сделал бы макросом (или вас запытал).
Придуривается - это только если в той части, что не верил в практическое решение. вопроса.
По итогам тестирования формулы из №4 - 4 минуты, 50 процентов выполнения (СУММЕСЛИМН пересчитывалась за 2).
Моя попытка применения СМЕЩ() и СЧЁТЗ() тоже не дала положительных результатов.

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеВ порядке поступления
Событийный макрос на такой объем, тоже не медовый пряник.
В таких случаях предпочитаю не сложные формулы. Ежели бы потребовалась формула уровня "штурм", даже не задумываясь, сделал бы макросом (или вас запытал).
Придуривается - это только если в той части, что не верил в практическое решение. вопроса.
По итогам тестирования формулы из №4 - 4 минуты, 50 процентов выполнения (СУММЕСЛИМН пересчитывалась за 2).
Моя попытка применения СМЕЩ() и СЧЁТЗ() тоже не дала положительных результатов.
Автор - RAN
Дата добавления - 01.04.2019 в 23:24
bmv98rus Дата: Вторник, 02.04.2019, 00:21 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
RAN, Андрей, как я понял, мои ты не проверял.
Ну если так все запущено и много, может надо вот так разбить?
К сообщению приложен файл: Copy_of_783_1.xlsx (11.3 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеRAN, Андрей, как я понял, мои ты не проверял.
Ну если так все запущено и много, может надо вот так разбить?
Автор - bmv98rus
Дата добавления - 02.04.2019 в 00:21
RAN Дата: Вторник, 02.04.2019, 10:30 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Миш, интересный вариант, но разбивается об отсутствие данных в базе за некое число.
А, как сам понимаешь, станки могут и простаивать по ряду причин. Да и в выходные они тоже не работают.
Пожалуй, оставлю как есть. Всем мУрси.

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеМиш, интересный вариант, но разбивается об отсутствие данных в базе за некое число.
А, как сам понимаешь, станки могут и простаивать по ряду причин. Да и в выходные они тоже не работают.
Пожалуй, оставлю как есть. Всем мУрси.
Автор - RAN
Дата добавления - 02.04.2019 в 10:30
_Boroda_ Дата: Вторник, 02.04.2019, 10:40 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А если с двумя допами?

* Добавлено. Посмотрел сейчас у Михаила, принцип тот же. ЕСЛИОШИБКА добавить и все прекрасно будет
Тут нужно смотреть на скорость - поможет ли это.
К сообщению приложен файл: 2588582-1-2.xlsx (11.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА если с двумя допами?

* Добавлено. Посмотрел сейчас у Михаила, принцип тот же. ЕСЛИОШИБКА добавить и все прекрасно будет
Тут нужно смотреть на скорость - поможет ли это.
Автор - _Boroda_
Дата добавления - 02.04.2019 в 10:40
RAN Дата: Вторник, 02.04.2019, 11:36 | Сообщение № 14
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Саш, этот вариант пошустрее. 1,5 минуты против 2.
Осталось решить, стоит ли такое ускорение такого усложнения файла.

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеСаш, этот вариант пошустрее. 1,5 минуты против 2.
Осталось решить, стоит ли такое ускорение такого усложнения файла.
Автор - RAN
Дата добавления - 02.04.2019 в 11:36
_Boroda_ Дата: Вторник, 02.04.2019, 11:56 | Сообщение № 15
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А если эти допы заполнять макросом?
И да, у тебя всегда меняется весь диапазон базы? Или просто дополняются данные снизу? Если дополняются, то макросу не нужно все пересчитывать, а только добавленные даты

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА если эти допы заполнять макросом?
И да, у тебя всегда меняется весь диапазон базы? Или просто дополняются данные снизу? Если дополняются, то макросу не нужно все пересчитывать, а только добавленные даты
Автор - _Boroda_
Дата добавления - 02.04.2019 в 11:56
bmv98rus Дата: Вторник, 02.04.2019, 12:12 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата RAN, 02.04.2019 в 10:30, в сообщении № 12 ()
но разбивается об отсутствие данных в базе за некое число
ну так тогда,

а сводную из №6 и ранее не пробовал?
К сообщению приложен файл: Copy_of_783_2.xlsx (16.5 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 02.04.2019, 12:14
 
Ответить
Сообщение
Цитата RAN, 02.04.2019 в 10:30, в сообщении № 12 ()
но разбивается об отсутствие данных в базе за некое число
ну так тогда,

а сводную из №6 и ранее не пробовал?
Автор - bmv98rus
Дата добавления - 02.04.2019 в 12:12
RAN Дата: Вторник, 02.04.2019, 13:38 | Сообщение № 17
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Саш, один из вопросов был - почему печатает не правильно, когда я лезу в базу, и меняю там дату. Так что отслеживать нужно каждый чих.
Цитата bmv98rus, 02.04.2019 в 12:12, в сообщении № 16 ()
а сводную из №6 и ранее не пробовал

сделал выдержку из реального файла (структура, частично функционал)
Места для сводной не наблюдаю
Миш, еще раз мУрси за идею с именами.
К сообщению приложен файл: 0149842.xlsx (23.9 Kb)

Быть или не быть, вот в чем загвоздка!
 
Ответить
СообщениеСаш, один из вопросов был - почему печатает не правильно, когда я лезу в базу, и меняю там дату. Так что отслеживать нужно каждый чих.
Цитата bmv98rus, 02.04.2019 в 12:12, в сообщении № 16 ()
а сводную из №6 и ранее не пробовал

сделал выдержку из реального файла (структура, частично функционал)
Места для сводной не наблюдаю
Миш, еще раз мУрси за идею с именами.
Автор - RAN
Дата добавления - 02.04.2019 в 13:38
bmv98rus Дата: Вторник, 02.04.2019, 13:55 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата RAN, 02.04.2019 в 13:38, в сообщении № 17 ()
Места для сводной не наблюдаю

она как промежуток, но надо смотреть как по чиху она будет обновляться и потом из нее данные браться.

Цитата RAN, 02.04.2019 в 13:38, в сообщении № 17 ()
за идею с именами
эээ пож. а это про что?
К сообщению приложен файл: Copy_of_783_3.xlsx (31.9 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата RAN, 02.04.2019 в 13:38, в сообщении № 17 ()
Места для сводной не наблюдаю

она как промежуток, но надо смотреть как по чиху она будет обновляться и потом из нее данные браться.

Цитата RAN, 02.04.2019 в 13:38, в сообщении № 17 ()
за идею с именами
эээ пож. а это про что?
Автор - bmv98rus
Дата добавления - 02.04.2019 в 13:55
RAN Дата: Вторник, 02.04.2019, 14:24 | Сообщение № 19
Группа: Друзья
Ранг: Экселист
Сообщений: 5660
Репутация: 1163 ±
Замечаний: 0% ±

2010
Имя для гистограмм
PS могёт быть...

Быть или не быть, вот в чем загвоздка!

Сообщение отредактировал RAN - Вторник, 02.04.2019, 14:30
 
Ответить
СообщениеИмя для гистограмм
PS могёт быть...
Автор - RAN
Дата добавления - 02.04.2019 в 14:24
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма по двум критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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