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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир 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
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Цитата Штурмовик, 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 (11.0 Kb)

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

Сообщение отредактировал Штурмовик - Пятница, 12.01.2018, 16:56
 
Ответить
Сообщениефайл прикрепил
Автор - Штурмовик
Дата добавления - 12.01.2018 в 16:54
Che79 Дата: Пятница, 12.01.2018, 16:55 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

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

Делай нормально и будет нормально!
 
Ответить
Сообщение
Цитата Штурмовик, 12.01.2018 в 16:54, в сообщении № 3 ( писал(а)):
файл прикрепил
снова ничего.
Цитата Che79, 12.01.2018 в 16:52, в сообщении № 2 ( писал(а)):
обратите внимание на размер. Д.б. не более 100 кб.
Автор - Che79
Дата добавления - 12.01.2018 в 16:55
Штурмовик Дата: Пятница, 12.01.2018, 16:57 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

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

исправился

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

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

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

Делай нормально и будет нормально!
 
Ответить
СообщениеШтурмовик, уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?
Автор - Che79
Дата добавления - 12.01.2018 в 17:05
Che79 Дата: Пятница, 12.01.2018, 17:10 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Такой вариант. В С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 (11.3 Kb)

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

Сообщение отредактировал 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
Цитата Che79, 12.01.2018 в 17:05, в сообщении № 6 ( писал(а)):
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?

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

пытаюсь освоить
 
Ответить
Сообщение
Цитата Che79, 12.01.2018 в 17:05, в сообщении № 6 ( писал(а)):
уточните, пжл, такой момент. У Вас в исходной и итоговой таблицах названия поставщиков не совпадают. Это так и задумано?

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

Excel 2016
Цитата Che79, 12.01.2018 в 17:10, в сообщении № 7 ( писал(а)):
Такой вариант. В С8 и протянуть

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

пытаюсь освоить
 
Ответить
Сообщение
Цитата Che79, 12.01.2018 в 17:10, в сообщении № 7 ( писал(а)):
Такой вариант. В С8 и протянуть

а как можно понять (расшифровать) формулу? Логику хочется понять...
Автор - Штурмовик
Дата добавления - 12.01.2018 в 17:26
Che79 Дата: Пятница, 12.01.2018, 17:39 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
СУММПРОИЗВ(), в данном случае, перемножает массив сумм платежей и массивы доп. условий по заданным критериям, которые дают 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
Цитата Che79, 12.01.2018 в 17:39, в сообщении № 10 ( писал(а)):
СУММПРОИЗВ(),

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

пытаюсь освоить
 
Ответить
Сообщение
Цитата Che79, 12.01.2018 в 17:39, в сообщении № 10 ( писал(а)):
СУММПРОИЗВ(),

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

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

пытаюсь освоить
 
Ответить
СообщениеДобрый день!
СУММПРОИЗВ() не работает при увеличении диапазонов. Или я не правильно прописываю синтаксис. Есть ли другие варианты?
Исходные данные и итоговая таблица (на разных листах) во вложенном файле.
Автор - Штурмовик
Дата добавления - 15.01.2018 в 12:54
Che79 Дата: Понедельник, 15.01.2018, 13:06 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Здравствуйте.
Проверяйте. Для С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
Цитата Che79, 15.01.2018 в 13:06, в сообщении № 13 ( писал(а)):
Проверяйте. Для С8

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

пытаюсь освоить
 
Ответить
Сообщение
Цитата Che79, 15.01.2018 в 13:06, в сообщении № 13 ( писал(а)):
Проверяйте. Для С8

Считает, но только сумму без условия по дате. Т.е. если даты по строке 27 будут разные то это не учитывается, а считается вся сумма по этой строке.
Автор - Штурмовик
Дата добавления - 15.01.2018 в 13:19
Che79 Дата: Понедельник, 15.01.2018, 13:20 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

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

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

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

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

Excel 2016
Цитата Che79, 15.01.2018 в 13:20, в сообщении № 15 ( писал(а)):
Если исходные данные расположить немного по-другому, то результат легко формируется сводной таблицей (см. файл)

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

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

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

2013 Win, 365 Mac
тогда так
=СУММПРОИЗВ((($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 (11.3 Kb)

Делай нормально и будет нормально!
 
Ответить
Сообщениетогда так
=СУММПРОИЗВ((($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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 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 (12.9 Kb)
 
Ответить
Сообщение
=СУММЕСЛИ(ИНДЕКС($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
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

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

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

Сообщение отредактировал 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 из 2
  • 1
  • 2
  • »
Поиск:

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