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

Вход

Регистрация

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

 

= Мир MS Excel/Из списка в столбцы - Мир MS Excel

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

Excel 2013
Здравствуйте, уважаемые эксперты!
Нужна помощь в составлении формулы.

Есть страница с единственным столбцом, в котором смешаны Категории, Значения и пустые ячейки. Столбец более 1000 строк. Категории и значения могут повторяться.
На второй странице нужно разместить значения этого списка в два столбца соответственно – Категория и Значения (игнорируя пустые строки).
На третьей странице – список категорий (в алфавитном порядке, категории не повторяются)

В исходных данных - одно и тоже значение может быть в разных категориях, причём, несколько раз.
В результатах - каждому значению присваивается категория, в списке которой она стоит. Если одно и тоже значение было в разных категориях – оно должно встречаться столько раз, в скольки категориях находилось. Пара: Значение-Категория, должно находиться в результатах не более одного раза (даже если попадалось в исходном списке несколько раз в одной и той же категории).

Что из списка является категорией – сверяется по столбцу третьей страницы. Всё, что идёт в исходных данных ниже категории – это её значение. Пока не попадётся следующая категория - тогда эти значения будут находиться уже в ней.

Нужна простая или массивная формула. Без макросов.
Спасибо!
К сообщению приложен файл: ___.xlsx (10.2 Kb)
 
Ответить
СообщениеЗдравствуйте, уважаемые эксперты!
Нужна помощь в составлении формулы.

Есть страница с единственным столбцом, в котором смешаны Категории, Значения и пустые ячейки. Столбец более 1000 строк. Категории и значения могут повторяться.
На второй странице нужно разместить значения этого списка в два столбца соответственно – Категория и Значения (игнорируя пустые строки).
На третьей странице – список категорий (в алфавитном порядке, категории не повторяются)

В исходных данных - одно и тоже значение может быть в разных категориях, причём, несколько раз.
В результатах - каждому значению присваивается категория, в списке которой она стоит. Если одно и тоже значение было в разных категориях – оно должно встречаться столько раз, в скольки категориях находилось. Пара: Значение-Категория, должно находиться в результатах не более одного раза (даже если попадалось в исходном списке несколько раз в одной и той же категории).

Что из списка является категорией – сверяется по столбцу третьей страницы. Всё, что идёт в исходных данных ниже категории – это её значение. Пока не попадётся следующая категория - тогда эти значения будут находиться уже в ней.

Нужна простая или массивная формула. Без макросов.
Спасибо!

Автор - Strateg_ru
Дата добавления - 10.08.2017 в 20:04
_Boroda_ Дата: Четверг, 10.08.2017, 21:15 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
На третьем листе категории уже даны или их нужно вывести?

Если нужно вывести, то нужен какой-то признак, по которому категории отличаются от значений (и этот признак не должен быть цветом)
Это у Вас выгрузка из 1С?
Крайне желательно, чтобы Вы выложили файл реальный (названия можно заменить, цифры тоже, но только их, все форматы, всё месторасположение должно сохраниться)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНа третьем листе категории уже даны или их нужно вывести?

Если нужно вывести, то нужен какой-то признак, по которому категории отличаются от значений (и этот признак не должен быть цветом)
Это у Вас выгрузка из 1С?
Крайне желательно, чтобы Вы выложили файл реальный (названия можно заменить, цифры тоже, но только их, все форматы, всё месторасположение должно сохраниться)

Автор - _Boroda_
Дата добавления - 10.08.2017 в 21:15
InExSu Дата: Четверг, 10.08.2017, 23:28 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 648
Репутация: 96 ±
Замечаний: 0% ±

Excel 2010, 365
Без макросов

Правильно ли я Вас понял:
Страница это лист.
Есть список "категорий" на отдельном листе,
На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".
Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами.
?


Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac

Сообщение отредактировал InExSu - Четверг, 10.08.2017, 23:29
 
Ответить
Сообщение
Без макросов

Правильно ли я Вас понял:
Страница это лист.
Есть список "категорий" на отдельном листе,
На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".
Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами.
?

Автор - InExSu
Дата добавления - 10.08.2017 в 23:28
Strateg_ru Дата: Пятница, 11.08.2017, 00:41 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
На третьем листе категории уже даны или их нужно вывести?

Категории задаются пользователем. Цветом пометил для наглядности.
Формулы нужны только на втором листе.

Это у Вас выгрузка из 1С?

Нет, но система такая же кривая)
Это эмулируемый фрагмент из более сложного файла. Но форматирование по столбцам и листам соблюдено:
1. Общий список из заполненных категорий, одна под другой
2. Категория и значение
3. Список категорий, задаваемый пользователем вручную
 
Ответить
Сообщение
На третьем листе категории уже даны или их нужно вывести?

Категории задаются пользователем. Цветом пометил для наглядности.
Формулы нужны только на втором листе.

Это у Вас выгрузка из 1С?

Нет, но система такая же кривая)
Это эмулируемый фрагмент из более сложного файла. Но форматирование по столбцам и листам соблюдено:
1. Общий список из заполненных категорий, одна под другой
2. Категория и значение
3. Список категорий, задаваемый пользователем вручную

Автор - Strateg_ru
Дата добавления - 11.08.2017 в 00:41
Strateg_ru Дата: Пятница, 11.08.2017, 00:48 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Страница это лист.

Да, всё правильно.

Есть список "категорий" на отдельном листе

Да, это третий лист.

На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".

На первом листе... да, получается, произвольное количество диапазонов, шапками которых являются "категории", а внутри которых идут "значения". Значений в каждой категории может быть от 0.

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

Сортировка желательна по категориям. Но не критична.
Да, список уникальных пар. Его образец приведён на листе 2.
В каждой ячейке первых двух столбцов формула, которая выдаёт своё значение.
 
Ответить
Сообщение
Страница это лист.

Да, всё правильно.

Есть список "категорий" на отдельном листе

Да, это третий лист.

На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".

На первом листе... да, получается, произвольное количество диапазонов, шапками которых являются "категории", а внутри которых идут "значения". Значений в каждой категории может быть от 0.

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

Сортировка желательна по категориям. Но не критична.
Да, список уникальных пар. Его образец приведён на листе 2.
В каждой ячейке первых двух столбцов формула, которая выдаёт своё значение.

Автор - Strateg_ru
Дата добавления - 11.08.2017 в 00:48
_Boroda_ Дата: Пятница, 11.08.2017, 14:33 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Сортировка желательна по категориям. Но не критична.

Если без сортировки, то не сильно сложно:
Чтобы данные можно было добавлять, делаем динамические диапазоны (посмотреть можно, нажав Контрл F3)
для списка "спис"
Код
=ИНДЕКС(Список!$A:$A;2):ИНДЕКС(Список!$A:$A;ПОИСКПОЗ("яяя";Список!$A:$A))

и категорий (на одну пустую ячейку больше) "кат0"
Код
=ИНДЕКС(Категории!$A:$A;2):ИНДЕКС(Категории!$A:$A;СЧЁТЗ(Категории!$A:$A)+1)

Создаем виртуальный массив, в котором заменяем все значения соответствующими категориями "кат"
Код
=ПРОСМОТР(СТРОКА(спис);СТРОКА(спис)/ЕЧИСЛО(ПОИСКПОЗ(спис;кат0;));спис)

пишем формулу для категорий
Код
=ЕСЛИОШИБКА(ИНДЕКС(кат;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;СТРОКА(спис)-1));СТРОКА(A1)));"")
и для значений
Код
=ЕСЛИОШИБКА(ИНДЕКС(спис;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;СТРОКА(спис)-1));СТРОКА(A1)));"")


А вот если с сортировкой, то тут сложнее
создаем виртуальный массив "наиб", в котором на место каждой категории в паре с непустым значением пишется число, показывающее, сколько пар категория-значение больше этой пары
Код
=МУМНОЖ(--(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;кат&спис))<ТРАНСП(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;кат&спис))));СТРОКА(спис)^0)

пишем формулу для категорий
Код
=ЕСЛИОШИБКА(ИНДЕКС(кат;ПОИСКПОЗ(НАИБОЛЬШИЙ(--ТЕКСТ(наиб;"0;;-1");СТРОКА(H1));наиб;));"")
и для значений
Код
=ЕСЛИОШИБКА(ИНДЕКС(спис;ПОИСКПОЗ(НАИБОЛЬШИЙ(--ТЕКСТ(наиб;"0;;-1");СТРОКА(I1));наиб;));"")


Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня
К сообщению приложен файл: 654646464_1.xlsx (13.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Сортировка желательна по категориям. Но не критична.

Если без сортировки, то не сильно сложно:
Чтобы данные можно было добавлять, делаем динамические диапазоны (посмотреть можно, нажав Контрл F3)
для списка "спис"
Код
=ИНДЕКС(Список!$A:$A;2):ИНДЕКС(Список!$A:$A;ПОИСКПОЗ("яяя";Список!$A:$A))

и категорий (на одну пустую ячейку больше) "кат0"
Код
=ИНДЕКС(Категории!$A:$A;2):ИНДЕКС(Категории!$A:$A;СЧЁТЗ(Категории!$A:$A)+1)

Создаем виртуальный массив, в котором заменяем все значения соответствующими категориями "кат"
Код
=ПРОСМОТР(СТРОКА(спис);СТРОКА(спис)/ЕЧИСЛО(ПОИСКПОЗ(спис;кат0;));спис)

пишем формулу для категорий
Код
=ЕСЛИОШИБКА(ИНДЕКС(кат;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;СТРОКА(спис)-1));СТРОКА(A1)));"")
и для значений
Код
=ЕСЛИОШИБКА(ИНДЕКС(спис;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;СТРОКА(спис)-1));СТРОКА(A1)));"")


А вот если с сортировкой, то тут сложнее
создаем виртуальный массив "наиб", в котором на место каждой категории в паре с непустым значением пишется число, показывающее, сколько пар категория-значение больше этой пары
Код
=МУМНОЖ(--(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;кат&спис))<ТРАНСП(ЕСЛИ(ЕНД(ПОИСКПОЗ(спис&9;кат0&9;));ЕСЛИ(ПОИСКПОЗ(кат&спис;кат&спис;)=СТРОКА(спис)-1;кат&спис))));СТРОКА(спис)^0)

пишем формулу для категорий
Код
=ЕСЛИОШИБКА(ИНДЕКС(кат;ПОИСКПОЗ(НАИБОЛЬШИЙ(--ТЕКСТ(наиб;"0;;-1");СТРОКА(H1));наиб;));"")
и для значений
Код
=ЕСЛИОШИБКА(ИНДЕКС(спис;ПОИСКПОЗ(НАИБОЛЬШИЙ(--ТЕКСТ(наиб;"0;;-1");СТРОКА(I1));наиб;));"")


Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня

Автор - _Boroda_
Дата добавления - 11.08.2017 в 14:33
Strateg_ru Дата: Пятница, 11.08.2017, 22:10 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Пытаюсь "разобрать" решение...
Усложняется всё тем, что в прикреплённом примере (как есть) формулы не меняют значения. И не растягиваются.
(массив включён - ctrl+shift+enter)

PS. Что означает "яяя" в формуле:
=ИНДЕКС(Список!$A:$A;2):ИНДЕКС(Список!$A:$A;ПОИСКПОЗ("яяя";Список!$A:$A))
 
Ответить
СообщениеПытаюсь "разобрать" решение...
Усложняется всё тем, что в прикреплённом примере (как есть) формулы не меняют значения. И не растягиваются.
(массив включён - ctrl+shift+enter)

PS. Что означает "яяя" в формуле:
=ИНДЕКС(Список!$A:$A;2):ИНДЕКС(Список!$A:$A;ПОИСКПОЗ("яяя";Список!$A:$A))

Автор - Strateg_ru
Дата добавления - 11.08.2017 в 22:10
_Boroda_ Дата: Пятница, 11.08.2017, 22:22 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
массив включён - ctrl+shift+enter

Предположу, что Вы "включили" массив, предварительно выделив все ячейки. Нужно встать в одну ячейку, провалиться в нее (или тпнувшись в строку формул, или нажав F2, или даблкликом), нажать Контрл Шифт Ентер, а потом уже эту ячейку тянуть вниз. Если что-то не получается - покажите в файле, словами все равно непонятно
в прикреплённом примере (как есть) формулы не меняют значения
Это Вы о чем?
яяя - это слово, больше которого на практике не бывает. d<f<x<z<б<ж<ы<я. Слово "яя" есть в природе - так речка называется. А "яяя" - уже нет


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
массив включён - ctrl+shift+enter

Предположу, что Вы "включили" массив, предварительно выделив все ячейки. Нужно встать в одну ячейку, провалиться в нее (или тпнувшись в строку формул, или нажав F2, или даблкликом), нажать Контрл Шифт Ентер, а потом уже эту ячейку тянуть вниз. Если что-то не получается - покажите в файле, словами все равно непонятно
в прикреплённом примере (как есть) формулы не меняют значения
Это Вы о чем?
яяя - это слово, больше которого на практике не бывает. d<f<x<z<б<ж<ы<я. Слово "яя" есть в природе - так речка называется. А "яяя" - уже нет

Автор - _Boroda_
Дата добавления - 11.08.2017 в 22:22
Strateg_ru Дата: Суббота, 12.08.2017, 08:48 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Разобрался. Оказалось, что нужно обновляться (F9). Только после этого формулы пересчитывают значения. Я по привычке ожидал как в обычных формулах - все изменения видны сразу... Видимо, это особенность формул массива (я с ними знаком очень поверхностно).

Это Вы о чем?
яяя - это слово, больше которого на практике не бывает

Да, но зачем оно в формуле? Подразумевается, что если оно появится в "Списке", то выдаст ошибку? То есть нужно, чтобы на этом месте в формуле стояло нечто, чего не встретится на самом деле?
Или нужно, чтобы это было "всегда самое последнее значение в списке возможных значений"?


Сообщение отредактировал Strateg_ru - Суббота, 12.08.2017, 08:57
 
Ответить
СообщениеРазобрался. Оказалось, что нужно обновляться (F9). Только после этого формулы пересчитывают значения. Я по привычке ожидал как в обычных формулах - все изменения видны сразу... Видимо, это особенность формул массива (я с ними знаком очень поверхностно).

Это Вы о чем?
яяя - это слово, больше которого на практике не бывает

Да, но зачем оно в формуле? Подразумевается, что если оно появится в "Списке", то выдаст ошибку? То есть нужно, чтобы на этом месте в формуле стояло нечто, чего не встретится на самом деле?
Или нужно, чтобы это было "всегда самое последнее значение в списке возможных значений"?

Автор - Strateg_ru
Дата добавления - 12.08.2017 в 08:48
китин Дата: Суббота, 12.08.2017, 09:12 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Оказалось, что нужно обновляться (F9)

а у вас автопересчет формул включен?


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Оказалось, что нужно обновляться (F9)

а у вас автопересчет формул включен?

Автор - китин
Дата добавления - 12.08.2017 в 09:12
Strateg_ru Дата: Суббота, 12.08.2017, 09:50 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
а у вас автопересчет формул включен?

Нашёл, включил. Спасибо))
 
Ответить
Сообщение
а у вас автопересчет формул включен?

Нашёл, включил. Спасибо))

Автор - Strateg_ru
Дата добавления - 12.08.2017 в 09:50
Strateg_ru Дата: Суббота, 12.08.2017, 13:49 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня

Переставил в основной документ.
Всё перепроверил с наполнением разных типов данных - всё отлично! Переносится и работает. Огромное Вам спасибо!
PS. Правда КАК именно работает, так и не понял... даже примерно)

Вопрос решён. Тему можно закрывать.
 
Ответить
Сообщение
Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня

Переставил в основной документ.
Всё перепроверил с наполнением разных типов данных - всё отлично! Переносится и работает. Огромное Вам спасибо!
PS. Правда КАК именно работает, так и не понял... даже примерно)

Вопрос решён. Тему можно закрывать.

Автор - Strateg_ru
Дата добавления - 12.08.2017 в 13:49
Udik Дата: Суббота, 12.08.2017, 14:15 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
А я опять на PQ сделал :) Сначала только допстолбец для категорий в исходных данных добавил.
К сообщению приложен файл: 6171717.xlsx (21.1 Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеА я опять на PQ сделал :) Сначала только допстолбец для категорий в исходных данных добавил.

Автор - Udik
Дата добавления - 12.08.2017 в 14:15
Strateg_ru Дата: Суббота, 12.08.2017, 22:53 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
на PQ сделал

PQ - почти незнакомая мне технология. Но тоже спасибо!
 
Ответить
Сообщение
на PQ сделал

PQ - почти незнакомая мне технология. Но тоже спасибо!

Автор - Strateg_ru
Дата добавления - 12.08.2017 в 22:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Из списка в столбцы (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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