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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос данных из строк в столбцы (Формулы/Formulas)
Перенос данных из строк в столбцы
mkotik Дата: Вторник, 14.05.2019, 16:28 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день, Уважаемые знатоки!
Есть огромный список (~1 млн) Клиентов. К этим клиентам привязаны Контрагенты (от 1 до 50)
У каждого Контрагента есть свой ОКВЭД (значения тип текст и 0).
Требуется получить таблицу или сводную таблицу Клиентов с уникальными ОКВЭД

В случае повтора по ОКВЭД по Клиенту - переносить не требуется (если 0 и список есть - не переносить). В случае неодинаковых записей по ОКВЭД - перенос в отдельный столбец.
Каким образом лучше это сделать?
Файл во вложении
Заранее благодарен за ответ
К сообщению приложен файл: 1118603.xlsx (10.1 Kb)
 
Ответить
СообщениеДобрый день, Уважаемые знатоки!
Есть огромный список (~1 млн) Клиентов. К этим клиентам привязаны Контрагенты (от 1 до 50)
У каждого Контрагента есть свой ОКВЭД (значения тип текст и 0).
Требуется получить таблицу или сводную таблицу Клиентов с уникальными ОКВЭД

В случае повтора по ОКВЭД по Клиенту - переносить не требуется (если 0 и список есть - не переносить). В случае неодинаковых записей по ОКВЭД - перенос в отдельный столбец.
Каким образом лучше это сделать?
Файл во вложении
Заранее благодарен за ответ
Автор - mkotik
Дата добавления - 14.05.2019 в 16:28
Светлый Дата: Вторник, 14.05.2019, 18:56 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Более точно название звучало бы так: Перенос уникальных данных из строк в столбцы. Лучше исправить.
Массивная формула для A2 и вниз:
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!A:A;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ('Как есть'!A$1:A$9999;'Как есть'!A$1:A$9999;);)=СТРОКА($1:$9999);СТРОКА($1:$9999));СТРОКА(A2)));"")
Для B2 и вниз и вправо:
Код
=ЕСЛИ(A2="";"";ЕСЛИОШИБКА(ИНДЕКС('Как есть'!$C:$C;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ('Как есть'!$C$1:$C$9999;ЕСЛИ($A2='Как есть'!$A$1:$A$9999;'Как есть'!$C$1:$C$9999););)=СТРОКА($1:$9999);СТРОКА($1:$9999));СТОЛБЕЦ(A2)));""))
Надо будет добавить ещё по паре девяток, но считать будет долго.
К сообщению приложен файл: 1118603-1.xlsx (12.4 Kb)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
Более точно название звучало бы так: Перенос уникальных данных из строк в столбцы. Лучше исправить.
Массивная формула для A2 и вниз:
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!A:A;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ('Как есть'!A$1:A$9999;'Как есть'!A$1:A$9999;);)=СТРОКА($1:$9999);СТРОКА($1:$9999));СТРОКА(A2)));"")
Для B2 и вниз и вправо:
Код
=ЕСЛИ(A2="";"";ЕСЛИОШИБКА(ИНДЕКС('Как есть'!$C:$C;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСКПОЗ('Как есть'!$C$1:$C$9999;ЕСЛИ($A2='Как есть'!$A$1:$A$9999;'Как есть'!$C$1:$C$9999););)=СТРОКА($1:$9999);СТРОКА($1:$9999));СТОЛБЕЦ(A2)));""))
Надо будет добавить ещё по паре девяток, но считать будет долго.
Автор - Светлый
Дата добавления - 14.05.2019 в 18:56
mkotik Дата: Среда, 15.05.2019, 08:47 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Светлый, огромное спасибо!
Сегодня попробую на больших данных
 
Ответить
СообщениеСветлый, огромное спасибо!
Сегодня попробую на больших данных
Автор - mkotik
Дата добавления - 15.05.2019 в 08:47
_Boroda_ Дата: Среда, 15.05.2019, 09:26 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Я так понял, что если допустим у клиента 4 только 0, то его вообще показывать не нужно
Для А2 и ниже
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!A$2:A$99999;ПОИСКПОЗ(;СЧЁТЕСЛИ(A$1:A1;ЕСЛИ('Как есть'!C$2:C$99999<>0;'Как есть'!A$2:A$99999;A1));));"")

Для В2 и вниз-вправо
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!$C$2:$C$9999;ПОИСКПОЗ(;СЧЁТЕСЛИ($A2:A2;ЕСЛИ(('Как есть'!$C$2:$C$9999<>0)*('Как есть'!$A$2:$A$9999=$A2);'Как есть'!$C$2:$C$9999;$A2));));"")


А вообще-то на таких объемах формулы помрут. Нужен макрос. Или, если это разовая или не очень частая операция, то
1. Выделяем столбцы А:С, Данные - Удалить дубликаты - снимаем галку "Контрагенты" - ОК
2. Ставим автофильтр, сортируем столбец ОКВЭД От Я до А
3. Контрл F, найти - 0, кнопка "Параметры", ставим галку "Ячейка целиком". Попадаем в первый ноль
4. Закрываем окошко "Найти", встаем в найденную ячейку с первым нулем, жмем Контрл Шифт СтрелкаВниз, удаляем выделенные строки
5. Сортируем А-Я по столбцу Клиент (для красоты можно отсортировать предварительно по АЯ по столбцу ОКВЭД)
6. В ячейке D2 пишем формулу
Код
=ТЕКСТ(ЕСЛИ(A2=A1;ЛЕВБ(D1;5))+1;"00000")&A2
и тянем ее вниз до конца таблицы
7. Копируем столбец А, вставляем его в столбец Н, Данные - Удалить дубликаты
8. В ячейку I2 пишем формулу
Код
=ЕСЛИОШИБКА(ИНДЕКС($C:$C;ПОИСКПОЗ(ТЕКСТ(СТОЛБЕЦ(A2);"00000")&$H2;$D:$D;));"")
и тянем ее вправо и вниз
Результат в файле _2
К сообщению приложен файл: 1118603_1.xlsx (13.7 Kb) · 1118603_2.xlsx (15.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Я так понял, что если допустим у клиента 4 только 0, то его вообще показывать не нужно
Для А2 и ниже
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!A$2:A$99999;ПОИСКПОЗ(;СЧЁТЕСЛИ(A$1:A1;ЕСЛИ('Как есть'!C$2:C$99999<>0;'Как есть'!A$2:A$99999;A1));));"")

Для В2 и вниз-вправо
Код
=ЕСЛИОШИБКА(ИНДЕКС('Как есть'!$C$2:$C$9999;ПОИСКПОЗ(;СЧЁТЕСЛИ($A2:A2;ЕСЛИ(('Как есть'!$C$2:$C$9999<>0)*('Как есть'!$A$2:$A$9999=$A2);'Как есть'!$C$2:$C$9999;$A2));));"")


А вообще-то на таких объемах формулы помрут. Нужен макрос. Или, если это разовая или не очень частая операция, то
1. Выделяем столбцы А:С, Данные - Удалить дубликаты - снимаем галку "Контрагенты" - ОК
2. Ставим автофильтр, сортируем столбец ОКВЭД От Я до А
3. Контрл F, найти - 0, кнопка "Параметры", ставим галку "Ячейка целиком". Попадаем в первый ноль
4. Закрываем окошко "Найти", встаем в найденную ячейку с первым нулем, жмем Контрл Шифт СтрелкаВниз, удаляем выделенные строки
5. Сортируем А-Я по столбцу Клиент (для красоты можно отсортировать предварительно по АЯ по столбцу ОКВЭД)
6. В ячейке D2 пишем формулу
Код
=ТЕКСТ(ЕСЛИ(A2=A1;ЛЕВБ(D1;5))+1;"00000")&A2
и тянем ее вниз до конца таблицы
7. Копируем столбец А, вставляем его в столбец Н, Данные - Удалить дубликаты
8. В ячейку I2 пишем формулу
Код
=ЕСЛИОШИБКА(ИНДЕКС($C:$C;ПОИСКПОЗ(ТЕКСТ(СТОЛБЕЦ(A2);"00000")&$H2;$D:$D;));"")
и тянем ее вправо и вниз
Результат в файле _2
Автор - _Boroda_
Дата добавления - 15.05.2019 в 09:26
mkotik Дата: Среда, 15.05.2019, 09:32 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, Большое спасибо Вам за постоянное участие!
 
Ответить
Сообщение_Boroda_, Большое спасибо Вам за постоянное участие!
Автор - mkotik
Дата добавления - 15.05.2019 в 09:32
_Boroda_ Дата: Среда, 15.05.2019, 09:53 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Игорь, посмотрите в моем посте выше, я дописал его немного

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИгорь, посмотрите в моем посте выше, я дописал его немного
Автор - _Boroda_
Дата добавления - 15.05.2019 в 09:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Перенос данных из строк в столбцы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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