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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос данных из таблицы по условиям в заданный столбец (Формулы/Formulas)
Перенос данных из таблицы по условиям в заданный столбец
Штурмовик Дата: Пятница, 12.01.2018, 16:50 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день.

Не могу сообразить как прописать формулами:
из исходной таблицы нужно выбрать не пустые значения по поставщику и виду а перенести данные по сумме в столбец с датой платежа.

Пример во вложенном файле.


пытаюсь освоить
 
Ответить
СообщениеДобрый день.

Не могу сообразить как прописать формулами:
из исходной таблицы нужно выбрать не пустые значения по поставщику и виду а перенести данные по сумме в столбец с датой платежа.

Пример во вложенном файле.

Автор - Штурмовик
Дата добавления - 12.01.2018 в 16:50
Che79 Дата: Пятница, 12.01.2018, 16:52 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Цитата Штурмовик, 12.01.2018 в 16:50, в сообщении № 1 ()
Пример во вложенном файле
Штурмовик, файл не прикрепился, обратите внимание на размер. Д.б. не более 100 кб.


Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата Штурмовик, 12.01.2018 в 16:50, в сообщении № 1 ()
Пример во вложенном файле
Штурмовик, файл не прикрепился, обратите внимание на размер. Д.б. не более 100 кб.

Автор - Che79
Дата добавления - 12.01.2018 в 16:52
Штурмовик Дата: Пятница, 12.01.2018, 16:54 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
файл прикрепил
К сообщению приложен файл: 123456.xlsx(11Kb)


пытаюсь освоить

Сообщение отредактировал Штурмовик - Пятница, 12.01.2018, 16:56
 
Ответить
Сообщениефайл прикрепил

Автор - Штурмовик
Дата добавления - 12.01.2018 в 16:54
Che79 Дата: Пятница, 12.01.2018, 16:55 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Цитата Штурмовик, 12.01.2018 в 16:54, в сообщении № 3 ()
файл прикрепил
снова ничего.
обратите внимание на размер. Д.б. не более 100 кб.


Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата Штурмовик, 12.01.2018 в 16:54, в сообщении № 3 ()
файл прикрепил
снова ничего.
обратите внимание на размер. Д.б. не более 100 кб.

Автор - Che79
Дата добавления - 12.01.2018 в 16:55
Штурмовик Дата: Пятница, 12.01.2018, 16:57 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
снова ничего.
Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):

исправился


пытаюсь освоить
 
Ответить
Сообщение
снова ничего.
Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):

исправился

Автор - Штурмовик
Дата добавления - 12.01.2018 в 16:57
Che79 Дата: Пятница, 12.01.2018, 17:05 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Штурмовик, уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?


Делай нормально и будет нормально!
 
Ответить
СообщениеШтурмовик, уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?

Автор - Che79
Дата добавления - 12.01.2018 в 17:05
Che79 Дата: Пятница, 12.01.2018, 17:10 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Такой вариант. В С8 и протянуть
Код
=СУММПРОИЗВ($C$27:$C$30*($D$27:$D$30=C$7)*(ЛЕВБ($A$27:$A$30;8)=ЛЕВБ($A8;8))*($B$27:$B$30=$B8))
+ формат ячейки 0;;; для скрытия нулей в итоговой таблице
К сообщению приложен файл: 123456_1.xlsx(11Kb)


Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Пятница, 12.01.2018, 17:10
 
Ответить
СообщениеТакой вариант. В С8 и протянуть
Код
=СУММПРОИЗВ($C$27:$C$30*($D$27:$D$30=C$7)*(ЛЕВБ($A$27:$A$30;8)=ЛЕВБ($A8;8))*($B$27:$B$30=$B8))
+ формат ячейки 0;;; для скрытия нулей в итоговой таблице

Автор - Che79
Дата добавления - 12.01.2018 в 17:10
Штурмовик Дата: Пятница, 12.01.2018, 17:23 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?

поставщик и тип сырья должны совпадать - это в файле я ошибся.


пытаюсь освоить
 
Ответить
Сообщение
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?

поставщик и тип сырья должны совпадать - это в файле я ошибся.

Автор - Штурмовик
Дата добавления - 12.01.2018 в 17:23
Штурмовик Дата: Пятница, 12.01.2018, 17:26 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Такой вариант. В С8 и протянуть

а как можно понять (расшифровать) формулу? Логику хочется понять...


пытаюсь освоить
 
Ответить
Сообщение
Такой вариант. В С8 и протянуть

а как можно понять (расшифровать) формулу? Логику хочется понять...

Автор - Штурмовик
Дата добавления - 12.01.2018 в 17:26
Che79 Дата: Пятница, 12.01.2018, 17:39 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
СУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 1 (ИСТИНА), если критерий выполняется и 0 (ЛОЖЬ) если критерий не выполняется. Таким образом на выходе получим массив платежей и массивы (в нашем случае их будет 3) из 0 и 1. Далее эти нули или единицы перемножаются с суммами платежей и в итоге мы получаем только ту сумму, которая удовлетворяет всем критериям сразу - дате, поставщику и типу сырья.
Также на сайте есть статья Суммирование по нескольким критериям.


Делай нормально и будет нормально!
 
Ответить
СообщениеСУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 1 (ИСТИНА), если критерий выполняется и 0 (ЛОЖЬ) если критерий не выполняется. Таким образом на выходе получим массив платежей и массивы (в нашем случае их будет 3) из 0 и 1. Далее эти нули или единицы перемножаются с суммами платежей и в итоге мы получаем только ту сумму, которая удовлетворяет всем критериям сразу - дате, поставщику и типу сырья.
Также на сайте есть статья Суммирование по нескольким критериям.

Автор - Che79
Дата добавления - 12.01.2018 в 17:39
Штурмовик Дата: Пятница, 12.01.2018, 17:43 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
СУММПРОИЗВ(),

Понял. Спасибо.


пытаюсь освоить
 
Ответить
Сообщение
СУММПРОИЗВ(),

Понял. Спасибо.

Автор - Штурмовик
Дата добавления - 12.01.2018 в 17:43
Штурмовик Дата: Понедельник, 15.01.2018, 12:54 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты?
Исходные данные и итоговая таблица (на разных листах) во вложенном файле.
К сообщению приложен файл: 1234567.xlsx(11Kb)


пытаюсь освоить
 
Ответить
СообщениеДобрый день!
СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты?
Исходные данные и итоговая таблица (на разных листах) во вложенном файле.

Автор - Штурмовик
Дата добавления - 15.01.2018 в 12:54
Che79 Дата: Понедельник, 15.01.2018, 13:06 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Здравствуйте.
Проверяйте. Для С8
Код
=СУММПРОИЗВ(($C$27:$C$30+$E$27:$E$30+$G$27:$G$30+$I$27:$I$30)*($D$27:$D$30=C$7)*(($B$27:$B$30=$B8)*($A$27:$A$30=$A8)))


Делай нормально и будет нормально!
 
Ответить
СообщениеЗдравствуйте.
Проверяйте. Для С8
Код
=СУММПРОИЗВ(($C$27:$C$30+$E$27:$E$30+$G$27:$G$30+$I$27:$I$30)*($D$27:$D$30=C$7)*(($B$27:$B$30=$B8)*($A$27:$A$30=$A8)))

Автор - Che79
Дата добавления - 15.01.2018 в 13:06
Штурмовик Дата: Понедельник, 15.01.2018, 13:19 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Проверяйте. Для С8

Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.


пытаюсь освоить
 
Ответить
Сообщение
Проверяйте. Для С8

Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.

Автор - Штурмовик
Дата добавления - 15.01.2018 в 13:19
Che79 Дата: Понедельник, 15.01.2018, 13:20 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)
К сообщению приложен файл: 1234567_2.xlsx(15Kb)


Делай нормально и будет нормально!
 
Ответить
СообщениеЕсли исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)

Автор - Che79
Дата добавления - 15.01.2018 в 13:20
Штурмовик Дата: Понедельник, 15.01.2018, 13:32 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
ПОИСКПОПОЗ в данном случае может как то помочь?
К сообщению приложен файл: 2863741.xlsx(11Kb)


пытаюсь освоить
 
Ответить
СообщениеПОИСКПОПОЗ в данном случае может как то помочь?

Автор - Штурмовик
Дата добавления - 15.01.2018 в 13:32
Штурмовик Дата: Понедельник, 15.01.2018, 13:35 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)

В таком случае у меня исходная таблица будет неимоверных размеров, да и ее заполнение автоматизировать будет сложнее.


пытаюсь освоить
 
Ответить
Сообщение
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)

В таком случае у меня исходная таблица будет неимоверных размеров, да и ее заполнение автоматизировать будет сложнее.

Автор - Штурмовик
Дата добавления - 15.01.2018 в 13:35
Che79 Дата: Понедельник, 15.01.2018, 13:49 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
тогда так
Код
=СУММПРОИЗВ((($C$27:$C$30*($D$27:$D$30=C$7))+($E$27:$E$30*($F$27:$F$30=C$7))+($G$27:$G$30*($H$27:$H$30=C$7))+($I$27:$I$30*($J$27:$J$30=C$7)))*($B$27:$B$30=$B8)*($A$27:$A$30=$A8))
К сообщению приложен файл: 2863741_1.xlsx(11Kb)


Делай нормально и будет нормально!
 
Ответить
Сообщениетогда так
Код
=СУММПРОИЗВ((($C$27:$C$30*($D$27:$D$30=C$7))+($E$27:$E$30*($F$27:$F$30=C$7))+($G$27:$G$30*($H$27:$H$30=C$7))+($I$27:$I$30*($J$27:$J$30=C$7)))*($B$27:$B$30=$B8)*($A$27:$A$30=$A8))

Автор - Che79
Дата добавления - 15.01.2018 в 13:49
vikttur Дата: Понедельник, 15.01.2018, 14:25 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 2642
Репутация: 474 ±
Замечаний: 0% ±

Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;);C$7;ИНДЕКС($C$27:$I$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;))

Если вынести определение строки в отдельную ячейку:
Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;$B16;);C$7;ИНДЕКС($C$27:$I$30;$B16;))

А если строки не меняют порядка, то и номер строки вычислять не нужно:
Код
=СУММЕСЛИ($D27:$J27;C$7;$C27:$I27)
К сообщению приложен файл: 2596868.xlsx(13Kb)
 
Ответить
Сообщение
Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;);C$7;ИНДЕКС($C$27:$I$30;ПОИСКПОЗ($A8;$A$27:$A$30;)+ПОИСКПОЗ($B8;$B$8:$B$9;)-1;))

Если вынести определение строки в отдельную ячейку:
Код
=СУММЕСЛИ(ИНДЕКС($D$27:$J$30;$B16;);C$7;ИНДЕКС($C$27:$I$30;$B16;))

А если строки не меняют порядка, то и номер строки вычислять не нужно:
Код
=СУММЕСЛИ($D27:$J27;C$7;$C27:$I27)

Автор - vikttur
Дата добавления - 15.01.2018 в 14:25
Che79 Дата: Понедельник, 15.01.2018, 14:45 | Сообщение № 20
Группа: Проверенные
Ранг: Ветеран
Сообщений: 840
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007, 2013
Что-то я напрочь забыл про СУММЕСЛИ(), vikttur, спасибо за подсказку!
Тогда забываем про СУММПРОИЗВ(), в С8
Код
=СУММЕСЛИ($D19:$XFD19;C$7;$C19:$XFC19)
, в исходной таблице просто протягиваете вправо ячейки "Сумма", "Дата оплаты", а Итоговую таблицу протягивайте вправо по датам насколько нужно.
К сообщению приложен файл: 2596868_1.xlsx(13Kb)


Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Понедельник, 15.01.2018, 15:10
 
Ответить
СообщениеЧто-то я напрочь забыл про СУММЕСЛИ(), vikttur, спасибо за подсказку!
Тогда забываем про СУММПРОИЗВ(), в С8
Код
=СУММЕСЛИ($D19:$XFD19;C$7;$C19:$XFC19)
, в исходной таблице просто протягиваете вправо ячейки "Сумма", "Дата оплаты", а Итоговую таблицу протягивайте вправо по датам насколько нужно.

Автор - Che79
Дата добавления - 15.01.2018 в 14:45
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос данных из таблицы по условиям в заданный столбец (Формулы/Formulas)
Страница 1 из 212»
Поиск:

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