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

Вход

Регистрация

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

 

= Мир MS Excel/Функция "Суммеслимн" и объединенные ячейки. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Функция "Суммеслимн" и объединенные ячейки.
Функция "Суммеслимн" и объединенные ячейки.
Saff Дата: Понедельник, 26.03.2012, 11:52 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

Есть пример в котором есть таблица форма которой не меняется (меняются только столбцы E, F, K, L - ячейки объединяются), внизу этой таблицы есть ещё таблица в которой считаются объёмы по каждому виду.
Проблема такого рода в одном из критериев есть текст но этот критерий написан в двух ячейках. Функция считает только первую ячейку. как заставить чтобы он понимал что если критерий объединен в из двух ячеек в одну распространялся на две или три строки?

я так понимаю тут только Макросом... sad

и ещё, сводной таблицей тоже не смог, всё по той же причине из-за объединенных ячеек.
К сообщению приложен файл: 26032012.xlsx (62.2 Kb)
 
Ответить
СообщениеЕсть пример в котором есть таблица форма которой не меняется (меняются только столбцы E, F, K, L - ячейки объединяются), внизу этой таблицы есть ещё таблица в которой считаются объёмы по каждому виду.
Проблема такого рода в одном из критериев есть текст но этот критерий написан в двух ячейках. Функция считает только первую ячейку. как заставить чтобы он понимал что если критерий объединен в из двух ячеек в одну распространялся на две или три строки?

я так понимаю тут только Макросом... sad

и ещё, сводной таблицей тоже не смог, всё по той же причине из-за объединенных ячеек.
Автор - Saff
Дата добавления - 26.03.2012 в 11:52
Формуляр Дата: Понедельник, 26.03.2012, 12:09 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 832
Репутация: 255 ±
Замечаний: 0% ±

Excel 2003, 2013
Данная проблема подробно рассматривается здесь :
Как считать в таблице с объединенными ячейками (если разъединять не хочется. Без макросов.)

Excel 2003 EN, 2013 EN
 
Ответить
СообщениеДанная проблема подробно рассматривается здесь :
Как считать в таблице с объединенными ячейками (если разъединять не хочется. Без макросов.)
Автор - Формуляр
Дата добавления - 26.03.2012 в 12:09
_Boroda_ Дата: Понедельник, 26.03.2012, 12:31 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А если просто так
Code
=СУММ(($D$3:$D$105=H$110)*(ЕСЛИ(ЕТЕКСТ($F$3:$F$105);$F$3:$F$105;ЕСЛИ(ЕТЕКСТ($F$2:$F$104);$F$2:$F$104;$F$1:$F$103))=$G111)*$C$3:$C$105)+СУММ(($J$3:$J$105=H$110)*(ЕСЛИ(ЕТЕКСТ($L$3:$L$105);$L$3:$L$105;ЕСЛИ(ЕТЕКСТ($L$2:$L$104);$L$2:$L$104;$L$1:$L$103))=$G111)*$I$3:$I$105)

там вся сложность в том, что объединены могут быть не только 2, но и 3 ячейки

{} указывают, что это формула массива. Вводится не Ентером, а КОНТРЛ+Шифт+Ентер (сразу нажать). Подробнее можно осмотреть в справке Excel
К сообщению приложен файл: 26032012_1.xlsx (32.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА если просто так
Code
=СУММ(($D$3:$D$105=H$110)*(ЕСЛИ(ЕТЕКСТ($F$3:$F$105);$F$3:$F$105;ЕСЛИ(ЕТЕКСТ($F$2:$F$104);$F$2:$F$104;$F$1:$F$103))=$G111)*$C$3:$C$105)+СУММ(($J$3:$J$105=H$110)*(ЕСЛИ(ЕТЕКСТ($L$3:$L$105);$L$3:$L$105;ЕСЛИ(ЕТЕКСТ($L$2:$L$104);$L$2:$L$104;$L$1:$L$103))=$G111)*$I$3:$I$105)

там вся сложность в том, что объединены могут быть не только 2, но и 3 ячейки

{} указывают, что это формула массива. Вводится не Ентером, а КОНТРЛ+Шифт+Ентер (сразу нажать). Подробнее можно осмотреть в справке Excel
Автор - _Boroda_
Дата добавления - 26.03.2012 в 12:31
Saff Дата: Понедельник, 26.03.2012, 12:50 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

Пока разбирался с ответом Формуляр'a, возник вопрос со скобками "{}"
и теперь в прекрастном варианте от _Boroda_, опять эти скобки ))

что за скобки? формула перестаёт работать, как только Enter нажимаешь и выходит "#Знач!"
 
Ответить
СообщениеПока разбирался с ответом Формуляр'a, возник вопрос со скобками "{}"
и теперь в прекрастном варианте от _Boroda_, опять эти скобки ))

что за скобки? формула перестаёт работать, как только Enter нажимаешь и выходит "#Знач!"
Автор - Saff
Дата добавления - 26.03.2012 в 12:50
Saff Дата: Понедельник, 26.03.2012, 13:07 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

_Boroda_, огромное вам спасибо!
 
Ответить
Сообщение_Boroda_, огромное вам спасибо!
Автор - Saff
Дата добавления - 26.03.2012 в 13:07
Влад Дата: Четверг, 17.01.2013, 18:25 | Сообщение № 6
Группа: Гости
Столкнулся с проблемой следующего характера:
есть столбец с датами в диапазоне от 10.01.2011 по 10.03.2015. И есть второй столбец, где указаны платежи согласно каждой дате...

Хотел сделать формулу, которая берёт необходимый диапазон (скажем с 10.01.2012 по 10.12.2012) и суммирует значения из 2-го столбца соответствующие заданному диапазону...

в формуле "СУММЕСЛИ" учитывается только один критерий... либо больше, либо меньше...
а "СУММЕСЛИМН" выбивает 0...

пробовал условия ставить через функцию "И"... результат 0...

Помогите, плиз!
 
Ответить
СообщениеСтолкнулся с проблемой следующего характера:
есть столбец с датами в диапазоне от 10.01.2011 по 10.03.2015. И есть второй столбец, где указаны платежи согласно каждой дате...

Хотел сделать формулу, которая берёт необходимый диапазон (скажем с 10.01.2012 по 10.12.2012) и суммирует значения из 2-го столбца соответствующие заданному диапазону...

в формуле "СУММЕСЛИ" учитывается только один критерий... либо больше, либо меньше...
а "СУММЕСЛИМН" выбивает 0...

пробовал условия ставить через функцию "И"... результат 0...

Помогите, плиз!
Автор - Влад
Дата добавления - 17.01.2013 в 18:25
Pelena Дата: Четверг, 17.01.2013, 18:37 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация: 4417 ±
Замечаний: ±

Excel 365 & Mac Excel
Влад, Ваш вопрос к теме объединенных ячеек отношения не имеет, поэтому прочитайте Правила форума, создайте свою тему и выложите файл со своими данными и вариантами формул

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВлад, Ваш вопрос к теме объединенных ячеек отношения не имеет, поэтому прочитайте Правила форума, создайте свою тему и выложите файл со своими данными и вариантами формул
Автор - Pelena
Дата добавления - 17.01.2013 в 18:37
Saff Дата: Четверг, 18.04.2013, 12:26 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

Цитата (_Boroda_)
там вся сложность в том, что объединены могут быть не только 2, но и 3 ячейки


всё работало по сей день, пока не появилась проблема с 4,5 и 6 объединёнными ячейками. А формула считает только до 3.... как быть тут ?
К сообщению приложен файл: 8117837.xlsx (31.3 Kb)


Сообщение отредактировал Saff - Четверг, 18.04.2013, 12:52
 
Ответить
Сообщение
Цитата (_Boroda_)
там вся сложность в том, что объединены могут быть не только 2, но и 3 ячейки


всё работало по сей день, пока не появилась проблема с 4,5 и 6 объединёнными ячейками. А формула считает только до 3.... как быть тут ?
Автор - Saff
Дата добавления - 18.04.2013 в 12:26
Michael_S Дата: Четверг, 18.04.2013, 12:38 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний: 0% ±

Excel2016
Saff, для начала выложить пример своего файла с проблемой.
 
Ответить
СообщениеSaff, для начала выложить пример своего файла с проблемой.
Автор - Michael_S
Дата добавления - 18.04.2013 в 12:38
Saff Дата: Четверг, 18.04.2013, 12:56 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

Michael_S, пример был выше, но ещё раз прикрепил в предыдущем сообщении. Просто в новом условии добавились строки в объединённые ячейки, было максимально 3, а сейчас доходит до 6, при этом новые 3 строки не считает по формуле которая была предложена _Boroda_.
 
Ответить
СообщениеMichael_S, пример был выше, но ещё раз прикрепил в предыдущем сообщении. Просто в новом условии добавились строки в объединённые ячейки, было максимально 3, а сейчас доходит до 6, при этом новые 3 строки не считает по формуле которая была предложена _Boroda_.
Автор - Saff
Дата добавления - 18.04.2013 в 12:56
_Boroda_ Дата: Четверг, 18.04.2013, 15:59 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Набросал тут формулку. Такая прям лапочка получилась, аж самому понравилась. Обожаю монстров!
Код
=СУММ(ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);))*$C$3:$C$104*($D$3:$D$104=H$110);ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($L$3:$L$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($L$3:$L$104=$G111;СТРОКА($F$3:$F$104)-2);))*$I$3:$I$104*($J$3:$J$104=H$110))
К сообщению приложен файл: 8117837_2.xlsx (32.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНабросал тут формулку. Такая прям лапочка получилась, аж самому понравилась. Обожаю монстров!
Код
=СУММ(ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);))*$C$3:$C$104*($D$3:$D$104=H$110);ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($L$3:$L$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($L$3:$L$104=$G111;СТРОКА($F$3:$F$104)-2);))*$I$3:$I$104*($J$3:$J$104=H$110))
Автор - _Boroda_
Дата добавления - 18.04.2013 в 15:59
Saff Дата: Понедельник, 22.04.2013, 12:10 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 2 ±
Замечаний: 20% ±

_Boroda_, Спасибо вам огромное!

зы. мечтаю теперь получить мастер класс по этой формуле ))
 
Ответить
Сообщение_Boroda_, Спасибо вам огромное!

зы. мечтаю теперь получить мастер класс по этой формуле ))
Автор - Saff
Дата добавления - 22.04.2013 в 12:10
_Boroda_ Дата: Среда, 24.04.2013, 15:44 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата (Saff)
мечтаю теперь получить мастер класс по этой формуле ))

Где-то так:
Формула считает 2 диапазона; B:F и H:L, поэтому объяснялку пишем только для одного. Для второго все аналогично.
Код
=СУММ(ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);))*$C$3:$C$104*($D$3:$D$104=H$110))

Делим на кусочки:
1) ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)) – создает диапазон из номеров строк в случае, когда это первая строка в объединенной ячейке столбца F и слова «ЛОЖЬ» в противном случае (например, 3,ЛОЖЬ,ЛОЖЬ,6,ЛОЖЬ,…);
2) ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104))) - ищет числа 3-104 в массиве из п.1 с неуказанным типом сопоставления у ПОИСКПОЗ. Результат – массив из п.1, но слово «ЛОЖЬ» меняется на предшествующее ему число, а числа уменьшаются на 2 (т.к. первое значение в СТРОКА($F$3:$... - тройка) (например, 1,1,1,4,4,…);
3) ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2) – при равенстве массива в столбце F слову «пишпек» из G111 присваиваем значению в этом массиве номер его строки минус 2, иначе – «ЛОЖЬ»;
4) ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);) – ищем массив из п.2 в массиве из п.3 с типом сопоставления 0. Если нашли, то какое-то число (номер позиции), если не нашли, то ошибка;
5) ЕЧИСЛО(п.4)*$C$3:$C$104*($D$3:$D$104=H$110) – создает массив из нужных нам для сложения чисел из столбца С (в случае, если соответствующее ему значение в столбце D равно значению «92» из Н110 и в п.4 есть какое-то число) и нулей;
6) Складываем все, что получилось в п.5.

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата (Saff)
мечтаю теперь получить мастер класс по этой формуле ))

Где-то так:
Формула считает 2 диапазона; B:F и H:L, поэтому объяснялку пишем только для одного. Для второго все аналогично.
Код
=СУММ(ЕЧИСЛО(ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);))*$C$3:$C$104*($D$3:$D$104=H$110))

Делим на кусочки:
1) ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)) – создает диапазон из номеров строк в случае, когда это первая строка в объединенной ячейке столбца F и слова «ЛОЖЬ» в противном случае (например, 3,ЛОЖЬ,ЛОЖЬ,6,ЛОЖЬ,…);
2) ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104))) - ищет числа 3-104 в массиве из п.1 с неуказанным типом сопоставления у ПОИСКПОЗ. Результат – массив из п.1, но слово «ЛОЖЬ» меняется на предшествующее ему число, а числа уменьшаются на 2 (т.к. первое значение в СТРОКА($F$3:$... - тройка) (например, 1,1,1,4,4,…);
3) ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2) – при равенстве массива в столбце F слову «пишпек» из G111 присваиваем значению в этом массиве номер его строки минус 2, иначе – «ЛОЖЬ»;
4) ПОИСКПОЗ(ПОИСКПОЗ(СТРОКА($F$3:$F$104);ЕСЛИ($F$3:$F$104<>"";СТРОКА($F$3:$F$104)));ЕСЛИ($F$3:$F$104=$G111;СТРОКА($F$3:$F$104)-2);) – ищем массив из п.2 в массиве из п.3 с типом сопоставления 0. Если нашли, то какое-то число (номер позиции), если не нашли, то ошибка;
5) ЕЧИСЛО(п.4)*$C$3:$C$104*($D$3:$D$104=H$110) – создает массив из нужных нам для сложения чисел из столбца С (в случае, если соответствующее ему значение в столбце D равно значению «92» из Н110 и в п.4 есть какое-то число) и нулей;
6) Складываем все, что получилось в п.5.
Автор - _Boroda_
Дата добавления - 24.04.2013 в 15:44
mileonn Дата: Воскресенье, 25.01.2015, 23:29 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

_Boroda_, Доброй ночи! Если не сложно помогите с проблемой. Вопрос вот в чем будет ли формула СУМЕСЛИМН работать со множеством объединенных ячеек или она все-таки таки предусматривает работу с не объединенными ячейками? Есть файл, файл делал я, понимаю что лучше бы переделать и можно избежать множество проблем, но переделать в другой вид не дают. ((( Есть лист Справочник на нем есть определенный список ЛИМИТЫ. Задача состоит в том чтобы по двум заданным критериям в определенный лист прибавлялась сумма соответствующая дате листа и соответствующей строке.
[moder]Читаем Правила форума. Создаём свою тему, прикладываем файл с примером. Эта тема закрыта[/moder]
 
Ответить
Сообщение_Boroda_, Доброй ночи! Если не сложно помогите с проблемой. Вопрос вот в чем будет ли формула СУМЕСЛИМН работать со множеством объединенных ячеек или она все-таки таки предусматривает работу с не объединенными ячейками? Есть файл, файл делал я, понимаю что лучше бы переделать и можно избежать множество проблем, но переделать в другой вид не дают. ((( Есть лист Справочник на нем есть определенный список ЛИМИТЫ. Задача состоит в том чтобы по двум заданным критериям в определенный лист прибавлялась сумма соответствующая дате листа и соответствующей строке.
[moder]Читаем Правила форума. Создаём свою тему, прикладываем файл с примером. Эта тема закрыта[/moder]
Автор - mileonn
Дата добавления - 25.01.2015 в 23:29
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Функция "Суммеслимн" и объединенные ячейки.
  • Страница 1 из 1
  • 1
Поиск:

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