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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет повторений внутри ячейки по условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет повторений внутри ячейки по условию (Макросы/Sub)
Подсчет повторений внутри ячейки по условию
Driven2002 Дата: Суббота, 22.08.2020, 23:36 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день уважаемые форумчане!

Я столкнулся с задачей подсчитать количество повторяющихся слов внутри ячейки в зависимости от значения строки выше.
Все данные располагаются в одном столбце.

функция
Код
=СЧЁТЕСЛИМН($A$1:$A$100;"*Красный*";$A$2:$A$101;"*пирожок*")
считает ячейки где содержится искомое значение, без учета его повторений в ячейки.

а функция
Код
=(ДЛСТР(A3)-ДЛСТР(ПОДСТАВИТЬ(A3;"пирожок";)))/ДЛСТР("пирожок")
позволяет посчитать количество слов внутри ячейке, вот только я не знаю как задать условие для этих подсчетов.
Подскажите как считать количество повторяющихся значений внутри ячейки по условию?
К сообщению приложен файл: 1_.xlsx(10.9 Kb)
 
Ответить
СообщениеДобрый день уважаемые форумчане!

Я столкнулся с задачей подсчитать количество повторяющихся слов внутри ячейки в зависимости от значения строки выше.
Все данные располагаются в одном столбце.

функция
Код
=СЧЁТЕСЛИМН($A$1:$A$100;"*Красный*";$A$2:$A$101;"*пирожок*")
считает ячейки где содержится искомое значение, без учета его повторений в ячейки.

а функция
Код
=(ДЛСТР(A3)-ДЛСТР(ПОДСТАВИТЬ(A3;"пирожок";)))/ДЛСТР("пирожок")
позволяет посчитать количество слов внутри ячейке, вот только я не знаю как задать условие для этих подсчетов.
Подскажите как считать количество повторяющихся значений внутри ячейки по условию?

Автор - Driven2002
Дата добавления - 22.08.2020 в 23:36
Driven2002 Дата: Суббота, 22.08.2020, 23:51 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
admin ошибся разделом. перенесите пожалуйста тему в раздел Вопросы по Excel
 
Ответить
Сообщениеadmin ошибся разделом. перенесите пожалуйста тему в раздел Вопросы по Excel

Автор - Driven2002
Дата добавления - 22.08.2020 в 23:51
Pelena Дата: Воскресенье, 23.08.2020, 09:33 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16638
Репутация: 3597 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Здравствуйте.
Код
=СУММПРОИЗВ((ДЛСТР($A$2:$A$30)-ДЛСТР(ПОДСТАВИТЬ($A$2:$A$30;G$5;"")))/ДЛСТР(G$5)*($A$1:$A$29=$F6))
К сообщению приложен файл: 9535640.xlsx(11.6 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Код
=СУММПРОИЗВ((ДЛСТР($A$2:$A$30)-ДЛСТР(ПОДСТАВИТЬ($A$2:$A$30;G$5;"")))/ДЛСТР(G$5)*($A$1:$A$29=$F6))

Автор - Pelena
Дата добавления - 23.08.2020 в 09:33
Driven2002 Дата: Воскресенье, 23.08.2020, 11:52 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Большое спасибо за ответ!

в примере все работает отлично, вот только на реальном файле возникла проблема.

Код
*($A$1:$A$29=$F6))
здесь задается жесткое условие поиска а в списке может бить № ??. Красный, № ?. красный<пробел> т.д. как искать первый параметр по части слова например *крас*, как это позволяет делать функция "Счет еслимн", и как не привязываться к регистру текста при поиске, слова могут быть написаны, как маленькими так и большими буквами?
 
Ответить
СообщениеБольшое спасибо за ответ!

в примере все работает отлично, вот только на реальном файле возникла проблема.

Код
*($A$1:$A$29=$F6))
здесь задается жесткое условие поиска а в списке может бить № ??. Красный, № ?. красный<пробел> т.д. как искать первый параметр по части слова например *крас*, как это позволяет делать функция "Счет еслимн", и как не привязываться к регистру текста при поиске, слова могут быть написаны, как маленькими так и большими буквами?

Автор - Driven2002
Дата добавления - 23.08.2020 в 11:52
Pelena Дата: Воскресенье, 23.08.2020, 12:51 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16638
Репутация: 3597 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Приведите более реальный пример.
Если без примера, то так
Код
=СУММПРОИЗВ((ДЛСТР($A$2:$A$30)-ДЛСТР(ПОДСТАВИТЬ($A$2:$A$30;G$5;"")))/ДЛСТР(G$5)*ЕЧИСЛО(ПОИСК($F6;$A$1:$A$29)))


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПриведите более реальный пример.
Если без примера, то так
Код
=СУММПРОИЗВ((ДЛСТР($A$2:$A$30)-ДЛСТР(ПОДСТАВИТЬ($A$2:$A$30;G$5;"")))/ДЛСТР(G$5)*ЕЧИСЛО(ПОИСК($F6;$A$1:$A$29)))

Автор - Pelena
Дата добавления - 23.08.2020 в 12:51
bmv98rus Дата: Воскресенье, 23.08.2020, 13:33 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3219
Репутация: 554 ±
Замечаний: 0% ±

Excel 2013/2016
Pelena,
и как не привязываться к регистру текста при поиске

Привести к одному знаменателю нужно перед подстановкой.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеPelena,
и как не привязываться к регистру текста при поиске

Привести к одному знаменателю нужно перед подстановкой.

Автор - bmv98rus
Дата добавления - 23.08.2020 в 13:33
Pelena Дата: Воскресенье, 23.08.2020, 16:09 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16638
Репутация: 3597 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Функция ПОИСК() не чувствительна к регистру


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеФункция ПОИСК() не чувствительна к регистру

Автор - Pelena
Дата добавления - 23.08.2020 в 16:09
Driven2002 Дата: Понедельник, 24.08.2020, 11:59 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Огромное спасибо! Данная формула работает.

если не трудно объясните мне для понимания механизм формулы, (как ищаться считаются повторы в ячейке понятно) а вот после того фрагмента
Код
*ЕЧИСЛО(ПОИСК($F6;$A$1:$A$29)))
не понятно как формируется условие поиска и получения общей суммы
 
Ответить
СообщениеОгромное спасибо! Данная формула работает.

если не трудно объясните мне для понимания механизм формулы, (как ищаться считаются повторы в ячейке понятно) а вот после того фрагмента
Код
*ЕЧИСЛО(ПОИСК($F6;$A$1:$A$29)))
не понятно как формируется условие поиска и получения общей суммы

Автор - Driven2002
Дата добавления - 24.08.2020 в 11:59
Pelena Дата: Понедельник, 24.08.2020, 13:24 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16638
Репутация: 3597 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Функция ПОИСК ищет значение (например, красный) в тексте. Если находит, возвращает число (номер позиции), а если не находит, возвращает ошибку.
В нашем случае в качестве текста выступает диапазон $A$1:$A$29, поэтому функция вернет массив, состоящий из чисел (если значение найдено) и ошибок (если не найдено)э
А функция ЕЧИСЛО преобразует этот массив в другой, который будет состоять из значений ИСТИНА и ЛОЖЬ. Теперь мы этот массив умножаем на первый, который содержит кол-во слов. ИСТИНА - это 1, ЛОЖЬ - это 0.
Соответственно, там, где "красный" найден не был, уже не важно, были ли повторы, всё равно на выходе 0


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеФункция ПОИСК ищет значение (например, красный) в тексте. Если находит, возвращает число (номер позиции), а если не находит, возвращает ошибку.
В нашем случае в качестве текста выступает диапазон $A$1:$A$29, поэтому функция вернет массив, состоящий из чисел (если значение найдено) и ошибок (если не найдено)э
А функция ЕЧИСЛО преобразует этот массив в другой, который будет состоять из значений ИСТИНА и ЛОЖЬ. Теперь мы этот массив умножаем на первый, который содержит кол-во слов. ИСТИНА - это 1, ЛОЖЬ - это 0.
Соответственно, там, где "красный" найден не был, уже не важно, были ли повторы, всё равно на выходе 0

Автор - Pelena
Дата добавления - 24.08.2020 в 13:24
bmv98rus Дата: Понедельник, 24.08.2020, 15:29 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3219
Репутация: 554 ±
Замечаний: 0% ±

Excel 2013/2016
не чувствительна
я про эту часть
Код
ПОДСТАВИТЬ($A$2:$A$30;G$5;"")


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
не чувствительна
я про эту часть
Код
ПОДСТАВИТЬ($A$2:$A$30;G$5;"")

Автор - bmv98rus
Дата добавления - 24.08.2020 в 15:29
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет повторений внутри ячейки по условию (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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