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

Вход

Регистрация

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

 

= Мир MS Excel/Альтернатива функции СЧЁТЕСЛИМН для оптимизации работы файла - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Альтернатива функции СЧЁТЕСЛИМН для оптимизации работы файла (Формулы/Formulas)
Альтернатива функции СЧЁТЕСЛИМН для оптимизации работы файла
monstr_ork Дата: Воскресенье, 15.04.2018, 23:54 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 92
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте форумчане.
Помогите пожалуйста с формулой, а точнее с ее оптимизация.
файл-пример во вложении.
На листе отчет происходит подсчет "Значений" по определенным условиям, чтобы совпадала категория и плюсом ко всему подсчет идет из 2-3 столбцов (в примере указал какие, но это примерно) и это все надо сосчитать со всех листов.
У меня получилась вот такая формула :
Код
=СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$Q:$Q;Отчет!C$2)+СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$R:$R;Отчет!C$2)+СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$S:$S;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$P:$P;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$P:$P;Отчет!C$2)+СЧЁТЕСЛИМН(Лист4!$J:$J;Отчет!$A3;Лист4!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист4!$J:$J;Отчет!$A3;Лист4!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$P:$P;Отчет!C$2)

А если добавить еще одно условие во второй таблице то и во всем "мутант" какой-то получает:
Код
=СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$Q:$Q;Отчет!C$21)+СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$R:$R;Отчет!C$21)+СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$S:$S;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$P:$P;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$P:$P;Отчет!C$21)+СЧЁТЕСЛИМН(Лист4!$B:$B;Отчет!$B$21;Лист4!$J:$J;Отчет!$A22;Лист4!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист4!$B:$B;Отчет!$B$21;Лист4!$J:$J;Отчет!$A22;Лист4!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$P:$P;Отчет!C$21)

Из-за этого в файле пришлось отключить автоматический подсчет.
Может быть подскажите как можно упростить формулу?
К сообщению приложен файл: 5064257.xlsx(63.0 Kb)


Сообщение отредактировал monstr_ork - Понедельник, 16.04.2018, 09:08
 
Ответить
СообщениеЗдравствуйте форумчане.
Помогите пожалуйста с формулой, а точнее с ее оптимизация.
файл-пример во вложении.
На листе отчет происходит подсчет "Значений" по определенным условиям, чтобы совпадала категория и плюсом ко всему подсчет идет из 2-3 столбцов (в примере указал какие, но это примерно) и это все надо сосчитать со всех листов.
У меня получилась вот такая формула :
Код
=СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$Q:$Q;Отчет!C$2)+СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$R:$R;Отчет!C$2)+СЧЁТЕСЛИМН(Лист1!$K:$K;Отчет!$A3;Лист1!$S:$S;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист2!$K:$K;Отчет!$A3;Лист2!$P:$P;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист3!$J:$J;Отчет!$A3;Лист3!$P:$P;Отчет!C$2)+СЧЁТЕСЛИМН(Лист4!$J:$J;Отчет!$A3;Лист4!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист4!$J:$J;Отчет!$A3;Лист4!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$N:$N;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$O:$O;Отчет!C$2)+СЧЁТЕСЛИМН(Лист5!$J:$J;Отчет!$A3;Лист5!$P:$P;Отчет!C$2)

А если добавить еще одно условие во второй таблице то и во всем "мутант" какой-то получает:
Код
=СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$Q:$Q;Отчет!C$21)+СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$R:$R;Отчет!C$21)+СЧЁТЕСЛИМН(Лист1!$C:$C;$B$21;Лист1!$K:$K;Отчет!$A22;Лист1!$S:$S;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист2!$C:$C;Отчет!$B$21;Лист2!$K:$K;Отчет!$A22;Лист2!$P:$P;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист3!$B:$B;Отчет!$B$21;Лист3!$J:$J;Отчет!$A22;Лист3!$P:$P;Отчет!C$21)+СЧЁТЕСЛИМН(Лист4!$B:$B;Отчет!$B$21;Лист4!$J:$J;Отчет!$A22;Лист4!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист4!$B:$B;Отчет!$B$21;Лист4!$J:$J;Отчет!$A22;Лист4!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$N:$N;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$O:$O;Отчет!C$21)+СЧЁТЕСЛИМН(Лист5!$B:$B;Отчет!$B$21;Лист5!$J:$J;Отчет!$A22;Лист5!$P:$P;Отчет!C$21)

Из-за этого в файле пришлось отключить автоматический подсчет.
Может быть подскажите как можно упростить формулу?

Автор - monstr_ork
Дата добавления - 15.04.2018 в 23:54
Pelena Дата: Понедельник, 16.04.2018, 06:30 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 13298
Репутация: 2927 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Дайте теме более конкретное название в соответствии с Правилами форума


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеДайте теме более конкретное название в соответствии с Правилами форума

Автор - Pelena
Дата добавления - 16.04.2018 в 06:30
monstr_ork Дата: Понедельник, 16.04.2018, 09:09 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 92
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena, Четно говоря даже и не знаю как назвать, исправил кончено, но не знаю на сколько это будет точно.
 
Ответить
СообщениеPelena, Четно говоря даже и не знаю как назвать, исправил кончено, но не знаю на сколько это будет точно.

Автор - monstr_ork
Дата добавления - 16.04.2018 в 09:09
Karataev Дата: Понедельник, 16.04.2018, 09:20 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1265
Репутация: 487 ±
Замечаний: 0% ±

Excel
Возможно проблема с использованием "СЧЁТЕСЛИМН" в том, что "глупый" Excel анализирует весь столбец целиком, вместо того, чтобы искать последнюю строку с данными. А Вы указываете столбцы целиком и Excel просматривает все 1 млн. ячеек.
В Вашем случае можно воспользоваться тем, что Вы используете умные таблицы. Указывайте в формулах диапазоны умных таблиц:
Код
=СЧЁТЕСЛИ(Таблица1[Столбец10];Отчет!A3)

В этом случае функция "СЧЁТЕСЛИ" будет просматривать не 1 млн. ячеек в столбце, а столько ячеек, сколько строк в умной таблице.
Чтобы в формуле указать столбец умной таблицы, во время написания формулы, щелкните по заголовку умной таблицы и Excel подставит "Таблица1[Столбец10]".


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288
 
Ответить
СообщениеВозможно проблема с использованием "СЧЁТЕСЛИМН" в том, что "глупый" Excel анализирует весь столбец целиком, вместо того, чтобы искать последнюю строку с данными. А Вы указываете столбцы целиком и Excel просматривает все 1 млн. ячеек.
В Вашем случае можно воспользоваться тем, что Вы используете умные таблицы. Указывайте в формулах диапазоны умных таблиц:
Код
=СЧЁТЕСЛИ(Таблица1[Столбец10];Отчет!A3)

В этом случае функция "СЧЁТЕСЛИ" будет просматривать не 1 млн. ячеек в столбце, а столько ячеек, сколько строк в умной таблице.
Чтобы в формуле указать столбец умной таблицы, во время написания формулы, щелкните по заголовку умной таблицы и Excel подставит "Таблица1[Столбец10]".

Автор - Karataev
Дата добавления - 16.04.2018 в 09:20
monstr_ork Дата: Понедельник, 16.04.2018, 11:30 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 92
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - monstr_ork
Дата добавления - 16.04.2018 в 11:30
sboy Дата: Понедельник, 16.04.2018, 11:43 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2224
Репутация: 631 ±
Замечаний: 0% ±

Excel 2010
но их и закрепить то нельзя

можно :)
Код
=СЧЁТЕСЛИ(Таблица1[[Столбец10]:[Столбец10]];Отчет!A3)
 
Ответить
Сообщение
но их и закрепить то нельзя

можно :)
Код
=СЧЁТЕСЛИ(Таблица1[[Столбец10]:[Столбец10]];Отчет!A3)

Автор - sboy
Дата добавления - 16.04.2018 в 11:43
Karataev Дата: Понедельник, 16.04.2018, 12:52 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1265
Репутация: 487 ±
Замечаний: 0% ±

Excel
monstr_ork, формулы по идее один раз делаются и затем пользователь больше не касается формул.
Наверное Вы пишите про копирование формулы вправо в столбцы. Если тянуть формулу вниз, проблем нет.
Ну или просто замените целые столбцы на фрагменты. Например Вам известно, что на листе не будет больше 10000 строк, то укажите строки со 2 по 10000:
Код
=СЧЁТЕСЛИМН(Лист1!$K2:$K10000;


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288


Сообщение отредактировал Karataev - Понедельник, 16.04.2018, 12:59
 
Ответить
Сообщениеmonstr_ork, формулы по идее один раз делаются и затем пользователь больше не касается формул.
Наверное Вы пишите про копирование формулы вправо в столбцы. Если тянуть формулу вниз, проблем нет.
Ну или просто замените целые столбцы на фрагменты. Например Вам известно, что на листе не будет больше 10000 строк, то укажите строки со 2 по 10000:
Код
=СЧЁТЕСЛИМН(Лист1!$K2:$K10000;

Автор - Karataev
Дата добавления - 16.04.2018 в 12:52
monstr_ork Дата: Вторник, 17.04.2018, 08:20 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 92
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо всем за помощь. Хотел еще спросить, возможно ли использование других функция в моем случае? А длинна кода меня не радует, да и для подсчета в другом столбце значений, приходит переписывать одно и тоже и только последняя часть отличается?
 
Ответить
СообщениеСпасибо всем за помощь. Хотел еще спросить, возможно ли использование других функция в моем случае? А длинна кода меня не радует, да и для подсчета в другом столбце значений, приходит переписывать одно и тоже и только последняя часть отличается?

Автор - monstr_ork
Дата добавления - 17.04.2018 в 08:20
sboy Дата: Вторник, 17.04.2018, 09:04 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2224
Репутация: 631 ±
Замечаний: 0% ±

Excel 2010
возможно ли использование других функция в моем случае?
Можно попробовать собрать все с помощью Power Query, но в примере данных нет, не начем потренироваться
 
Ответить
Сообщение
возможно ли использование других функция в моем случае?
Можно попробовать собрать все с помощью Power Query, но в примере данных нет, не начем потренироваться

Автор - sboy
Дата добавления - 17.04.2018 в 09:04
monstr_ork Дата: Вторник, 17.04.2018, 11:16 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 92
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sboy, А Power Query в онлайн все считает или каждый раз нужно таблицу составлять?
 
Ответить
Сообщениеsboy, А Power Query в онлайн все считает или каждый раз нужно таблицу составлять?

Автор - monstr_ork
Дата добавления - 17.04.2018 в 11:16
sboy Дата: Вторник, 17.04.2018, 11:42 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2224
Репутация: 631 ±
Замечаний: 0% ±

Excel 2010
Один раз составить, потом только нажимать обновить (примерно как в сводной)
 
Ответить
СообщениеОдин раз составить, потом только нажимать обновить (примерно как в сводной)

Автор - sboy
Дата добавления - 17.04.2018 в 11:42
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Альтернатива функции СЧЁТЕСЛИМН для оптимизации работы файла (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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