Добрый день, не продвинутый юзер в Excel, буду признателен за любую подсказку.
Назначение документа: Документ должен выводить в столбце "Статус" одно из трёх значений: 1) Освоено - когда три подряд значения превышают значение "80" 2) В работе - когда в столбце появляется хоть одно из значений (даже 0- это обозначает нулевой результат на занятии, но само занятие учитывается) 3) Не в работе - когда все ячейки пустые
Что сделано и суть проблемы: 1) Нашёл формулу для подсчёта трёх совпадающих условий подряд (19 и 22 строка, в таблицы столбец "N") - вроде стабильно работает. МАКС(ЧАСТОТА(СТОЛБЕЦ(E3:M3);(E3:M3<=80)*СТОЛБЕЦ(E3:M3)))-1 МАКС(ЧАСТОТА(СТОЛБЕЦ(E9:M9);(E9:M9<>1)*СТОЛБЕЦ(E9:M9)))-1 2) Вывел Графу статус через формулу "если" (25 строка, в таблице столбец "N") - тоже стабильно работает. ЕСЛИМН(N3>=3;"Освоено";И(N3<3;N3>0);"В работе";N3=0;"Не в работе")
– Однако, Не получается сделать так, чтобы формула для вычисления учитывала пустые клетки (пропуски занятий). В данном документе 0 и пустая клетка имеют разные значения (0 - нулевой результат на занятии, "" - отсутствие занятия). На примере второй строки из таблицы: там 3 числа подряд соответствуют критерию (за 80%), но между ними стоит пустая клетка, которая ломает расчёт.
Вопрос: Как сделать так чтобы пустая клетка не ломала созданное условие? Есть ли какая-нибудь другая функция, формула или вообще более простой шаблон для решения данной задачи?
P.S. Excel 2016 (если это важно)
Добрый день, не продвинутый юзер в Excel, буду признателен за любую подсказку.
Назначение документа: Документ должен выводить в столбце "Статус" одно из трёх значений: 1) Освоено - когда три подряд значения превышают значение "80" 2) В работе - когда в столбце появляется хоть одно из значений (даже 0- это обозначает нулевой результат на занятии, но само занятие учитывается) 3) Не в работе - когда все ячейки пустые
Что сделано и суть проблемы: 1) Нашёл формулу для подсчёта трёх совпадающих условий подряд (19 и 22 строка, в таблицы столбец "N") - вроде стабильно работает. МАКС(ЧАСТОТА(СТОЛБЕЦ(E3:M3);(E3:M3<=80)*СТОЛБЕЦ(E3:M3)))-1 МАКС(ЧАСТОТА(СТОЛБЕЦ(E9:M9);(E9:M9<>1)*СТОЛБЕЦ(E9:M9)))-1 2) Вывел Графу статус через формулу "если" (25 строка, в таблице столбец "N") - тоже стабильно работает. ЕСЛИМН(N3>=3;"Освоено";И(N3<3;N3>0);"В работе";N3=0;"Не в работе")
– Однако, Не получается сделать так, чтобы формула для вычисления учитывала пустые клетки (пропуски занятий). В данном документе 0 и пустая клетка имеют разные значения (0 - нулевой результат на занятии, "" - отсутствие занятия). На примере второй строки из таблицы: там 3 числа подряд соответствуют критерию (за 80%), но между ними стоит пустая клетка, которая ломает расчёт.
Вопрос: Как сделать так чтобы пустая клетка не ломала созданное условие? Есть ли какая-нибудь другая функция, формула или вообще более простой шаблон для решения данной задачи?
DrMini, спасибо за ответ. Пробовал через такую формулу, которую вы предложили, но так происходит подсчёт количества совпадающих под это условие ячеек. А в данном случае нужно количество совпадающих под условие ячеек подряд. Пробовал придумать какое-либо доп. условие к этой формуле, но безрезультатно.
DrMini, спасибо за ответ. Пробовал через такую формулу, которую вы предложили, но так происходит подсчёт количества совпадающих под это условие ячеек. А в данном случае нужно количество совпадающих под условие ячеек подряд. Пробовал придумать какое-либо доп. условие к этой формуле, но безрезультатно.alex70737
alex70737, поясните, в строке 4 "Освоено" хотя >=80 идут не 3 ячейки подряд. Между первым и третьем значением пустая ячейка. В 8 строке "Освоено" хотя 1 идут не 3 ячейки подряд. Между первым и пятым значением две пустые ячейки.
alex70737, поясните, в строке 4 "Освоено" хотя >=80 идут не 3 ячейки подряд. Между первым и третьем значением пустая ячейка. В 8 строке "Освоено" хотя 1 идут не 3 ячейки подряд. Между первым и пятым значением две пустые ячейки.AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
AlexM, спасибо за ответ. 1) Представьте тестирование по какому-либо навыку на занятии с ребенком, когда этот ребенок три занятия подряд получает процент выше 80% (или "1" смотря по каким усл.ед. идёт подсчёт), тогда навык считается "Освоенным". Однако иногда ребенок пропускает занятие по каким-либо причинам (ячейка остаётся пустой в таблице), но три подряд занятия с соответствующими результатами не сбрасываются от пропуска в какой-либо день (пустой ячейки в таблице). 2) В таблице в строке №8 показан результат с тремя подряд соответствующими значениями ("1" - единицами), но между ними есть пропуск в два занятия ("Между первым и вторым соответствующим значением есть две пустые ячейки"). В таком случае правильный результат в графе "Статус" должен быть "Освоено", не смотря на пропуски занятий. Но в данном случае "Формула для вычисления кол-ва правильных значений" учитывает пустые клетки в расчете и производит подсчёт неверно и из-за этого в графе "Статус" появляется неверный статус. 3) Извините, по строке 4 не понял Вас. Там стоит статус "В работе" и в примере правильного статуса также стоит статус "В работе"
AlexM, спасибо за ответ. 1) Представьте тестирование по какому-либо навыку на занятии с ребенком, когда этот ребенок три занятия подряд получает процент выше 80% (или "1" смотря по каким усл.ед. идёт подсчёт), тогда навык считается "Освоенным". Однако иногда ребенок пропускает занятие по каким-либо причинам (ячейка остаётся пустой в таблице), но три подряд занятия с соответствующими результатами не сбрасываются от пропуска в какой-либо день (пустой ячейки в таблице). 2) В таблице в строке №8 показан результат с тремя подряд соответствующими значениями ("1" - единицами), но между ними есть пропуск в два занятия ("Между первым и вторым соответствующим значением есть две пустые ячейки"). В таком случае правильный результат в графе "Статус" должен быть "Освоено", не смотря на пропуски занятий. Но в данном случае "Формула для вычисления кол-ва правильных значений" учитывает пустые клетки в расчете и производит подсчёт неверно и из-за этого в графе "Статус" появляется неверный статус. 3) Извините, по строке 4 не понял Вас. Там стоит статус "В работе" и в примере правильного статуса также стоит статус "В работе"alex70737
Сообщение отредактировал alex70737 - Четверг, 11.01.2024, 13:01
Возможно на допстолбцы вывести баллы без пробелов и подсчет вести по допстолбцам А вот формулу с частотой я не понял, там дальше сами попробуйте
Возможно на допстолбцы вывести баллы без пробелов и подсчет вести по допстолбцам А вот формулу с частотой я не понял, там дальше сами попробуйтеelovkov
elovkov, Спасибо. То есть вывести отдельную таблицу, где будут пропадать все пустые ячейки. С такой стороны не смотрел на задачу. Попробую развить вашу идею, осталось разобраться в вашей формуле)) P.S. Формулу с частотой я тоже не понимаю, нашёл похожую формулу на просторах интернета
elovkov, Спасибо. То есть вывести отдельную таблицу, где будут пропадать все пустые ячейки. С такой стороны не смотрел на задачу. Попробую развить вашу идею, осталось разобраться в вашей формуле)) P.S. Формулу с частотой я тоже не понимаю, нашёл похожую формулу на просторах интернетаalex70737
elovkov, Спасибо. То есть вывести отдельную таблицу, где будут пропадать все пустые ячейки. С такой стороны не смотрел на задачу. Попробую развить вашу идею, осталось разобраться в вашей формуле)) P.S. Формулу с частотой я тоже не понимаю, нашёл похожую формулу на просторах интернета
elovkov, Спасибо. То есть вывести отдельную таблицу, где будут пропадать все пустые ячейки. С такой стороны не смотрел на задачу. Попробую развить вашу идею, осталось разобраться в вашей формуле)) P.S. Формулу с частотой я тоже не понимаю, нашёл похожую формулу на просторах интернетаalex70737
Я ее победил))) Не любит ЧАСТОТА пустоты видимо, если все пустое нулями забить то правильно считает Еще подправил формулу для статуса (желтым), а то получается что при 0 и в работе и не в работе может быть ЕСЛИМН пустоту же за ноль считает, а 0 это тоже работа получается
Я ее победил))) Не любит ЧАСТОТА пустоты видимо, если все пустое нулями забить то правильно считает Еще подправил формулу для статуса (желтым), а то получается что при 0 и в работе и не в работе может быть ЕСЛИМН пустоту же за ноль считает, а 0 это тоже работа получаетсяelovkov
Формула массива вводится одновременным нажатием Ctrl+Shift+Enter:
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(M3;;;;-СТОЛБЕЦ(A:H));">=80")-СЧЁТЕСЛИ(СМЕЩ(M3;;;;-СТОЛБЕЦ(A:H));"<80")>2)+(СЧЁТЕСЛИ(F3:M3;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
*Без дополнительных ячеек. **Для "0" и "1":
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(M9;;;;-СТОЛБЕЦ(A:H));1)-СЧЁТЕСЛИ(СМЕЩ(M9;;;;-СТОЛБЕЦ(A:H));0)>2)+(СЧЁТЕСЛИ(F9:M9;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
***А чтобы можно было правую границу таблицы расширять:
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(E3;;ЧИСЛСТОЛБ(G3:N3);;6-СТОЛБЕЦ(G:N));">=80")-СЧЁТЕСЛИ(СМЕЩ(E3;;ЧИСЛСТОЛБ(G3:N3);;6-СТОЛБЕЦ(G:N));"<80")>2)+(СЧЁТЕСЛИ(F3:M3;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
****Алгоритм формулы привязан к правому краю таблицы. Если после тройки значений >=80 будет меньшее значение, то формула не сработает.
Формула массива вводится одновременным нажатием Ctrl+Shift+Enter:
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(M3;;;;-СТОЛБЕЦ(A:H));">=80")-СЧЁТЕСЛИ(СМЕЩ(M3;;;;-СТОЛБЕЦ(A:H));"<80")>2)+(СЧЁТЕСЛИ(F3:M3;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
*Без дополнительных ячеек. **Для "0" и "1":
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(M9;;;;-СТОЛБЕЦ(A:H));1)-СЧЁТЕСЛИ(СМЕЩ(M9;;;;-СТОЛБЕЦ(A:H));0)>2)+(СЧЁТЕСЛИ(F9:M9;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
***А чтобы можно было правую границу таблицы расширять:
Код
=ВЫБОР(ИЛИ(СЧЁТЕСЛИ(СМЕЩ(E3;;ЧИСЛСТОЛБ(G3:N3);;6-СТОЛБЕЦ(G:N));">=80")-СЧЁТЕСЛИ(СМЕЩ(E3;;ЧИСЛСТОЛБ(G3:N3);;6-СТОЛБЕЦ(G:N));"<80")>2)+(СЧЁТЕСЛИ(F3:M3;"<>")>0)+1;"Не в работе";"В работе";"Освоено")
****Алгоритм формулы привязан к правому краю таблицы. Если после тройки значений >=80 будет меньшее значение, то формула не сработает.Светлый
Программировать проще, чем писать стихи.
Сообщение отредактировал Светлый - Четверг, 11.01.2024, 18:59
elovkov, Спасибо. Единственное что заменил в вашей формуле "8", так как там не статичное число столбцов с датами, а они будут постоянно добавляться. Заменил с "8" на "(СЧЁТЕСЛИ(F3:M3;">=0")+СЧЁТЕСЛИ(F3:M3;"")" для подсчёта кол-ва всех столбцов.
Было - "=ЕСЛИ(N3>=3;"Освоено";ЕСЛИ(СУММПРОИЗВ((F3:M3="")*1)=8;"Не в работе";"В работе"))"; Стало - =ЕСЛИ(N3>=3;"Освоено";ЕСЛИ(СУММПРОИЗВ((F3:M3="")*1)=(СЧЁТЕСЛИ(F3:M3;">=0")+СЧЁТЕСЛИ(F3:M3;""));"Не в работе";"В работе"))
P.S. Вроде бы корректно работает)
elovkov, Спасибо. Единственное что заменил в вашей формуле "8", так как там не статичное число столбцов с датами, а они будут постоянно добавляться. Заменил с "8" на "(СЧЁТЕСЛИ(F3:M3;">=0")+СЧЁТЕСЛИ(F3:M3;"")" для подсчёта кол-ва всех столбцов.
Было - "=ЕСЛИ(N3>=3;"Освоено";ЕСЛИ(СУММПРОИЗВ((F3:M3="")*1)=8;"Не в работе";"В работе"))"; Стало - =ЕСЛИ(N3>=3;"Освоено";ЕСЛИ(СУММПРОИЗВ((F3:M3="")*1)=(СЧЁТЕСЛИ(F3:M3;">=0")+СЧЁТЕСЛИ(F3:M3;""));"Не в работе";"В работе"))
ИМХО лишнее, так как это условие для выявления именно пустой строки, как я понимаю именно если строка пуста, то "не в работе", а если есть хотябы один ноль, то уже работа идет. Но работе формулы мешать не будет
Ну тут
Код
(СЧЁТЕСЛИ(F3:M3;">=0")
ИМХО лишнее, так как это условие для выявления именно пустой строки, как я понимаю именно если строка пуста, то "не в работе", а если есть хотябы один ноль, то уже работа идет. Но работе формулы мешать не будетelovkov
Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
Светлый, Огромное спасибо. Более лаконичный вариант решения задачи. Однако пока сам не знаю насколько критично данное допущение("****"). "****Алгоритм формулы привязан к правому краю таблицы. Если после тройки значений >=80 будет меньшее значение, то формула не сработает."
Светлый, Огромное спасибо. Более лаконичный вариант решения задачи. Однако пока сам не знаю насколько критично данное допущение("****"). "****Алгоритм формулы привязан к правому краю таблицы. Если после тройки значений >=80 будет меньшее значение, то формула не сработает."alex70737
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
