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

Вход

Регистрация

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

 

= Мир MS Excel/Сбор данных из несколькиз таблиц - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сбор данных из несколькиз таблиц (Формулы/Formulas)
Сбор данных из несколькиз таблиц
Dron_S Дата: Четверг, 01.10.2015, 12:33 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Добрый день.
Есть формула:
Код
=СУММ(ВПР(B2;'Таблица1'!$A$4:$AG$91;4;0)*C2;ВПР(B3;'Таблица1'!$A$4:$AG$91;4;0)*C3;ВПР(B4;'Таблица1'!$A$4:$AG$91;4;0)*C4;ВПР(B5;'Таблица1'!$A$4:$AG$91;4;0)*C5)

Как видно, она состоит нескольких формул ВПР.
Задача: преобразовать эту формулу так, что не перечислять руками все ВПР, а чтобы он сам автоматически суммировал диапазон ячеек вот таким образом через ВПР.

В PHP это выглядело бы так:
[vba]
Код
function groupedSumm(array $cellRange)
{
        $result = 0;
        foreach($cellRange as $cellID) $result += VLOOKUP($cellID, 'Таблица1'!$A$4:$AG$91, 4, false);
        return $result;
}
[/vba]
В excel без использования VBA можно сделать?


Сообщение отредактировал Dron_S - Четверг, 01.10.2015, 15:46
 
Ответить
СообщениеДобрый день.
Есть формула:
Код
=СУММ(ВПР(B2;'Таблица1'!$A$4:$AG$91;4;0)*C2;ВПР(B3;'Таблица1'!$A$4:$AG$91;4;0)*C3;ВПР(B4;'Таблица1'!$A$4:$AG$91;4;0)*C4;ВПР(B5;'Таблица1'!$A$4:$AG$91;4;0)*C5)

Как видно, она состоит нескольких формул ВПР.
Задача: преобразовать эту формулу так, что не перечислять руками все ВПР, а чтобы он сам автоматически суммировал диапазон ячеек вот таким образом через ВПР.

В PHP это выглядело бы так:
[vba]
Код
function groupedSumm(array $cellRange)
{
        $result = 0;
        foreach($cellRange as $cellID) $result += VLOOKUP($cellID, 'Таблица1'!$A$4:$AG$91, 4, false);
        return $result;
}
[/vba]
В excel без использования VBA можно сделать?
Автор - Dron_S
Дата добавления - 01.10.2015 в 12:33
YouGreed Дата: Четверг, 01.10.2015, 12:35 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 589
Репутация: 123 ±
Замечаний: 0% ±

Excel 2010
Dron_S, Файл с примером пожалуйста. Массивная формула я думаю, вытянет то, что Вам нужно и перемножит и переплюсует)
 
Ответить
СообщениеDron_S, Файл с примером пожалуйста. Массивная формула я думаю, вытянет то, что Вам нужно и перемножит и переплюсует)
Автор - YouGreed
Дата добавления - 01.10.2015 в 12:35
Dron_S Дата: Четверг, 01.10.2015, 12:41 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Массивная формула не нужна, потому что это не правильно и потому, что диапазон ячеек может содержать и 1000 ячеек - что, всю 1000 руками прописывать.
Жаль, что мелкомяхкие не додумались сделать SQL-запросы к листам.
 
Ответить
СообщениеМассивная формула не нужна, потому что это не правильно и потому, что диапазон ячеек может содержать и 1000 ячеек - что, всю 1000 руками прописывать.
Жаль, что мелкомяхкие не додумались сделать SQL-запросы к листам.
Автор - Dron_S
Дата добавления - 01.10.2015 в 12:41
buchlotnik Дата: Четверг, 01.10.2015, 12:51 | Сообщение № 4
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
это не правильно
с чего вы это взяли?
Цитата
что, всю 1000 руками прописывать
нет, для этого и существуют формулы массива. Пример файла будет?
 
Ответить
Сообщение
Цитата
это не правильно
с чего вы это взяли?
Цитата
что, всю 1000 руками прописывать
нет, для этого и существуют формулы массива. Пример файла будет?
Автор - buchlotnik
Дата добавления - 01.10.2015 в 12:51
Pelena Дата: Четверг, 01.10.2015, 12:58 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 19185
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Dron_S, дайте теме более конкретное название в соответствии с Правилами форума

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеDron_S, дайте теме более конкретное название в соответствии с Правилами форума
Автор - Pelena
Дата добавления - 01.10.2015 в 12:58
AlexM Дата: Четверг, 01.10.2015, 13:07 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация: 1116 ±
Замечаний: 0% ±

Excel 2003
Dron_S, Если в столбце А на листе Таблица1 значения текстовые, то ваша формула сокращается так
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(B$2:B$5&"";));Таблица1!A$4:AG$91;4;)*C$2:C$5)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеDron_S, Если в столбце А на листе Таблица1 значения текстовые, то ваша формула сокращается так
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(B$2:B$5&"";));Таблица1!A$4:AG$91;4;)*C$2:C$5)
Автор - AlexM
Дата добавления - 01.10.2015 в 13:07
Dron_S Дата: Четверг, 01.10.2015, 13:28 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
AlexM, Большее спасибо! То что нужно.
А примера пока не будет.
 
Ответить
СообщениеAlexM, Большее спасибо! То что нужно.
А примера пока не будет.
Автор - Dron_S
Дата добавления - 01.10.2015 в 13:28
AlexM Дата: Четверг, 01.10.2015, 13:34 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация: 1116 ±
Замечаний: 0% ±

Excel 2003
Чтобы формулу не переписывать, если добавятся значения в столбцах В и С можно ее дополнить так
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(СМЕЩ(B$2;;;СЧЁТЗ(B$2:B$999))&"";));Таблица1!A$4:AG$91;4;)*СМЕЩ(C$2;;;СЧЁТЗ(B$2:B$999)))


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЧтобы формулу не переписывать, если добавятся значения в столбцах В и С можно ее дополнить так
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(СМЕЩ(B$2;;;СЧЁТЗ(B$2:B$999))&"";));Таблица1!A$4:AG$91;4;)*СМЕЩ(C$2;;;СЧЁТЗ(B$2:B$999)))
Автор - AlexM
Дата добавления - 01.10.2015 в 13:34
Dron_S Дата: Четверг, 01.10.2015, 14:33 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Оказалось все не так просто, как я думал. Пример во вложении.
Книга состоит из 3х листов. На каждом по одной таблице. Результат нужно записать на лист "Итог". Расскажу поэтапно на примере одной сущности в терминах SQL.
1. К таблице "Множители" нужно присоединить (JOIN) таблицу "КлючЗначение" по полю "Ключ". Промежуточная таблица содержит поля: Сущность|Значение|Множитель.
2. Далее нужно для каждой строки из этой таблицы найти произведение - Значение*Множитель и просуммировать полученные произведения, группируя по полю "Сущность" (SUM() ... GROUP BY). Промежуточный результат: Сущность|Произведение
3. Поле "Произведение" записать в таблицу "Итог" для каждой сущности соответственно.
К сообщению приложен файл: 4901194.xlsx (13.6 Kb)
 
Ответить
СообщениеОказалось все не так просто, как я думал. Пример во вложении.
Книга состоит из 3х листов. На каждом по одной таблице. Результат нужно записать на лист "Итог". Расскажу поэтапно на примере одной сущности в терминах SQL.
1. К таблице "Множители" нужно присоединить (JOIN) таблицу "КлючЗначение" по полю "Ключ". Промежуточная таблица содержит поля: Сущность|Значение|Множитель.
2. Далее нужно для каждой строки из этой таблицы найти произведение - Значение*Множитель и просуммировать полученные произведения, группируя по полю "Сущность" (SUM() ... GROUP BY). Промежуточный результат: Сущность|Произведение
3. Поле "Произведение" записать в таблицу "Итог" для каждой сущности соответственно.
Автор - Dron_S
Дата добавления - 01.10.2015 в 14:33
Pelena Дата: Четверг, 01.10.2015, 15:02 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19185
Репутация: 4420 ±
Замечаний: ±

Excel 365 & Mac Excel
Прошу помогающих больше не отвечать, пока не будет исправлено название темы, о чём было сказано ещё в посте №5.
Ответы будут удаляться

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПрошу помогающих больше не отвечать, пока не будет исправлено название темы, о чём было сказано ещё в посте №5.
Ответы будут удаляться
Автор - Pelena
Дата добавления - 01.10.2015 в 15:02
Dron_S Дата: Четверг, 01.10.2015, 15:32 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Ох пелена... Исправь сама на более подходящее название, вы же знатоки excel, а не я.
[moder]При чём здесь знатоки/не знатоки? Кто лучше может сформулировать суть ВАШЕЙ задачи, Вы или я?[/moder]


Сообщение отредактировал Pelena - Четверг, 01.10.2015, 15:36
 
Ответить
СообщениеОх пелена... Исправь сама на более подходящее название, вы же знатоки excel, а не я.
[moder]При чём здесь знатоки/не знатоки? Кто лучше может сформулировать суть ВАШЕЙ задачи, Вы или я?[/moder]
Автор - Dron_S
Дата добавления - 01.10.2015 в 15:32
Dron_S Дата: Четверг, 01.10.2015, 15:48 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Уважаемая, название изменено. Довольна?
[moder]Dron_S, не язвите, правила для всех одинаковы!
Кстати, советую Вам обратить особое внимание на пункт 6![/moder]


Сообщение отредактировал Manyasha - Четверг, 01.10.2015, 15:56
 
Ответить
СообщениеУважаемая, название изменено. Довольна?
[moder]Dron_S, не язвите, правила для всех одинаковы!
Кстати, советую Вам обратить особое внимание на пункт 6![/moder]
Автор - Dron_S
Дата добавления - 01.10.2015 в 15:48
YouGreed Дата: Четверг, 01.10.2015, 15:57 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 589
Репутация: 123 ±
Замечаний: 0% ±

Excel 2010
Dron_S, Не нужно ругаться. Модифицировал формулу Алексея.
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(Множители!$B$2:$B$7&"";));КлючЗначение!$A$2:$B$7;2;)*(Множители!$A$2:$A$7=Итог!$A3)*Множители!$C$2:$C$7)


Файл проверки с доп столбцом, файл 2
К сообщению приложен файл: 8986505.xlsx (13.7 Kb) · _____.xlsx (13.9 Kb)


Сообщение отредактировал YouGreed - Четверг, 01.10.2015, 15:59
 
Ответить
СообщениеDron_S, Не нужно ругаться. Модифицировал формулу Алексея.
Код
=СУММПРОИЗВ(ВПР(Т(ИНДЕКС(Множители!$B$2:$B$7&"";));КлючЗначение!$A$2:$B$7;2;)*(Множители!$A$2:$A$7=Итог!$A3)*Множители!$C$2:$C$7)


Файл проверки с доп столбцом, файл 2
Автор - YouGreed
Дата добавления - 01.10.2015 в 15:57
Dron_S Дата: Четверг, 01.10.2015, 17:16 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 11
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
YouGreed, спасибо, работает!
 
Ответить
СообщениеYouGreed, спасибо, работает!
Автор - Dron_S
Дата добавления - 01.10.2015 в 17:16
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сбор данных из несколькиз таблиц (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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