Подскажите пожалуйста. Есть большая база отфильтровываю ее по нужным критериям. на другом листе формула "счетесли" обрабатывает не видимый массив данных, а полный. Как сделать чтоб она обрабатывала только видимый?
лист si-2012m30 сама база лист экспорт g5 первая расчетная ячейка которая должна считать видимые отфильтрованные данные
Подскажите пожалуйста. Есть большая база отфильтровываю ее по нужным критериям. на другом листе формула "счетесли" обрабатывает не видимый массив данных, а полный. Как сделать чтоб она обрабатывала только видимый?
лист si-2012m30 сама база лист экспорт g5 первая расчетная ячейка которая должна считать видимые отфильтрованные данныеreborn
тему http://www.excelworld.ru/forum/2-3434-1#36901 смотрел функции предварительный.итог тоже смотрел, но там нет обработки фукнции "счетесли" а как считать с помощью других я пока не додумался
Подсчет ячеек по двум параметрам. Можете добавить свои.
Вообще не понял причем тут ячейка $B$2
в текущем раскладе примера исходя из данных первого листа значения строки 5 и 12 должны быть следующими: для 5й строки 3 1 1 0 0 0 0 0 0 0 0 0 для 12 строки 2 1 0 0 0 0 0 0 0 0 0 0
и не совсем понял как сумма произведений мне поможет в подсчете рядов исходя из условий
Подсчет ячеек по двум параметрам. Можете добавить свои.
Вообще не понял причем тут ячейка $B$2
в текущем раскладе примера исходя из данных первого листа значения строки 5 и 12 должны быть следующими: для 5й строки 3 1 1 0 0 0 0 0 0 0 0 0 для 12 строки 2 1 0 0 0 0 0 0 0 0 0 0
и не совсем понял как сумма произведений мне поможет в подсчете рядов исходя из условийreborn
Сообщение отредактировал reborn - Среда, 16.10.2013, 18:29
По каким критериям вы фильтруете диапазон, по тем критериям и ведите подсчет. Используйте СУММЕСЛИМН или СУММПРОИЗВ.
как использовать сумму произведений в данном случае я вообще не представляю суммеслимн да допустим я запользную, но разве при фильтрации данных автофильтром, скрытые строки не будут считаться? у меня проблема не в том что я не могу посчитать, а в том эксель и невидимые строки считает, а мне надо чтоб считал только видимые отфильтрованные из общей базы
По каким критериям вы фильтруете диапазон, по тем критериям и ведите подсчет. Используйте СУММЕСЛИМН или СУММПРОИЗВ.
как использовать сумму произведений в данном случае я вообще не представляю суммеслимн да допустим я запользную, но разве при фильтрации данных автофильтром, скрытые строки не будут считаться? у меня проблема не в том что я не могу посчитать, а в том эксель и невидимые строки считает, а мне надо чтоб считал только видимые отфильтрованные из общей базыreborn
Объяснялка формулы. Делим на кусочки 1. СЧЁТЗ('si-2012m30'!A:A) – считает количество заполненных ячеек в столбце А (мы на него будем много потом ссылаться, поэтому обзовем его для краткости ффф); 2. СМЕЩ('si-2012m30'!$R$1;;;СЧЁТЗ('si-2012m30'!A:A)) – дает диапазон, начинающийся с R1 и высотой ффф; 3. СМЕЩ('si-2012m30'!$R$1;;;СЧЁТЗ('si-2012m30'!A:A))=G4 – приравниваем диапазон из п.2 к значению G4. Если равно, то будет ИСТИНА, если не равно – то ЛОЖЬ. Итог – массив из ИСТИНА и ЛОЖЬ с количеством значений, равным ффф; 4. ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)) – получаем ссылку на ячейки А1:Аффф; 5. СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A))) – дает нам массив 1;2;3;…;ффф; 6. СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1 – дает массив 0;1;2;,,,;ффф; 7. СМЕЩ('si-2012m30'!$A$1;СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1;) – последовательно дает нам значения ячеек, начиная с А1 и смещаясь вниз на числа из п.6 (то есть – А1; А2;…;Аффф); 8. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ('si-2012m30'!$A$1;СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1;)) – дает массив из нулей и единиц. Нули получаются, если а) в ячейках из п.7 не число – у нас это только шапка таблицы, а она нам не нужна для расчетов (про первый аргумент в ПРОМЕЖУТОЧНЫЕ.ИТОГИ см. справку по функции, кстати, в нашу формулу можно поставить не 2, а 3) и б) ячейка (вернее, вся строка) скрыта. Итог – массив из нулей и единиц, где 0 появляется тогда, когда скрыта соответствующая по порядку ячейка; 9. перемножив результаты п.3 и п.8, получим массив из нулей и единиц. Единица получается только в том случае, если совпадают 2 условия: значение из столбца R равно значению ячейки G4 и соответствующая строка не скрыта; 10. складываем полученное в п.9 с помощью СУММПРОИЗВ.
Так нужно? Для статической таблицы (всегда 18 строк)
Объяснялка формулы. Делим на кусочки 1. СЧЁТЗ('si-2012m30'!A:A) – считает количество заполненных ячеек в столбце А (мы на него будем много потом ссылаться, поэтому обзовем его для краткости ффф); 2. СМЕЩ('si-2012m30'!$R$1;;;СЧЁТЗ('si-2012m30'!A:A)) – дает диапазон, начинающийся с R1 и высотой ффф; 3. СМЕЩ('si-2012m30'!$R$1;;;СЧЁТЗ('si-2012m30'!A:A))=G4 – приравниваем диапазон из п.2 к значению G4. Если равно, то будет ИСТИНА, если не равно – то ЛОЖЬ. Итог – массив из ИСТИНА и ЛОЖЬ с количеством значений, равным ффф; 4. ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)) – получаем ссылку на ячейки А1:Аффф; 5. СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A))) – дает нам массив 1;2;3;…;ффф; 6. СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1 – дает массив 0;1;2;,,,;ффф; 7. СМЕЩ('si-2012m30'!$A$1;СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1;) – последовательно дает нам значения ячеек, начиная с А1 и смещаясь вниз на числа из п.6 (то есть – А1; А2;…;Аффф); 8. ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;СМЕЩ('si-2012m30'!$A$1;СТРОКА(ДВССЫЛ("A1:A"&СЧЁТЗ('si-2012m30'!A:A)))-1;)) – дает массив из нулей и единиц. Нули получаются, если а) в ячейках из п.7 не число – у нас это только шапка таблицы, а она нам не нужна для расчетов (про первый аргумент в ПРОМЕЖУТОЧНЫЕ.ИТОГИ см. справку по функции, кстати, в нашу формулу можно поставить не 2, а 3) и б) ячейка (вернее, вся строка) скрыта. Итог – массив из нулей и единиц, где 0 появляется тогда, когда скрыта соответствующая по порядку ячейка; 9. перемножив результаты п.3 и п.8, получим массив из нулей и единиц. Единица получается только в том случае, если совпадают 2 условия: значение из столбца R равно значению ячейки G4 и соответствующая строка не скрыта; 10. складываем полученное в п.9 с помощью СУММПРОИЗВ._Boroda_
пробую растянуть формулу до -13 и добавить несколько строк, выдает неправильный счет для статической таблицы (первая формула) и н.д. для динамической
пробую растянуть формулу до -13 и добавить несколько строк, выдает неправильный счет для статической таблицы (первая формула) и н.д. для динамическойreborn
Сообщение отредактировал reborn - Среда, 16.10.2013, 20:08
Добрый день! Подскажите, пожалуйста, есть ли решение моей задачи (проблемы)? Задача: определение максимального диапазона пустых ячеек в столбце видимых данных, то есть формула "=МАКС(ЧАСТОТА(СТРОКА(AJ5:AJ7484);(ЕЧИСЛО(AJ5:AJ7484))*СТРОКА(AJ5:AJ7484)))-1" определяет максимальный диапазон пустых ячеек в столбце AJ5:AJ7484 (количество пустых ячеек подряд). Мне необходимо считать максимальный диапазон пустых ячеек только в отфильтрованных (видимых) строках столбца AJ5:AJ7484.
Добрый день! Подскажите, пожалуйста, есть ли решение моей задачи (проблемы)? Задача: определение максимального диапазона пустых ячеек в столбце видимых данных, то есть формула "=МАКС(ЧАСТОТА(СТРОКА(AJ5:AJ7484);(ЕЧИСЛО(AJ5:AJ7484))*СТРОКА(AJ5:AJ7484)))-1" определяет максимальный диапазон пустых ячеек в столбце AJ5:AJ7484 (количество пустых ячеек подряд). Мне необходимо считать максимальный диапазон пустых ячеек только в отфильтрованных (видимых) строках столбца AJ5:AJ7484.Vit080837
Vit080837, прочитайте Правила форума и создайте свою тему, эта тема закрыта. Формулу не забудьте оформить тегами с помощью кнопки fx при редактировании поста. И файл пример обычно ускоряет получение решения
Vit080837, прочитайте Правила форума и создайте свою тему, эта тема закрыта. Формулу не забудьте оформить тегами с помощью кнопки fx при редактировании поста. И файл пример обычно ускоряет получение решенияPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
