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

Вход

Регистрация

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

 

= Мир MS Excel/Найти число и выстроить его пары по убыванию - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти число и выстроить его пары по убыванию (Формулы/Formulas)
Найти число и выстроить его пары по убыванию
Kaktus8 Дата: Вторник, 07.05.2019, 00:46 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Уважаемые знатоки,
помогите пожалуйста, отыскать заданное число в нескольких столбцах, для найденных определить пару из соседних колонок и выстроить эти значения по убыванию их встречаемости.
К сообщению приложен файл: 10_1.xlsx (11.2 Kb)
 
Ответить
СообщениеУважаемые знатоки,
помогите пожалуйста, отыскать заданное число в нескольких столбцах, для найденных определить пару из соседних колонок и выстроить эти значения по убыванию их встречаемости.

Автор - Kaktus8
Дата добавления - 07.05.2019 в 00:46
Светлый Дата: Вторник, 07.05.2019, 09:59 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
Массивные формулы с сортировкой по возрастанию, а не по частоте встречаемости. Для трёх пар столбцов с именами (П1 - П11) (П2 - П22) (П3 - П33). Можно добавить ещё пару (П4 - П44), тогда придётся заменить 399 на 499 и
Код
СТРОКА($1:$3) на СТРОКА($1:$4)

В столбец X и протянуть вниз:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)));1+СУММ(Y$7:Y7));"")
В столбец Y и протянуть вниз. Просто количество:
Код
=СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)))=X8))
*С сортировкой по частоте значительно увеличится.
**Столбцы можно тусовать и вставлять как угодно. Строки вставлять и удалять нельзя.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 07.05.2019, 10:09
 
Ответить
СообщениеДобрый день!
Массивные формулы с сортировкой по возрастанию, а не по частоте встречаемости. Для трёх пар столбцов с именами (П1 - П11) (П2 - П22) (П3 - П33). Можно добавить ещё пару (П4 - П44), тогда придётся заменить 399 на 499 и
Код
СТРОКА($1:$3) на СТРОКА($1:$4)

В столбец X и протянуть вниз:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)));1+СУММ(Y$7:Y7));"")
В столбец Y и протянуть вниз. Просто количество:
Код
=СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)))=X8))
*С сортировкой по частоте значительно увеличится.
**Столбцы можно тусовать и вставлять как угодно. Строки вставлять и удалять нельзя.

Автор - Светлый
Дата добавления - 07.05.2019 в 09:59
Светлый Дата: Вторник, 07.05.2019, 10:46 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Придумал, как сделать с сортировкой по частоте, но не хочу размер формулы увеличивать в 2 раза.
В столбец X и протянуть вниз:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(X1));100)
В конце будут значения 99, 98 и т.д., которые встречаются 0 раз.
*Числа во втором столбце должны быть положительными, целыми и не больше 90.


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 07.05.2019, 10:51
 
Ответить
СообщениеПридумал, как сделать с сортировкой по частоте, но не хочу размер формулы увеличивать в 2 раза.
В столбец X и протянуть вниз:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1))=X$4;Ч(СМЕЩ(A$5;ОСТАТ(СТРОКА($100:$399);100);НАИБОЛЬШИЙ(("П"&СТРОКА($1:$3)&СТРОКА($1:$3)=A$4:V$4)*СТОЛБЕЦ(A:V);ОТБР(СТРОКА($100:$399)/100))-1)));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(X1));100)
В конце будут значения 99, 98 и т.д., которые встречаются 0 раз.
*Числа во втором столбце должны быть положительными, целыми и не больше 90.

Автор - Светлый
Дата добавления - 07.05.2019 в 10:46
sboy Дата: Вторник, 07.05.2019, 11:06 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Решение на Power Query (не совсем по ТЗ, больше для себя. Обновляется только после сохранения изменений и нажатия "Обновить")
К сообщению приложен файл: 8032173.xlsx (24.1 Kb)


Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Решение на Power Query (не совсем по ТЗ, больше для себя. Обновляется только после сохранения изменений и нажатия "Обновить")

Автор - sboy
Дата добавления - 07.05.2019 в 11:06
Kaktus8 Дата: Вторник, 07.05.2019, 13:36 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, sboy, большое спасибо за помощь!
Решение на Power Query

Спасибо, но мне с помощью формул надо, традиционным способом. На PQ сейчас просто нет возможности заморачиваться.
Но всё-равно благодарю за участие!

Цитата Светлый, 07.05.2019 в 09:59, в сообщении № 2 ()
*С сортировкой по частоте значительно увеличится.
Строки вставлять и удалять нельзя.

Сортировка по частоте и возможность добавления новых строк в столбцы "П" (с возможностью пополнения до 10000 строк),
это два самых важных критерия для меня.
Столбцы "Парные значения" и "Количество" будут находиться в неизменном положении и редактироваться не будут.

Да... конечно, очень сложные формулы получаются. Не думал, что так получится.
Похоже, придётся урезать требования ТЗ.

Тогда буду переделывать часть таблицы в вид имеющий блочную структуру.
Придётся повозиться, но так даже лучше будет и формулы, я так понимаю, можно будет значительно упростить.

Упрощенное ТЗ тогда становится таким:
1. Блочная структура группировки столбцов (четыре блока по семь столбцов, серые всегда седьмые, желтые - первые, но прошу объяснить, как переделать формулы для вариантов, в которых желтые можно будет сделать вторыми, третьими и т.п., вплоть до шестых).
2. "Парные значения по убыванию частоты" и "Количество повторов" можно ограничить первыми 20 значениями и расположить эти группы друг под другом. Их местоположение меняться не будет.
3. Должна быть возможность пополнения столбцов "П" до 10000 строк.

Помогите, пожалуйста, сделать по такому варианту!
Файл с новым вариантом расположения столбцов (Лист 2).
К сообщению приложен файл: 10_1_2.xlsx (15.8 Kb)
 
Ответить
СообщениеСветлый, sboy, большое спасибо за помощь!
Решение на Power Query

Спасибо, но мне с помощью формул надо, традиционным способом. На PQ сейчас просто нет возможности заморачиваться.
Но всё-равно благодарю за участие!

Цитата Светлый, 07.05.2019 в 09:59, в сообщении № 2 ()
*С сортировкой по частоте значительно увеличится.
Строки вставлять и удалять нельзя.

Сортировка по частоте и возможность добавления новых строк в столбцы "П" (с возможностью пополнения до 10000 строк),
это два самых важных критерия для меня.
Столбцы "Парные значения" и "Количество" будут находиться в неизменном положении и редактироваться не будут.

Да... конечно, очень сложные формулы получаются. Не думал, что так получится.
Похоже, придётся урезать требования ТЗ.

Тогда буду переделывать часть таблицы в вид имеющий блочную структуру.
Придётся повозиться, но так даже лучше будет и формулы, я так понимаю, можно будет значительно упростить.

Упрощенное ТЗ тогда становится таким:
1. Блочная структура группировки столбцов (четыре блока по семь столбцов, серые всегда седьмые, желтые - первые, но прошу объяснить, как переделать формулы для вариантов, в которых желтые можно будет сделать вторыми, третьими и т.п., вплоть до шестых).
2. "Парные значения по убыванию частоты" и "Количество повторов" можно ограничить первыми 20 значениями и расположить эти группы друг под другом. Их местоположение меняться не будет.
3. Должна быть возможность пополнения столбцов "П" до 10000 строк.

Помогите, пожалуйста, сделать по такому варианту!
Файл с новым вариантом расположения столбцов (Лист 2).

Автор - Kaktus8
Дата добавления - 07.05.2019 в 13:36
Светлый Дата: Вторник, 07.05.2019, 15:06 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Пока оставил прежнюю формулу. Причесал и расширил до 10000 строк:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&СТОЛБЕЦ(A:C);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1))=X$4;Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&ПОВТОР(СТОЛБЕЦ(A:C);2);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1)));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(X1));100)
И колич. повторов:
Код
=СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&СТОЛБЕЦ(A:C);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1))=X$4;Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&ПОВТОР(СТОЛБЕЦ(A:C);2);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1)))=X8))
В новом файле тоже должна работать.
*Не предусмотрен образец (3). Где он находится? И всё-таки число и количество рядом надо поставить, а не друг под другом.
Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"
Величины парных чисел превосходят 90? В примере максимальное значение 24.
**Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.
К сообщению приложен файл: 10_1_2-1.xlsx (17.7 Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 07.05.2019, 15:45
 
Ответить
СообщениеПока оставил прежнюю формулу. Причесал и расширил до 10000 строк:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&СТОЛБЕЦ(A:C);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1))=X$4;Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&ПОВТОР(СТОЛБЕЦ(A:C);2);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1)));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(X1));100)
И колич. повторов:
Код
=СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&СТОЛБЕЦ(A:C);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1))=X$4;Ч(СМЕЩ(A$5;ПРАВБ(СТРОКА($10000:$39999);4);НАИМЕНЬШИЙ(ПОИСКПОЗ("П"&ПОВТОР(СТОЛБЕЦ(A:C);2);$4:$4);ЛЕВБ(СТРОКА($10000:$39999)))-1)))=X8))
В новом файле тоже должна работать.
*Не предусмотрен образец (3). Где он находится? И всё-таки число и количество рядом надо поставить, а не друг под другом.
Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"
Величины парных чисел превосходят 90? В примере максимальное значение 24.
**Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.

Автор - Светлый
Дата добавления - 07.05.2019 в 15:06
Kaktus8 Дата: Вторник, 07.05.2019, 16:52 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый,
спасибо большое!

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
Не предусмотрен образец (3)

Упс... Поторопился и забыл, извиняюсь.
Да, Вы её там где надо поставили. Прямо над столбцом с парами.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
И всё-таки число и количество рядом надо поставить, а не друг под другом.
Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"

Да, я согласен с Вами. Только,у меня рядом воткнуть не получится, - нет места.
Да их ещё и несколько штук придётся мне делать.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
Величины парных чисел превосходят 90? В примере максимальное значение 24.

Нет.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
**Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.

Спасибо, большое!
Да-а, формула, конечно, грандиозная получилась!

Попробовать на рабочем файле пока не могу, - перелопачиваю таблицу под блоки по 7 столбцов.
Ещё часа 3-4, я думаю, провожусь.
Но, наверно, в результате оно и к лучшему будет.
 
Ответить
СообщениеСветлый,
спасибо большое!

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
Не предусмотрен образец (3)

Упс... Поторопился и забыл, извиняюсь.
Да, Вы её там где надо поставили. Прямо над столбцом с парами.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
И всё-таки число и количество рядом надо поставить, а не друг под другом.
Даже при регулярной структуре данных формула незначительно уменьшится, так что лучше оставить так. И не нужно будет переделывать под другой столбец, достаточно название столбца поменять в четвёртой строке от "П1" до "П4"

Да, я согласен с Вами. Только,у меня рядом воткнуть не получится, - нет места.
Да их ещё и несколько штук придётся мне делать.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
Величины парных чисел превосходят 90? В примере максимальное значение 24.

Нет.

Цитата Светлый, 07.05.2019 в 15:06, в сообщении № 6 ()
**Добавляю файл. Переделал под 4 пары столбцов и убрал 99, 98... Формула в два раза больше.

Спасибо, большое!
Да-а, формула, конечно, грандиозная получилась!

Попробовать на рабочем файле пока не могу, - перелопачиваю таблицу под блоки по 7 столбцов.
Ещё часа 3-4, я думаю, провожусь.
Но, наверно, в результате оно и к лучшему будет.

Автор - Kaktus8
Дата добавления - 07.05.2019 в 16:52
Светлый Дата: Вторник, 07.05.2019, 23:12 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Формулы для регулярной структуры:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1));100);"")
Колич. повторов:
Код
=ЕСЛИ(AH11="";"";СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})))=AH11)))
*Или
Код
=ЕСЛИ(AH11="";"";СУММ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4)*(Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}))=AH11)))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 07.05.2019, 23:36
 
Ответить
СообщениеФормулы для регулярной структуры:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1));100);"")
Колич. повторов:
Код
=ЕСЛИ(AH11="";"";СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})))=AH11)))
*Или
Код
=ЕСЛИ(AH11="";"";СУММ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4)*(Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}))=AH11)))

Автор - Светлый
Дата добавления - 07.05.2019 в 23:12
Kaktus8 Дата: Среда, 08.05.2019, 17:16 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Возникли некоторые трудности в процессе переделки таблицы к регулярному виду, поэтому прошу прощения за задержку с ответом.

Цитата Светлый, 07.05.2019 в 23:12, в сообщении № 8 ()
Формулы для регулярной структуры:

Большущее спасибо!
Работает изумительно!

Но обнаружилась одна ошибка:
если искомое значение (АН4) =0, то не происходит сортировка по убыванию (точнее, происходит, но она начинается со второй позиции и ниже). Соответвтенно и в графе колич. повторов это тоже отражается.
Если можно, то поправить бы?
В остальном, всё работает идеально!

Также, если ввести парное значение (П11, П12, П13, П14), а в первый столбец (П1, П2, П3, П4) ничего не вводить, то такое значение считается как "0". Но этот момент не критичен, так как таких данных не будет. Просто заметил.

Как я понял, для случаев, когда позиции левого столбца будут не 1, а 2,3,4,5,6, нужно изменить только ячейки их заголовков, а массивы констант (которые в фигурных скобках) трогать не нужно?

А чем отличаются варианты формулы для количества повторов?


Сообщение отредактировал Kaktus8 - Среда, 08.05.2019, 17:57
 
Ответить
СообщениеВозникли некоторые трудности в процессе переделки таблицы к регулярному виду, поэтому прошу прощения за задержку с ответом.

Цитата Светлый, 07.05.2019 в 23:12, в сообщении № 8 ()
Формулы для регулярной структуры:

Большущее спасибо!
Работает изумительно!

Но обнаружилась одна ошибка:
если искомое значение (АН4) =0, то не происходит сортировка по убыванию (точнее, происходит, но она начинается со второй позиции и ниже). Соответвтенно и в графе колич. повторов это тоже отражается.
Если можно, то поправить бы?
В остальном, всё работает идеально!

Также, если ввести парное значение (П11, П12, П13, П14), а в первый столбец (П1, П2, П3, П4) ничего не вводить, то такое значение считается как "0". Но этот момент не критичен, так как таких данных не будет. Просто заметил.

Как я понял, для случаев, когда позиции левого столбца будут не 1, а 2,3,4,5,6, нужно изменить только ячейки их заголовков, а массивы констант (которые в фигурных скобках) трогать не нужно?

А чем отличаются варианты формулы для количества повторов?

Автор - Kaktus8
Дата добавления - 08.05.2019 в 17:16
Светлый Дата: Среда, 08.05.2019, 19:51 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
А чем отличаются варианты формулы для количества повторов?
функцией ЕСЛИ
нужно изменить только ячейки их заголовков
можно и так. А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке.
Вот формула, которая может брать за образец 0, но не считает нули в правом столбце:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100);"")
если ввести парное значение (П11, П12, П13, П14)
столбец парного значения тоже можно сдвигать.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
А чем отличаются варианты формулы для количества повторов?
функцией ЕСЛИ
нужно изменить только ячейки их заголовков
можно и так. А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке.
Вот формула, которая может брать за образец 0, но не считает нули в правом столбце:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100);"")
если ввести парное значение (П11, П12, П13, П14)
столбец парного значения тоже можно сдвигать.

Автор - Светлый
Дата добавления - 08.05.2019 в 19:51
Kaktus8 Дата: Среда, 08.05.2019, 22:55 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
Вот формула, которая может брать за образец 0

Большое спасибо!
Всё прекрасно работает!

Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
но не считает нули в правом столбце

В каждой правой колонке по одному нулю и больше быть не может, поэтому не критично.
А вот для левых это важно.
Спасибо, ещё раз!

Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке

А для последней формулы это тоже справедливо?
Что-то у меня не получилось. Делал так: 3+{0;7;14;21} для всех блоков с {0;7;14;21}.
Вполне возможно, что напутал где-нибудь, попозже попробую ещё.
 
Ответить
Сообщение
Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
Вот формула, которая может брать за образец 0

Большое спасибо!
Всё прекрасно работает!

Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
но не считает нули в правом столбце

В каждой правой колонке по одному нулю и больше быть не может, поэтому не критично.
А вот для левых это важно.
Спасибо, ещё раз!

Цитата Светлый, 08.05.2019 в 19:51, в сообщении № 10 ()
А можно ввести в формулу слагаемое для сдвига столбца рядом с фигурными скобками и менять только одно число в одной ячейке

А для последней формулы это тоже справедливо?
Что-то у меня не получилось. Делал так: 3+{0;7;14;21} для всех блоков с {0;7;14;21}.
Вполне возможно, что напутал где-нибудь, попозже попробую ещё.

Автор - Kaktus8
Дата добавления - 08.05.2019 в 22:55
Светлый Дата: Среда, 08.05.2019, 23:30 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
В рамках формулы со сдвигом. Рядом разные варианты.
К сообщению приложен файл: 10_1_2-2.xlsx (19.8 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВ рамках формулы со сдвигом. Рядом разные варианты.

Автор - Светлый
Дата добавления - 08.05.2019 в 23:30
Kaktus8 Дата: Четверг, 09.05.2019, 00:20 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо за образец. Неправильно я делал, оказывается.
И большое спасибо за помощь!
Первый вариант формулы мне тоже пригодился.
Всё классно получилось.

yes

P.S.
Да, и c уже наступившим Праздником!


Сообщение отредактировал Kaktus8 - Четверг, 09.05.2019, 00:23
 
Ответить
СообщениеСпасибо за образец. Неправильно я делал, оказывается.
И большое спасибо за помощь!
Первый вариант формулы мне тоже пригодился.
Всё классно получилось.

yes

P.S.
Да, и c уже наступившим Праздником!

Автор - Kaktus8
Дата добавления - 09.05.2019 в 00:20
Kaktus8 Дата: Суббота, 11.05.2019, 18:56 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Я извиняюсь, но есть ещё одна просьба.
У меня очень большой рабочий файл, и хотя формулы которые Вы написали, работают прекрасно, но применительно к моему файлу, очень сильно загружают процессор (все ядра) при их массовом добавлении.
Не могли бы Вы или кто-то ещё, как-то их упростить?
А то Excel стал затыкаться на несколько минут при каждом действии. Если убираю эти формулы или сокращаю их количество, то всё становится как раньше. Почитал разные статьи про оптимизацию Экселя и всё, что возможно сделал, но проблема сохраняется.
Если есть возможность, то можно их как-то оптимизировать?
64 битная версия 2016 экселя. Памяти в системе -16 Гб.


Сообщение отредактировал Kaktus8 - Суббота, 11.05.2019, 19:10
 
Ответить
СообщениеЯ извиняюсь, но есть ещё одна просьба.
У меня очень большой рабочий файл, и хотя формулы которые Вы написали, работают прекрасно, но применительно к моему файлу, очень сильно загружают процессор (все ядра) при их массовом добавлении.
Не могли бы Вы или кто-то ещё, как-то их упростить?
А то Excel стал затыкаться на несколько минут при каждом действии. Если убираю эти формулы или сокращаю их количество, то всё становится как раньше. Почитал разные статьи про оптимизацию Экселя и всё, что возможно сделал, но проблема сохраняется.
Если есть возможность, то можно их как-то оптимизировать?
64 битная версия 2016 экселя. Памяти в системе -16 Гб.

Автор - Kaktus8
Дата добавления - 11.05.2019 в 18:56
Светлый Дата: Воскресенье, 12.05.2019, 14:32 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Для увеличения быстродействия формулу можно располовинить, но когда заканчиваются данные, будет выдавать 99, 98 и т.д. Без сдвига:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1));100
Со сдвигом:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДля увеличения быстродействия формулу можно располовинить, но когда заканчиваются данные, будет выдавать 99, 98 и т.д. Без сдвига:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98))*100+СТРОКА($1:$99);СТРОКА(AH1));100
Со сдвигом:
Код
=ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}))=AH$4)*Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21}));Ч(СМЕЩ(J$5;ПРАВБ(СТРОКА($10000:$49999);4);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100)

Автор - Светлый
Дата добавления - 12.05.2019 в 14:32
Светлый Дата: Понедельник, 13.05.2019, 08:34 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Оказывается, диапазоны можно уменьшить. Скорость повысится. Попробуйте эти формулы:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4)*Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}));Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4)*Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}));Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100);"")
Код
=ЕСЛИ(AH11="";"";СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})))=AH11)))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеОказывается, диапазоны можно уменьшить. Скорость повысится. Попробуйте эти формулы:
Код
=ЕСЛИ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4)*Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}));Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1))>99;ОСТАТ(НАИБОЛЬШИЙ(ЧАСТОТА(ЕСЛИ((Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4)*Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21}));Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})));СТРОКА($1:$98)-1)*100+СТРОКА($1:$99)-1;СТРОКА(A1));100);"")
Код
=ЕСЛИ(AH11="";"";СУММ(Ч(ЕСЛИ(Ч(СМЕЩ(D$4;СТРОКА($1:9999);{0;7;14;21}+AH$5))=AH$4;Ч(СМЕЩ(J$4;СТРОКА($1:9999);{0;7;14;21})))=AH11)))

Автор - Светлый
Дата добавления - 13.05.2019 в 08:34
Kaktus8 Дата: Вторник, 14.05.2019, 19:02 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый,
большущее спасибо!
Скорость, действительно, повысилась. Причём, заметно.

Прошу прощения, что не ответил сразу: обновлял систему и офис (на всякий случай). Правда, не помогло.
Думал, может оптимизировали/улучшили что-нибудь и это скажется на быстродействии.

Сейчас, пока, терпимо, но придётся разбивать таблицу на части, чтобы по мере её заполнения опять не столкнуться с подобной ситуацией.

P.S.: Хм, интересно, может кто знает, а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Насколько это сложно?
Всё заново придётся делать или мастер конвертации справится?
С Акцессом дела практически не имел, но, говорят, там с быстродействием на больших объёмах всё хорошо.
Это я на всякий случай спрашиваю. Насколько подобное решение может быть оправдано при большом количестве вычислений?
 
Ответить
СообщениеСветлый,
большущее спасибо!
Скорость, действительно, повысилась. Причём, заметно.

Прошу прощения, что не ответил сразу: обновлял систему и офис (на всякий случай). Правда, не помогло.
Думал, может оптимизировали/улучшили что-нибудь и это скажется на быстродействии.

Сейчас, пока, терпимо, но придётся разбивать таблицу на части, чтобы по мере её заполнения опять не столкнуться с подобной ситуацией.

P.S.: Хм, интересно, может кто знает, а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Насколько это сложно?
Всё заново придётся делать или мастер конвертации справится?
С Акцессом дела практически не имел, но, говорят, там с быстродействием на больших объёмах всё хорошо.
Это я на всякий случай спрашиваю. Насколько подобное решение может быть оправдано при большом количестве вычислений?

Автор - Kaktus8
Дата добавления - 14.05.2019 в 19:02
Светлый Дата: Вторник, 14.05.2019, 20:42 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Там такие громоздкие формулы не нужны. Это простой запрос с сортированным выводом. Доли секунды будет считать.
В Excel другой принцип, формулы выполняют много лишних вычислений. С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
а в Access из Excel-a можно подобные формулы (например, из этой темы) перетащить?
Там такие громоздкие формулы не нужны. Это простой запрос с сортированным выводом. Доли секунды будет считать.
В Excel другой принцип, формулы выполняют много лишних вычислений. С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.

Автор - Светлый
Дата добавления - 14.05.2019 в 20:42
Kaktus8 Дата: Вторник, 14.05.2019, 21:37 | Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 79
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Т.е. в Акцессе организовать расчёты с логическими операциями с созданием на их основе дополнительных полей в базе не так удобно?
Получается, что не стоит заморачиваться и проще разбить таблицу на несколько частей с подсчётом по частям?
Похоже, так и придётся делать.
Цитата Светлый, 14.05.2019 в 20:42, в сообщении № 18 ()
С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.

Спасибо, учту это при разбивке таблицы.
 
Ответить
СообщениеТ.е. в Акцессе организовать расчёты с логическими операциями с созданием на их основе дополнительных полей в базе не так удобно?
Получается, что не стоит заморачиваться и проще разбить таблицу на несколько частей с подсчётом по частям?
Похоже, так и придётся делать.
Цитата Светлый, 14.05.2019 в 20:42, в сообщении № 18 ()
С дополнительными ячейками можно было бы и эти формулы ЗНАЧИТЕЛЬНО ускорить.

Спасибо, учту это при разбивке таблицы.

Автор - Kaktus8
Дата добавления - 14.05.2019 в 21:37
bmv98rus Дата: Вторник, 14.05.2019, 22:16 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 14.05.2019 в 20:42, в сообщении № 18 ()
Это простой запрос с сортированным выводом
Это не так, если с данными будет такой бардак. ибо таких вольностей с полями 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 подправить путь к файлу или надо крохотный макрос для этой автоматизации.
К сообщению приложен файл: Copy_of_936.xlsx (13.5 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 14.05.2019, 22:34
 
Ответить
Сообщение
Цитата Светлый, 14.05.2019 в 20:42, в сообщении № 18 ()
Это простой запрос с сортированным выводом
Это не так, если с данными будет такой бардак. ибо таких вольностей с полями 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
Дата добавления - 14.05.2019 в 22:16
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти число и выстроить его пары по убыванию (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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