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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчёт суммы совпадений в таблице по двум и более ячейкам - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт суммы совпадений в таблице по двум и более ячейкам (Сводные таблицы/Pivot Table)
Подсчёт суммы совпадений в таблице по двум и более ячейкам
NiceZumma Дата: Среда, 19.04.2017, 12:22 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Доброго времени суток!

Я новичок на форуме, собственно зарегистрировался потому что ваш форум посоветовали друзья. Уже неделю пытаюсь решить следующую проблему:

Имеется таблица, где в столбцах гости, а в строках номера приёмов пищи, разбитые на подстроки в которых заданно кто что ел и из чего ел (данные условно изменены для удобства понимания).

Задача с виду очень проста: нужно узнать, сколько всего раз яблоко ели из тарелки. Или, проще говоря, узнать сколько раз во всей таблице под ячейкой со словом Яблоко находится ячейка со словом Тарелка.

Пытался решить через функции INDEX, COUNTIF, IF, но дальше чем посчитать общее количество яблок уехать не выходит :(
Если кто-то может подсказать решение, буду очень обязан.

P.s: не знал как точно назвать тему, прошу меня извинить если название не совсем соответствует рассматриваемой задаче.
К сообщению приложен файл: ___Excel.xls (14.5 Kb)
 
Ответить
СообщениеДоброго времени суток!

Я новичок на форуме, собственно зарегистрировался потому что ваш форум посоветовали друзья. Уже неделю пытаюсь решить следующую проблему:

Имеется таблица, где в столбцах гости, а в строках номера приёмов пищи, разбитые на подстроки в которых заданно кто что ел и из чего ел (данные условно изменены для удобства понимания).

Задача с виду очень проста: нужно узнать, сколько всего раз яблоко ели из тарелки. Или, проще говоря, узнать сколько раз во всей таблице под ячейкой со словом Яблоко находится ячейка со словом Тарелка.

Пытался решить через функции INDEX, COUNTIF, IF, но дальше чем посчитать общее количество яблок уехать не выходит :(
Если кто-то может подсказать решение, буду очень обязан.

P.s: не знал как точно назвать тему, прошу меня извинить если название не совсем соответствует рассматриваемой задаче.
Автор - NiceZumma
Дата добавления - 19.04.2017 в 12:22
buchlotnik Дата: Среда, 19.04.2017, 12:30 | Сообщение № 2
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Здравствуйте.
Варианты:
Код
=СЧЁТЕСЛИМН(D3:F15;D3;D4:F16;D4)
Код
=СУММПРОИЗВ((D3:F15="яблоко")*(D4:F16="тарелка"))
или массивки
Код
=СУММ((D3:F15="яблоко")*(D4:F16="тарелка"))
Код
=СУММ(--(D3:F15&D4:F16="ЯблокоТарелка"))
Код
=СЧЁТ(1/(D3:F15="яблоко")/(D4:F16="тарелка"))
К сообщению приложен файл: _Excel.xls (28.5 Kb)


Сообщение отредактировал buchlotnik - Среда, 19.04.2017, 12:38
 
Ответить
СообщениеЗдравствуйте.
Варианты:
Код
=СЧЁТЕСЛИМН(D3:F15;D3;D4:F16;D4)
Код
=СУММПРОИЗВ((D3:F15="яблоко")*(D4:F16="тарелка"))
или массивки
Код
=СУММ((D3:F15="яблоко")*(D4:F16="тарелка"))
Код
=СУММ(--(D3:F15&D4:F16="ЯблокоТарелка"))
Код
=СЧЁТ(1/(D3:F15="яблоко")/(D4:F16="тарелка"))
Автор - buchlotnik
Дата добавления - 19.04.2017 в 12:30
_Boroda_ Дата: Среда, 19.04.2017, 12:31 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ((D3:F15=H3)*(D4:F16=H4))

Код
=СУММПРОИЗВ((D3:F15="Яблоко")*(D4:F16="Тарелка"))

Код
=СУММПРОИЗВ(--(D3:F15&D4:F16=H3&H4))
К сообщению приложен файл: _Excel_1.xls (28.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ((D3:F15=H3)*(D4:F16=H4))

Код
=СУММПРОИЗВ((D3:F15="Яблоко")*(D4:F16="Тарелка"))

Код
=СУММПРОИЗВ(--(D3:F15&D4:F16=H3&H4))
Автор - _Boroda_
Дата добавления - 19.04.2017 в 12:31
NiceZumma Дата: Среда, 19.04.2017, 12:52 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Через оператор Sumproduct действительно всё работает идеально, хоть я и не до конца понимаю как :D

Спасибо огромное! Скажите, пожалуйста, если в таблице, допустим, между словом тарелка и словом яблоко было бы ещё какое-то слово, например каждый завтрак также содержал бы строку с информацией о напитке, ваша формула бы всё равно работала? То есть она считает не по расположению ячеек, а как-то иначе? Никак не доходит до меня принцип работы функции, хотя и вижу что ответ правильный.

Прошу извинить если задаю лишний вопрос в теме, правила форума читал.
К сообщению приложен файл: 0755086.xls (28.5 Kb)


Сообщение отредактировал NiceZumma - Среда, 19.04.2017, 13:00
 
Ответить
СообщениеЧерез оператор Sumproduct действительно всё работает идеально, хоть я и не до конца понимаю как :D

Спасибо огромное! Скажите, пожалуйста, если в таблице, допустим, между словом тарелка и словом яблоко было бы ещё какое-то слово, например каждый завтрак также содержал бы строку с информацией о напитке, ваша формула бы всё равно работала? То есть она считает не по расположению ячеек, а как-то иначе? Никак не доходит до меня принцип работы функции, хотя и вижу что ответ правильный.

Прошу извинить если задаю лишний вопрос в теме, правила форума читал.
Автор - NiceZumma
Дата добавления - 19.04.2017 в 12:52
buchlotnik Дата: Среда, 19.04.2017, 12:55 | Сообщение № 5
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
каждый завтрак также содержал бы строку с информацией о напитке
в этом случае второй диапазон в формуле сдвинуть и всё, принцип не поменяется. Покажите более жизненный вариант - адаптируем
 
Ответить
Сообщение
Цитата
каждый завтрак также содержал бы строку с информацией о напитке
в этом случае второй диапазон в формуле сдвинуть и всё, принцип не поменяется. Покажите более жизненный вариант - адаптируем
Автор - buchlotnik
Дата добавления - 19.04.2017 в 12:55
NiceZumma Дата: Среда, 19.04.2017, 13:02 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Добавил в таблицу 3 строку (в приложении к сообщению выше) - формула перестала работать <_<
 
Ответить
СообщениеДобавил в таблицу 3 строку (в приложении к сообщению выше) - формула перестала работать <_<
Автор - NiceZumma
Дата добавления - 19.04.2017 в 13:02
_Boroda_ Дата: Среда, 19.04.2017, 13:07 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Диапазоны нужно сместить соответственно
Код
=СУММПРОИЗВ((D3:F15="Яблоко")*(D5:F17="Тарелка"))

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДиапазоны нужно сместить соответственно
Код
=СУММПРОИЗВ((D3:F15="Яблоко")*(D5:F17="Тарелка"))
Автор - _Boroda_
Дата добавления - 19.04.2017 в 13:07
NiceZumma Дата: Среда, 19.04.2017, 13:14 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Действительно работает, независимо от количества срок, проверил на актуальном документе. Просто МАГИЯ :)

Спасибо вам огромное! Целую неделю пытался сам сделать. Низко кланяюсь и аплодирую стоя hands

На всех бы форумах так помогали быстро и просто.

Ответ получен, тему можно закрывать.
 
Ответить
СообщениеДействительно работает, независимо от количества срок, проверил на актуальном документе. Просто МАГИЯ :)

Спасибо вам огромное! Целую неделю пытался сам сделать. Низко кланяюсь и аплодирую стоя hands

На всех бы форумах так помогали быстро и просто.

Ответ получен, тему можно закрывать.
Автор - NiceZumma
Дата добавления - 19.04.2017 в 13:14
_Boroda_ Дата: Среда, 19.04.2017, 13:24 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16885
Репутация: 6599 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Если еще смещать задумаете, то вот общая формула
Код
=СУММПРОИЗВ((ИНДЕКС(D:D;ПОИСКПОЗ("Фрукты";C1:C1017;)):ИНДЕКС(F:F;ПРОСМОТР(;-1/("Фрукты"=C1:C1017);СТРОКА(C1:C1017)))="Яблоко")*(ИНДЕКС(D:D;ПОИСКПОЗ("Посуда";C1:C1017;)):ИНДЕКС(F:F;ПРОСМОТР(;-1/("Посуда"=C1:C1017);СТРОКА(C1:C1017)))="Тарелка"))
К сообщению приложен файл: 0755086_2.xls (30.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли еще смещать задумаете, то вот общая формула
Код
=СУММПРОИЗВ((ИНДЕКС(D:D;ПОИСКПОЗ("Фрукты";C1:C1017;)):ИНДЕКС(F:F;ПРОСМОТР(;-1/("Фрукты"=C1:C1017);СТРОКА(C1:C1017)))="Яблоко")*(ИНДЕКС(D:D;ПОИСКПОЗ("Посуда";C1:C1017;)):ИНДЕКС(F:F;ПРОСМОТР(;-1/("Посуда"=C1:C1017);СТРОКА(C1:C1017)))="Тарелка"))
Автор - _Boroda_
Дата добавления - 19.04.2017 в 13:24
NiceZumma Дата: Среда, 19.04.2017, 13:35 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
И ещё раз спасибо!

Последняя формула учитывает, как я понял, даже ещё не введённые данные от последующих "завтраков". Это очень удобно - при постепенном заполнении таблицы данными не придётся каждый раз менять функцию :)
 
Ответить
СообщениеИ ещё раз спасибо!

Последняя формула учитывает, как я понял, даже ещё не введённые данные от последующих "завтраков". Это очень удобно - при постепенном заполнении таблицы данными не придётся каждый раз менять функцию :)
Автор - NiceZumma
Дата добавления - 19.04.2017 в 13:35
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт суммы совпадений в таблице по двум и более ячейкам (Сводные таблицы/Pivot Table)
  • Страница 1 из 1
  • 1
Поиск:

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