Распределение данных по условию - Мир MS Excel

Регистрация

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

= Мир MS Excel/Распределение данных по условию - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Распределение данных по условию (Формулы/Formulas)

Распределение данных по условию

Дата: Вторник, 21.03.2023, 09:32 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

Здравствуйте! Есть некий список сотрудников. В процессе своей работы данные сотрудники могут работать в разных местах по разным адресам - по адресу регистрации подразделения, по адресу его нахождения и по адресу пребывания, как указано в прилагаемом файле. Задача заключается в сборе показателей работы сотрудников по местам их работы. Сбор показателей заключается в направлении писем по указанным адресам.
Т.е. для удобства формирования таких писем, из исходного списка (столбцы с адресами) формируется список всех (т.е. уникальных) адресов, где могли работать сотрудники. Это сделано. И вот задача состоит в том, как к этим адресам привязать самих сотрудников, а главное - с указанием адреса (регистрации, нахождения, пребывания)?
Прошу по возможности решить вопрос стандартными средствами (формулами), за исключением PQ, VBA и сводных таблиц. Офис 2007.

К сообщению приложен файл: adresa.xlsx (12.5 Kb)

Ответить

Дата: Вторник, 21.03.2023, 09:49 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1394

Репутация: 320 ±

Замечаний: 0% ±

Excel 2010

Ivan2063, преобразуйте таблицу и используйте сводные

К сообщению приложен файл: 3393775.xlsx (21.4 Kb)

QIWI 9173973973

Ответить

Дата: Вторник, 21.03.2023, 09:55 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

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

Ответить

Дата: Вторник, 21.03.2023, 11:00 | Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Добрый день! Как понял для ячейки В2

Код

=ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$1)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$2)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$3)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$4)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$5)))

формула массивная, только для адреса регистрации

К сообщению приложен файл: 6258467.xlsx (15.2 Kb)

Главное не быть балабастиком

Сообщение отредактировал alexa1965 - Вторник, 21.03.2023, 11:34

Ответить

Дата: Вторник, 21.03.2023, 13:58 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

alexa1965, спасибо за уделенное время и помощь!! Скажите, а для адресов проживания и пребывания надо добавлять дополнительные условия в Вашу формулу?

Ответить

Дата: Вторник, 21.03.2023, 14:06 | Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

В принципе да для нахождения столбец Е заменить на F для пребывания на G, ну и соответственно заменить регистрацию на нахождение и пребывание, формула будет большая только

Главное не быть балабастиком

Сообщение отредактировал alexa1965 - Вторник, 21.03.2023, 14:08

Ответить

Дата: Вторник, 21.03.2023, 14:10 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

Голова закипает... Как это все склеить. Помогите чайнику

Ответить

Дата: Вторник, 21.03.2023, 14:26 | Сообщение № 8

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Как просили, но много лишнего текста выплывает

Код

=ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$1)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$2)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$3)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$4)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес регистрации)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!E$2:E$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$5)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес нахождения)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!F$2:F$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$1)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес нахождения)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!F$2:F$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$2)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес нахождения)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!F$2:F$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$3)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес нахождения)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!F$2:F$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$4)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес нахождения)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!F$2:F$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$5)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес пребывания)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!G$2:G$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$1)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес пребывания)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!G$2:G$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$2)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес пребывания)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!G$2:G$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$3)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес пребывания)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!G$2:G$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$4)))&" "&ИНДЕКС(СЦЕПИТЬ(Данные!A$1:A$1000;" - ";Данные!B$1:B$1000;" ";Данные!C$1:C$1000;" ";Данные!D$1:D$1000;" (адрес пребывания)");НАИМЕНЬШИЙ(ЕСЛИ(Данные!G$2:G$1000=A2;СТРОКА(Данные!A$2:A$1000);1000);СТРОКА(A$5)))

К сообщению приложен файл: 2229431.xlsx (16.2 Kb)

Главное не быть балабастиком

Ответить

Дата: Вторник, 21.03.2023, 14:30 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

Вы Гений!!! Это просто праздник какой-то!!! А скажите так и должно быть, что строка заканчивается СТРОКА(A$5)? Эх убрать бы еще эти ненужные дубли

Сообщение отредактировал Ivan2063 - Вторник, 21.03.2023, 14:44

Ответить

Дата: Вторник, 21.03.2023, 14:45 | Сообщение № 10

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

Да, скажите еще пожалуйста, в какое место нужно вставить СИМВОЛ(10)?

Ответить

Дата: Вторник, 21.03.2023, 14:51 | Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Строка А5 вытаскивает 5 значений по заданным параметрам, те 5 человек. А что такое символ(10)?

Главное не быть балабастиком

Ответить

Дата: Вторник, 21.03.2023, 14:56 | Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

символ(10) это перенос строки

Ответить

Дата: Вторник, 21.03.2023, 15:00 | Сообщение № 13

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

не пробовал, но думаю в формуле после СТРОКА(A$1)))&" "&, вместо пробела наверное...&" "&, - это пробел между зачениями выборки

Главное не быть балабастиком

Ответить

Дата: Вторник, 21.03.2023, 15:08 | Сообщение № 14

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

Да! Именно так как Вы сказали! Спасибо!!!

Ответить

Дата: Вторник, 21.03.2023, 17:39 | Сообщение № 15

Группа: Проверенные

Ранг: Старожил

Сообщений: 1276

Репутация: 246 ±

Замечаний: 0% ±

нет

Ivan2063, какой у вас офис?
Для того, где есть объединить()

Код

="Регистрации"&CHAR(10)&TEXTJOIN(" ";1;IFERROR(INDEX(Данные.$A$2:$H$20;SMALL(IF(Данные.$E$2:$E$11=A2;ROW($A$2:$A$20)-1;"");ROW($A$1:$A$20));{1;2;3;4;8});"")) & CHAR(10) & "Нахождения"&CHAR(10)&TEXTJOIN(" ";1;IFERROR(INDEX(Данные.$A$2:$H$20;SMALL(IF(Данные.$F$2:$F$11=A2;ROW($A$2:$A$20)-1;"");ROW($A$1:$A$20));{1;2;3;4;8});"")) & CHAR(10) & "Пребывания"&CHAR(10)&TEXTJOIN(" ";1;IFERROR(INDEX(Данные.$A$2:$H$20;SMALL(IF(Данные.$G$2:$G$11=A2;ROW($A$2:$A$20)-1;"");ROW($A$1:$A$20));{1;2;3;4;8});""))

добавил столбец h на первый лист, в нем символ переноса строки, как напрямую запихнуть его в формулу не придумал пока, если добавить еще 3 столбца с типом адреса, то вывод будет более похожим на ваш

К сообщению приложен файл: proverka_adresov_proba_1.xlsx (11.2 Kb)

Сообщение отредактировал bigor - Вторник, 21.03.2023, 17:53

Ответить

Дата: Вторник, 21.03.2023, 18:36 | Сообщение № 16

Группа: Пользователи

Ранг: Новичок

Сообщений: 22

Репутация: 0 ±

Замечаний: 20% ±

К сожалению нет ОБЪЕДИНИТЬ. У меня Офис 2007. Как можно это решить с эти офисом?

Ответить

Дата: Среда, 22.03.2023, 09:14 | Сообщение № 17

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Доброго дня! Подпилил формулу, чтобы убрать лишнее, но появились пробелы

К сообщению приложен файл: 9459816.xlsx (16.1 Kb)

Главное не быть балабастиком

Ответить

Распределение данных по условию (Формулы/Formulas)

Страница 1 из 1
1

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