Здравствуйте, уважаемые эксперты! Нужна помощь в составлении формулы.
Есть страница с единственным столбцом, в котором смешаны Категории, Значения и пустые ячейки. Столбец более 1000 строк. Категории и значения могут повторяться. На второй странице нужно разместить значения этого списка в два столбца соответственно – Категория и Значения (игнорируя пустые строки). На третьей странице – список категорий (в алфавитном порядке, категории не повторяются)
В исходных данных - одно и тоже значение может быть в разных категориях, причём, несколько раз. В результатах - каждому значению присваивается категория, в списке которой она стоит. Если одно и тоже значение было в разных категориях – оно должно встречаться столько раз, в скольки категориях находилось. Пара: Значение-Категория, должно находиться в результатах не более одного раза (даже если попадалось в исходном списке несколько раз в одной и той же категории).
Что из списка является категорией – сверяется по столбцу третьей страницы. Всё, что идёт в исходных данных ниже категории – это её значение. Пока не попадётся следующая категория - тогда эти значения будут находиться уже в ней.
Нужна простая или массивная формула. Без макросов. Спасибо!
Здравствуйте, уважаемые эксперты! Нужна помощь в составлении формулы.
Есть страница с единственным столбцом, в котором смешаны Категории, Значения и пустые ячейки. Столбец более 1000 строк. Категории и значения могут повторяться. На второй странице нужно разместить значения этого списка в два столбца соответственно – Категория и Значения (игнорируя пустые строки). На третьей странице – список категорий (в алфавитном порядке, категории не повторяются)
В исходных данных - одно и тоже значение может быть в разных категориях, причём, несколько раз. В результатах - каждому значению присваивается категория, в списке которой она стоит. Если одно и тоже значение было в разных категориях – оно должно встречаться столько раз, в скольки категориях находилось. Пара: Значение-Категория, должно находиться в результатах не более одного раза (даже если попадалось в исходном списке несколько раз в одной и той же категории).
Что из списка является категорией – сверяется по столбцу третьей страницы. Всё, что идёт в исходных данных ниже категории – это её значение. Пока не попадётся следующая категория - тогда эти значения будут находиться уже в ней.
Нужна простая или массивная формула. Без макросов. Спасибо!Strateg_ru
На третьем листе категории уже даны или их нужно вывести?
Если нужно вывести, то нужен какой-то признак, по которому категории отличаются от значений (и этот признак не должен быть цветом) Это у Вас выгрузка из 1С? Крайне желательно, чтобы Вы выложили файл реальный (названия можно заменить, цифры тоже, но только их, все форматы, всё месторасположение должно сохраниться)
На третьем листе категории уже даны или их нужно вывести?
Если нужно вывести, то нужен какой-то признак, по которому категории отличаются от значений (и этот признак не должен быть цветом) Это у Вас выгрузка из 1С? Крайне желательно, чтобы Вы выложили файл реальный (названия можно заменить, цифры тоже, но только их, все форматы, всё месторасположение должно сохраниться)_Boroda_
Правильно ли я Вас понял: Страница это лист. Есть список "категорий" на отдельном листе, На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория". Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами. ?
Правильно ли я Вас понял: Страница это лист. Есть список "категорий" на отдельном листе, На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория". Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами. ?InExSu
Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac
Сообщение отредактировал InExSu - Четверг, 10.08.2017, 23:29
Нет, но система такая же кривая) Это эмулируемый фрагмент из более сложного файла. Но форматирование по столбцам и листам соблюдено: 1. Общий список из заполненных категорий, одна под другой 2. Категория и значение 3. Список категорий, задаваемый пользователем вручную
Нет, но система такая же кривая) Это эмулируемый фрагмент из более сложного файла. Но форматирование по столбцам и листам соблюдено: 1. Общий список из заполненных категорий, одна под другой 2. Категория и значение 3. Список категорий, задаваемый пользователем вручнуюStrateg_ru
На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".
На первом листе... да, получается, произвольное количество диапазонов, шапками которых являются "категории", а внутри которых идут "значения". Значений в каждой категории может быть от 0.
Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами.
Сортировка желательна по категориям. Но не критична. Да, список уникальных пар. Его образец приведён на листе 2. В каждой ячейке первых двух столбцов формула, которая выдаёт своё значение.
На отдельном листе будет произвольное количество диапазонов, в этих диапазонах будут "категории" и "значения". Отдельно списка значений нет. Признак "значения": не "категория".
На первом листе... да, получается, произвольное количество диапазонов, шапками которых являются "категории", а внутри которых идут "значения". Значений в каждой категории может быть от 0.
Нужно создать несортированный список уникальных пар ячеек "категория" + "значение" на отдельном листе именно формулами.
Сортировка желательна по категориям. Но не критична. Да, список уникальных пар. Его образец приведён на листе 2. В каждой ячейке первых двух столбцов формула, которая выдаёт своё значение.Strateg_ru
Сортировка желательна по категориям. Но не критична.
Если без сортировки, то не сильно сложно: Чтобы данные можно было добавлять, делаем динамические диапазоны (посмотреть можно, нажав Контрл F3) для списка "спис"
А вот если с сортировкой, то тут сложнее создаем виртуальный массив "наиб", в котором на место каждой категории в паре с непустым значением пишется число, показывающее, сколько пар категория-значение больше этой пары
Сортировка желательна по категориям. Но не критична.
Если без сортировки, то не сильно сложно: Чтобы данные можно было добавлять, делаем динамические диапазоны (посмотреть можно, нажав Контрл F3) для списка "спис"
А вот если с сортировкой, то тут сложнее создаем виртуальный массив "наиб", в котором на место каждой категории в паре с непустым значением пишется число, показывающее, сколько пар категория-значение больше этой пары
Пытаюсь "разобрать" решение... Усложняется всё тем, что в прикреплённом примере (как есть) формулы не меняют значения. И не растягиваются. (массив включён - ctrl+shift+enter)
Пытаюсь "разобрать" решение... Усложняется всё тем, что в прикреплённом примере (как есть) формулы не меняют значения. И не растягиваются. (массив включён - ctrl+shift+enter)
Предположу, что Вы "включили" массив, предварительно выделив все ячейки. Нужно встать в одну ячейку, провалиться в нее (или тпнувшись в строку формул, или нажав F2, или даблкликом), нажать Контрл Шифт Ентер, а потом уже эту ячейку тянуть вниз. Если что-то не получается - покажите в файле, словами все равно непонятно
в прикреплённом примере (как есть) формулы не меняют значения
Это Вы о чем? яяя - это слово, больше которого на практике не бывает. d<f<x<z<б<ж<ы<я. Слово "яя" есть в природе - так речка называется. А "яяя" - уже нет
Предположу, что Вы "включили" массив, предварительно выделив все ячейки. Нужно встать в одну ячейку, провалиться в нее (или тпнувшись в строку формул, или нажав F2, или даблкликом), нажать Контрл Шифт Ентер, а потом уже эту ячейку тянуть вниз. Если что-то не получается - покажите в файле, словами все равно непонятно
в прикреплённом примере (как есть) формулы не меняют значения
Это Вы о чем? яяя - это слово, больше которого на практике не бывает. d<f<x<z<б<ж<ы<я. Слово "яя" есть в природе - так речка называется. А "яяя" - уже нет_Boroda_
Разобрался. Оказалось, что нужно обновляться (F9). Только после этого формулы пересчитывают значения. Я по привычке ожидал как в обычных формулах - все изменения видны сразу... Видимо, это особенность формул массива (я с ними знаком очень поверхностно).
Это Вы о чем? яяя - это слово, больше которого на практике не бывает
Да, но зачем оно в формуле? Подразумевается, что если оно появится в "Списке", то выдаст ошибку? То есть нужно, чтобы на этом месте в формуле стояло нечто, чего не встретится на самом деле? Или нужно, чтобы это было "всегда самое последнее значение в списке возможных значений"?
Разобрался. Оказалось, что нужно обновляться (F9). Только после этого формулы пересчитывают значения. Я по привычке ожидал как в обычных формулах - все изменения видны сразу... Видимо, это особенность формул массива (я с ними знаком очень поверхностно).
Это Вы о чем? яяя - это слово, больше которого на практике не бывает
Да, но зачем оно в формуле? Подразумевается, что если оно появится в "Списке", то выдаст ошибку? То есть нужно, чтобы на этом месте в формуле стояло нечто, чего не встретится на самом деле? Или нужно, чтобы это было "всегда самое последнее значение в списке возможных значений"?Strateg_ru
Сообщение отредактировал Strateg_ru - Суббота, 12.08.2017, 08:57
Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня
Переставил в основной документ. Всё перепроверил с наполнением разных типов данных - всё отлично! Переносится и работает. Огромное Вам спасибо! PS. Правда КАК именно работает, так и не понял... даже примерно)
Наверняка я пошел не самым оптимальным путем, но что-то не очень хорошо соображаю сегодня
Переставил в основной документ. Всё перепроверил с наполнением разных типов данных - всё отлично! Переносится и работает. Огромное Вам спасибо! PS. Правда КАК именно работает, так и не понял... даже примерно)