Уважаемые знатоки, помогите пожалуйста, отыскать заданное число в нескольких столбцах, для найденных определить пару из соседних колонок и выстроить эти значения по убыванию их встречаемости.
Уважаемые знатоки, помогите пожалуйста, отыскать заданное число в нескольких столбцах, для найденных определить пару из соседних колонок и выстроить эти значения по убыванию их встречаемости.Kaktus8
Добрый день! Массивные формулы с сортировкой по возрастанию, а не по частоте встречаемости. Для трёх пар столбцов с именами (П1 - П11) (П2 - П22) (П3 - П33). Можно добавить ещё пару (П4 - П44), тогда придётся заменить 399 на 499 и
*С сортировкой по частоте значительно увеличится. **Столбцы можно тусовать и вставлять как угодно. Строки вставлять и удалять нельзя.
Добрый день! Массивные формулы с сортировкой по возрастанию, а не по частоте встречаемости. Для трёх пар столбцов с именами (П1 - П11) (П2 - П22) (П3 - П33). Можно добавить ещё пару (П4 - П44), тогда придётся заменить 399 на 499 и
*С сортировкой по частоте значительно увеличится. Строки вставлять и удалять нельзя.
Сортировка по частоте и возможность добавления новых строк в столбцы "П" (с возможностью пополнения до 10000 строк), это два самых важных критерия для меня. Столбцы "Парные значения" и "Количество" будут находиться в неизменном положении и редактироваться не будут.
Да... конечно, очень сложные формулы получаются. Не думал, что так получится. Похоже, придётся урезать требования ТЗ.
Тогда буду переделывать часть таблицы в вид имеющий блочную структуру. Придётся повозиться, но так даже лучше будет и формулы, я так понимаю, можно будет значительно упростить.
Упрощенное ТЗ тогда становится таким: 1. Блочная структура группировки столбцов (четыре блока по семь столбцов, серые всегда седьмые, желтые - первые, но прошу объяснить, как переделать формулы для вариантов, в которых желтые можно будет сделать вторыми, третьими и т.п., вплоть до шестых). 2. "Парные значения по убыванию частоты" и "Количество повторов" можно ограничить первыми 20 значениями и расположить эти группы друг под другом. Их местоположение меняться не будет. 3. Должна быть возможность пополнения столбцов "П" до 10000 строк.
Помогите, пожалуйста, сделать по такому варианту! Файл с новым вариантом расположения столбцов (Лист 2).
*С сортировкой по частоте значительно увеличится. Строки вставлять и удалять нельзя.
Сортировка по частоте и возможность добавления новых строк в столбцы "П" (с возможностью пополнения до 10000 строк), это два самых важных критерия для меня. Столбцы "Парные значения" и "Количество" будут находиться в неизменном положении и редактироваться не будут.
Да... конечно, очень сложные формулы получаются. Не думал, что так получится. Похоже, придётся урезать требования ТЗ.
Тогда буду переделывать часть таблицы в вид имеющий блочную структуру. Придётся повозиться, но так даже лучше будет и формулы, я так понимаю, можно будет значительно упростить.
Упрощенное ТЗ тогда становится таким: 1. Блочная структура группировки столбцов (четыре блока по семь столбцов, серые всегда седьмые, желтые - первые, но прошу объяснить, как переделать формулы для вариантов, в которых желтые можно будет сделать вторыми, третьими и т.п., вплоть до шестых). 2. "Парные значения по убыванию частоты" и "Количество повторов" можно ограничить первыми 20 значениями и расположить эти группы друг под другом. Их местоположение меняться не будет. 3. Должна быть возможность пополнения столбцов "П" до 10000 строк.
Помогите, пожалуйста, сделать по такому варианту! Файл с новым вариантом расположения столбцов (Лист 2).Kaktus8
В новом файле тоже должна работать. *Не предусмотрен образец (3). Где он находится? И всё-таки число и количество рядом надо поставить, а не друг под другом. Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4" Величины парных чисел превосходят 90? В примере максимальное значение 24. **Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.
Пока оставил прежнюю формулу. Причесал и расширил до 10000 строк:
В новом файле тоже должна работать. *Не предусмотрен образец (3). Где он находится? И всё-таки число и количество рядом надо поставить, а не друг под другом. Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4" Величины парных чисел превосходят 90? В примере максимальное значение 24. **Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.Светлый
И всё-таки число и количество рядом надо поставить, а не друг под другом. Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"
Да, я согласен с Вами. Только,у меня рядом воткнуть не получится, - нет места. Да их ещё и несколько штук придётся мне делать.
Попробовать на рабочем файле пока не могу, - перелопачиваю таблицу под блоки по 7 столбцов. Ещё часа 3-4, я думаю, провожусь. Но, наверно, в результате оно и к лучшему будет.
И всё-таки число и количество рядом надо поставить, а не друг под другом. Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"
Да, я согласен с Вами. Только,у меня рядом воткнуть не получится, - нет места. Да их ещё и несколько штук придётся мне делать.
Попробовать на рабочем файле пока не могу, - перелопачиваю таблицу под блоки по 7 столбцов. Ещё часа 3-4, я думаю, провожусь. Но, наверно, в результате оно и к лучшему будет.Kaktus8
Но обнаружилась одна ошибка: если искомое значение (АН4) =0, то не происходит сортировка по убыванию (точнее, происходит, но она начинается со второй позиции и ниже). Соответвтенно и в графе колич. повторов это тоже отражается. Если можно, то поправить бы? В остальном, всё работает идеально!
Также, если ввести парное значение (П11, П12, П13, П14), а в первый столбец (П1, П2, П3, П4) ничего не вводить, то такое значение считается как "0". Но этот момент не критичен, так как таких данных не будет. Просто заметил.
Как я понял, для случаев, когда позиции левого столбца будут не 1, а 2,3,4,5,6, нужно изменить только ячейки их заголовков, а массивы констант (которые в фигурных скобках) трогать не нужно?
А чем отличаются варианты формулы для количества повторов?
Возникли некоторые трудности в процессе переделки таблицы к регулярному виду, поэтому прошу прощения за задержку с ответом.
Но обнаружилась одна ошибка: если искомое значение (АН4) =0, то не происходит сортировка по убыванию (точнее, происходит, но она начинается со второй позиции и ниже). Соответвтенно и в графе колич. повторов это тоже отражается. Если можно, то поправить бы? В остальном, всё работает идеально!
Также, если ввести парное значение (П11, П12, П13, П14), а в первый столбец (П1, П2, П3, П4) ничего не вводить, то такое значение считается как "0". Но этот момент не критичен, так как таких данных не будет. Просто заметил.
Как я понял, для случаев, когда позиции левого столбца будут не 1, а 2,3,4,5,6, нужно изменить только ячейки их заголовков, а массивы констант (которые в фигурных скобках) трогать не нужно?
А чем отличаются варианты формулы для количества повторов?Kaktus8
Сообщение отредактировал Kaktus8 - Среда, 08.05.2019, 17:57
можно и так. А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке. Вот формула, которая может брать за образец 0, но не считает нули в правом столбце:
можно и так. А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке. Вот формула, которая может брать за образец 0, но не считает нули в правом столбце:
А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке
А для последней формулы это тоже справедливо? Что-то у меня не получилось. Делал так: 3+{0;7;14;21} для всех блоков с {0;7;14;21}. Вполне возможно, что напутал где-нибудь, попозже попробую ещё.
А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке
А для последней формулы это тоже справедливо? Что-то у меня не получилось. Делал так: 3+{0;7;14;21} для всех блоков с {0;7;14;21}. Вполне возможно, что напутал где-нибудь, попозже попробую ещё.Kaktus8
Я извиняюсь, но есть ещё одна просьба. У меня очень большой рабочий файл, и хотя формулы которые Вы написали, работают прекрасно, но применительно к моему файлу, очень сильно загружают процессор (все ядра) при их массовом добавлении. Не могли бы Вы или кто-то ещё, как-то их упростить? А то Excel стал затыкаться на несколько минут при каждом действии. Если убираю эти формулы или сокращаю их количество, то всё становится как раньше. Почитал разные статьи про оптимизацию Экселя и всё, что возможно сделал, но проблема сохраняется. Если есть возможность, то можно их как-то оптимизировать? 64 битная версия 2016 экселя. Памяти в системе -16 Гб.
Я извиняюсь, но есть ещё одна просьба. У меня очень большой рабочий файл, и хотя формулы которые Вы написали, работают прекрасно, но применительно к моему файлу, очень сильно загружают процессор (все ядра) при их массовом добавлении. Не могли бы Вы или кто-то ещё, как-то их упростить? А то Excel стал затыкаться на несколько минут при каждом действии. Если убираю эти формулы или сокращаю их количество, то всё становится как раньше. Почитал разные статьи про оптимизацию Экселя и всё, что возможно сделал, но проблема сохраняется. Если есть возможность, то можно их как-то оптимизировать? 64 битная версия 2016 экселя. Памяти в системе -16 Гб.Kaktus8
Сообщение отредактировал Kaktus8 - Суббота, 11.05.2019, 19:10
Прошу прощения, что не ответил сразу: обновлял систему и офис (на всякий случай). Правда, не помогло. Думал, может оптимизировали/улучшили что-нибудь и это скажется на быстродействии.
Сейчас, пока, терпимо, но придётся разбивать таблицу на части, чтобы по мере её заполнения опять не столкнуться с подобной ситуацией.
P.S.: Хм, интересно, может кто знает, а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить? Насколько это сложно? Всё заново придётся делать или мастер конвертации справится? С Акцессом дела практически не имел, но, говорят, там с быстродействием на больших объёмах всё хорошо. Это я на всякий случай спрашиваю. Насколько подобное решение может быть оправдано при большом количестве вычислений?
Прошу прощения, что не ответил сразу: обновлял систему и офис (на всякий случай). Правда, не помогло. Думал, может оптимизировали/улучшили что-нибудь и это скажется на быстродействии.
Сейчас, пока, терпимо, но придётся разбивать таблицу на части, чтобы по мере её заполнения опять не столкнуться с подобной ситуацией.
P.S.: Хм, интересно, может кто знает, а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить? Насколько это сложно? Всё заново придётся делать или мастер конвертации справится? С Акцессом дела практически не имел, но, говорят, там с быстродействием на больших объёмах всё хорошо. Это я на всякий случай спрашиваю. Насколько подобное решение может быть оправдано при большом количестве вычислений?Kaktus8
а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Там такие громоздкие формулы не нужны. Это простой запрос с сортированным выводом. Доли секунды будет считать. В Excel другой принцип, формулы выполняют много лишних вычислений. С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.
а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Там такие громоздкие формулы не нужны. Это простой запрос с сортированным выводом. Доли секунды будет считать. В Excel другой принцип, формулы выполняют много лишних вычислений. С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.Светлый
Т.е. в Акцессе организовать расчёты с логическими операциями с созданием на их основе дополнительных полей в базе не так удобно? Получается, что не стоит заморачиваться и проще разбить таблицу на несколько частей с подсчётом по частям? Похоже, так и придётся делать.
С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.
Спасибо, учту это при разбивке таблицы.
Т.е. в Акцессе организовать расчёты с логическими операциями с созданием на их основе дополнительных полей в базе не так удобно? Получается, что не стоит заморачиваться и проще разбить таблицу на несколько частей с подсчётом по частям? Похоже, так и придётся делать.
Это не так, если с данными будет такой бардак. ибо таких вольностей с полями Acces не допустит. но запрос и В Excel отработает на ура. Сейчас на 10000 строк рассчитано. [vba]
Код
select П as "Парное значение" , Count(П) as "Количество повторов" from ( SELECT П1 as ПА, П11 as П FROM `Лист1$A1:T10000` Union all SELECT П2 as ПА, П22 as П FROM `Лист1$A1:T10000` Union all SELECT П3 as ПА, П33 as П FROM `Лист1$A1:T10000`) Where ПА =? Group by П
[/vba] Сортировку можно и в запрос положить, но не хотел лишнее писать :-) , да и зачем если есть внутренний инструмент Обновление или по правой кнопке или при смене значения в заветной ячейке. ? в запросе параметр который на нее ссылается. Для работы в Connection подправить путь к файлу или надо крохотный макрос для этой автоматизации.
Это не так, если с данными будет такой бардак. ибо таких вольностей с полями Acces не допустит. но запрос и В Excel отработает на ура. Сейчас на 10000 строк рассчитано. [vba]
Код
select П as "Парное значение" , Count(П) as "Количество повторов" from ( SELECT П1 as ПА, П11 as П FROM `Лист1$A1:T10000` Union all SELECT П2 as ПА, П22 as П FROM `Лист1$A1:T10000` Union all SELECT П3 as ПА, П33 as П FROM `Лист1$A1:T10000`) Where ПА =? Group by П
[/vba] Сортировку можно и в запрос положить, но не хотел лишнее писать :-) , да и зачем если есть внутренний инструмент Обновление или по правой кнопке или при смене значения в заветной ячейке. ? в запросе параметр который на нее ссылается. Для работы в Connection подправить путь к файлу или надо крохотный макрос для этой автоматизации.bmv98rus