Здравствуйте! Есть таблица в 2 столбца. В одном цифры, во втором текст. Необходимо выполнить подсчет строк в таблице формулой вроде СЧЕТЕСЛИМН по следующим условиям: 1. 1й стобец - все строки содержащие число 2 2. 2й столбец - все строки содержащие любое из заданных слов. Например ячейки можут содержать слова "мама" и "папа" как по отдельности, так и оба сразу причем в любой последовательности. Но посчитать такую строку, вне зависимости от содержимого ("мама"/"папа"/"мама"+"папа"/"папа"+"мама") нужно только один раз (нечто вроде формулы БСЧЕТА). На самом деле ячейка может содержать любую комбинацию из 10-15 искомых слов (мама, папа, сын, дочь, брат и т.д.), но остановимся пока на 2х.
Выполнение условий по отдельности вопросов не вызывает, но как свести их в одно уравнение я понятия не имею. Если в дополнение к условию для 1 столбца (числа) прописать сразу 2 условия на каждое слово для второго столбца (текст), то формула не посчтитает строки, содержащие только одно слово. Если сделать 2 формулы с условиями для каждого из слов по отдельности (не забыв условие для столбца с числами), то в сумме формулы могут посчитать одну и ту же строку ("мама" + "папа") два раза вместо одного.
Надеюсь, кто-нибудь сможет помочь. Заранее спасибо
Здравствуйте! Есть таблица в 2 столбца. В одном цифры, во втором текст. Необходимо выполнить подсчет строк в таблице формулой вроде СЧЕТЕСЛИМН по следующим условиям: 1. 1й стобец - все строки содержащие число 2 2. 2й столбец - все строки содержащие любое из заданных слов. Например ячейки можут содержать слова "мама" и "папа" как по отдельности, так и оба сразу причем в любой последовательности. Но посчитать такую строку, вне зависимости от содержимого ("мама"/"папа"/"мама"+"папа"/"папа"+"мама") нужно только один раз (нечто вроде формулы БСЧЕТА). На самом деле ячейка может содержать любую комбинацию из 10-15 искомых слов (мама, папа, сын, дочь, брат и т.д.), но остановимся пока на 2х.
Выполнение условий по отдельности вопросов не вызывает, но как свести их в одно уравнение я понятия не имею. Если в дополнение к условию для 1 столбца (числа) прописать сразу 2 условия на каждое слово для второго столбца (текст), то формула не посчтитает строки, содержащие только одно слово. Если сделать 2 формулы с условиями для каждого из слов по отдельности (не забыв условие для столбца с числами), то в сумме формулы могут посчитать одну и ту же строку ("мама" + "папа") два раза вместо одного.
Надеюсь, кто-нибудь сможет помочь. Заранее спасибоBeaverSid
Как-то выполнял аналогичную задачу. Первый раз формула сработала, а в последующие разы (с другими таблицами) выскочило такое окно. И я в тупике. Вроде места у меня в локальном С и др. местах предостаточно. Тогда как преодолеть эту ошибку?
Как-то выполнял аналогичную задачу. Первый раз формула сработала, а в последующие разы (с другими таблицами) выскочило такое окно. И я в тупике. Вроде места у меня в локальном С и др. местах предостаточно. Тогда как преодолеть эту ошибку? Eclan
Pelena, спасибо. Первый вариант отлично подходит. Второй вариант слишком громоздкий, потому как таблица, в которой выполняется счет составляет 500-1000 строк и каждый день меняется.
Спасибо
Pelena, спасибо. Первый вариант отлично подходит. Второй вариант слишком громоздкий, потому как таблица, в которой выполняется счет составляет 500-1000 строк и каждый день меняется.
Решил усложнить вам задачу (точнее облегчить себе на работе). Добавляем еще одно условие - цвет ячейки. В моих тысячестрочных таблицах многие ячейки выделены разными цветами. Но для начала давайте возьмем 1 цвет - желтый. Если только формулами не получится и потребуется дополнительно прописать в VBA, пусть так и будет.
Я понимаю, что задача непроста, но очень надеюсь на Вашу помощь. Заранее спасибо
И снова здравствуйте.
Решил усложнить вам задачу (точнее облегчить себе на работе). Добавляем еще одно условие - цвет ячейки. В моих тысячестрочных таблицах многие ячейки выделены разными цветами. Но для начала давайте возьмем 1 цвет - желтый. Если только формулами не получится и потребуется дополнительно прописать в VBA, пусть так и будет.
Я понимаю, что задача непроста, но очень надеюсь на Вашу помощь. Заранее спасибоBeaverSid
Вариант с пользовательской функцией. Только вот заливка ячеек не отслеживается как событие, поэтому после изменения цвета придётся нажимать клавишу F9 для пересчёта или кнопку Пересчитать на листе
Вариант с пользовательской функцией. Только вот заливка ячеек не отслеживается как событие, поэтому после изменения цвета придётся нажимать клавишу F9 для пересчёта или кнопку Пересчитать на листеPelena
Сами формулы работают на ура, но Пересчет (F9), как и кнопка "Пересчитать", в данном файле почему-то не работают. Зато работает F2, Enter для обычных формул и F2, Ctrl+Shift+Enter для массива. Но этот способ не подходит, поскольку, в моих тысячестрочных таблицах придется делать это около полусотни раз при каждой правке таблицы.
Проверено на работе (2013 офис) и дома (2016 офис).
P.S. И еще попробуй убеди теток на работе нажимать эти комбинации. Причем для разных ячеек разные комбинации
Pelena, работает, но не совсем.
Сами формулы работают на ура, но Пересчет (F9), как и кнопка "Пересчитать", в данном файле почему-то не работают. Зато работает F2, Enter для обычных формул и F2, Ctrl+Shift+Enter для массива. Но этот способ не подходит, поскольку, в моих тысячестрочных таблицах придется делать это около полусотни раз при каждой правке таблицы.
Проверено на работе (2013 офис) и дома (2016 офис).
P.S. И еще попробуй убеди теток на работе нажимать эти комбинации. Причем для разных ячеек разные комбинации BeaverSid
Сообщение отредактировал BeaverSid - Пятница, 11.08.2017, 21:08
Желтый цвет как получается? Ручная заливка или, может быть, Условное форматирование? Допустимо ли вместо цвета использовать какой-то другой признак? Например, какой-нибудь символ рядом в столбце?
Желтый цвет как получается? Ручная заливка или, может быть, Условное форматирование? Допустимо ли вместо цвета использовать какой-то другой признак? Например, какой-нибудь символ рядом в столбце?Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Попытаюсь объяснить, для чего это вообще нужно. Есть список задач, которые нужно выполнить в определенные сроки. Ежедневно добавляются новые задачи. Исходный экселевсий файл со списком задач формируется сторонней программой. В этот файл, для каждой задачи вручную вносится информация об исполнителе. После выполнения задачи строка с ее описанием выделяется цветом (другие цвета не трогаем, пока нужен только желтый). Еще одна программа переносит из предыдущего (вчерашнего) файла во вновь сформированный (сегодняшний) файл информацию об исполнителе и цвете строк. Это необходимо делать потому что, во-первых, задач очень много и постоянно добавляются новые, а во-вторых выполненные задачи могут оставаться в списке еще несколько дней или недель. И исполнитель, видя строку желтого цвета, пропускает уже выполненную задачу. Использование другой индикации недопустимо. Если использовать другой признак, например символ рядом в столбце, то срабатывает человеческий фактор (зачастую в силу возраста исполнителя) - исполнитель может тупо не заметить этот символ. Уже проверяли ))) Дополнительный фильтр сортировки но этому символу тоже не вариант - большинство людей воспринимает лишние 2 клика мышью как пол дня каторжного труда. Ну и наверное основная причина - это уже устоявшийся способ, которым пользуется несколько десятков человек. При большинстве нововведений слышится фраза типа "верните все как было, а то нам так неудобно". Ну а все эти расчеты нужны чтобы исполнители и группы исполнителей знали у кого сколько задач выполнено/осталось выполнить/просрочено. Ну и для начальства - гонять подчиненных за невыполнение))))
Желтый цвет получается путем ручной заливки.
Попытаюсь объяснить, для чего это вообще нужно. Есть список задач, которые нужно выполнить в определенные сроки. Ежедневно добавляются новые задачи. Исходный экселевсий файл со списком задач формируется сторонней программой. В этот файл, для каждой задачи вручную вносится информация об исполнителе. После выполнения задачи строка с ее описанием выделяется цветом (другие цвета не трогаем, пока нужен только желтый). Еще одна программа переносит из предыдущего (вчерашнего) файла во вновь сформированный (сегодняшний) файл информацию об исполнителе и цвете строк. Это необходимо делать потому что, во-первых, задач очень много и постоянно добавляются новые, а во-вторых выполненные задачи могут оставаться в списке еще несколько дней или недель. И исполнитель, видя строку желтого цвета, пропускает уже выполненную задачу. Использование другой индикации недопустимо. Если использовать другой признак, например символ рядом в столбце, то срабатывает человеческий фактор (зачастую в силу возраста исполнителя) - исполнитель может тупо не заметить этот символ. Уже проверяли ))) Дополнительный фильтр сортировки но этому символу тоже не вариант - большинство людей воспринимает лишние 2 клика мышью как пол дня каторжного труда. Ну и наверное основная причина - это уже устоявшийся способ, которым пользуется несколько десятков человек. При большинстве нововведений слышится фраза типа "верните все как было, а то нам так неудобно". Ну а все эти расчеты нужны чтобы исполнители и группы исполнителей знали у кого сколько задач выполнено/осталось выполнить/просрочено. Ну и для начальства - гонять подчиненных за невыполнение))))BeaverSid
Сообщение отредактировал BeaverSid - Суббота, 12.08.2017, 06:59
Как вариант, могу попробовать условно форматирование по символу рядом в столбце. Но поскольку параметры файла (в том числе условное форматирование) при его формировании сторонней программой задать нельзя, условное форматирование придется каждый раз переносить вручную. А для ответственного за всю эту фигню, как уже писал выше, это страшная мука)))
Как вариант, могу попробовать условно форматирование по символу рядом в столбце. Но поскольку параметры файла (в том числе условное форматирование) при его формировании сторонней программой задать нельзя, условное форматирование придется каждый раз переносить вручную. А для ответственного за всю эту фигню, как уже писал выше, это страшная мука)))BeaverSid
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
