последовательность (сумм количества (повторных значений))

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

последовательность (сумм количества (повторных значений)) (Формулы)

koyaanisqatsi

Дата: Четверг, 25.07.2013, 12:56 | Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

Есть в экселе такая функция как подсчет повторных значений. Выглядит следующим образом

Код

=СЧЁТЕСЛИ(B2:B5;"=3")

есть недостаток этой функции я могу в данной ячейки получить подсчет только по количеству значений равных "3"

вот пример какие бывают значения в таблице 9, 9, 7, 6, 5, 5, 5, 5, 5, 3, 3.5, 2, 2, 2, 2, 2.5, 2.5, 2.5, 2.1, 2, 2, 2, 1.7, 1.5, 1.5, 1.2, 1.1, 1, 1, 1, 0.8, 0.7, 0.5, 0.5, 0.5, 0.2. 0.1 и др.
примерно так. желательно вывести в сводной таблице сколько каких развесов. например как-то так:
2/9 , 1/7 , 1/6, 5/5, 1/3, 1/3.5, 4/2, 2/2.5, и тд.. (я нарушил последовательность по возрастанию но это не принципиально .)

еще не умею выводить значения в формате приведенного мной примера 2/9 (две девятки) умею лишь так: ,/10 2,/9 ,/8 1,/7 (такогорезультата могу достичь следующим методом. Форматирование ячеек указав формат #,###\/20 )
но это не удобно когда вся таблиц заполнена пустыми значениями количества повторных значений.

Сейчас я выписываю все развесы подряд, но когда пойдет большой объем повторных значение будет море. Все придется считать самому. Количество развесов может дойти до 60 это примерно. в сжатом виде это выглядит так: 14/10, 13/5, 8/3, 17/2, 2/1, 1/6,5, 1/2,2, 1/8,7, 1/3,4

Извините пожалуйста за нарушение правил. Заранее предупреждаю файл очень объемный примерно весит 1.2Mb http://koyaanisqatsi.spb.ru/exel/2013.07.25.xlsx
Тут видно как коряво работает система повесам в репе, квашеной капусте, и соленых огурцах.

Еще один вопрос по этому файлу. Там есть вкладка "огурцы" там этикетки по фасовке. я хочу сделать автоматическую транспортировку развесов в ячейки этикеток. В принципе я это умею делать но как присвоить нужные значения для каждой ячейки внутри этикеток в автоматическом режиме на подобии как растягиваются формулы я не знаю. А задавать для каждой отдельной этикетки следующее значение не очень то просто при условии если развесов будет под сотню а количество наименований товаров с развесами будет около 20штук.

Благодарю за помощь.

[admin]Оформляйте коды тегами![/admin]

Есть в экселе такая функция как подсчет повторных значений. Выглядит следующим образом

Код

=СЧЁТЕСЛИ(B2:B5;"=3")

Сообщение отредактировал koyaanisqatsi - Четверг, 25.07.2013, 13:26

Ответить

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

Код

=СЧЁТЕСЛИ(B2:B5;"=3")

Автор - koyaanisqatsi
Дата добавления - 25.07.2013 в 12:56

Pelena

Дата: Четверг, 25.07.2013, 15:33 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19420

Репутация: 4567 ±

Замечаний: ±

Excel 365 & Mac Excel

koyaanisqatsi, очень сомневаюсь, что найдутся желающие разбираться в таком объёме информации.
Читайте в Правилах форума Рекомендации по составлению примера

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 16:21 | Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

Pelena,
Если просто надо посчитать сколько каких развесов в таблице и вывести этот результат.

Причем так как я не умею это делать правильно я нарисовал это через формат ячеек мануально. Хочу чтобы это было в автоматическом режиме желательно без макросов.

Иногда легче объяснить как можно подробнее чем напрягать человека делать одно и то же в доделками и всевозможными нюансами.

К сообщению приложен файл: 3057957.xlsx (10.3 Kb)

Ответить

_Boroda_

Дата: Четверг, 25.07.2013, 16:34 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16771

Репутация: 6551 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Ну вот, совсем другое дело. Пример есть, в нем все понятно описано. Ловите:

Код

=СЧЁТЕСЛИ($B3:$P3;НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$P3;-$B3:$P3;)=СТОЛБЕЦ($B3:$P3)-1)*$B3:$P3;СТОЛБЕЦ()-1))&"/"&НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$P3;-$B3:$P3;)=СТОЛБЕЦ($B3:$P3)-1)*$B3:$P3;СТОЛБЕЦ()-1)

Формула массива, вводится одновременным нажатием Контрл Шифт и Ентер

К сообщению приложен файл: 3057957_1.xlsx (11.2 Kb)

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

Ответить

Формуляр

Дата: Четверг, 25.07.2013, 16:36 | Сообщение № 5

Группа: Друзья

Ранг: Ветеран

Сообщений: 832

Репутация: 255 ±

Замечаний: 0% ±

Excel 2003, 2013

koyaanisqatsi,
сводная таблица вам поможет.
Только данные правильно расположите.

К сообщению приложен файл: 3057957.xls (28.0 Kb)

Excel 2003 EN, 2013 EN

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 17:23 | Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

_Boroda_,
Спасибо большое очень красиво и как раз то что надо. Есть единственный вопрос по этой формуле.
Когда растягиваю на всю вероятную длину он отображает пустые значения. Каким образом их лучше скрыть ?

К сообщению приложен файл: 6765939.xlsx (10.7 Kb)

Ответить

_Boroda_

Дата: Четверг, 25.07.2013, 17:55 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16771

Репутация: 6551 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Код

=ЕСЛИ(СЧЁТ($B3:$P3)<=СУММ(--ПСТР($A8:A8&"0/";1;ПОИСК("/";$A8:A8&"0/")-1));"";СЧЁТЕСЛИ($B3:$P3;НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$P3;-$B3:$P3;)=СТОЛБЕЦ($B3:$P3)-1)*$B3:$P3;СТОЛБЕЦ()-1))&"/"&НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$P3;-$B3:$P3;)=СТОЛБЕЦ($B3:$P3)-1)*$B3:$P3;СТОЛБЕЦ()-1))

файл 2
или условным форматированием
файл 3

К сообщению приложен файл: 6765939_2.xlsx (11.4 Kb) · 6765939_3.xlsx (11.4 Kb)

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 18:50 | Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

_Boroda_,

Код

=ЕСЛИ(СЧЁТ($B3:$v3)<=СУММ(--ПСТР($A8:A8&"0/";1;ПОИСК("/";$A8:A8&"0/")-1));"";СЧЁТЕСЛИ($B3:$v3;НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$v3;-$B3:$v3;)=СТОЛБЕЦ($B3:$v3)-1)*$B3:$v3;СТОЛБЕЦ()-1))&"/"&НАИБОЛЬШИЙ((ПОИСКПОЗ(-$B3:$v3;-$B3:$v3;)=СТОЛБЕЦ($B3:$v3)-1)*$B3:$v3;СТОЛБЕЦ()-1))

Попытался увеличить диапазон анализируемого фрагмента. Так уже не работает.

"Формула массива, вводится одновременным нажатием Контрл Шифт и Ентер" - это встречал и на форумах и и в хелпе экселя но так и не понял как это работает

Ответить

AndreTM

Дата: Четверг, 25.07.2013, 20:02 | Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 1762

Репутация: 501 ±

Замечаний: 0% ±

2003 & 2010

Цитата (koyaanisqatsi, 25.07.2013 в 18:50, в сообщении №8)

Формула массива, вводится одновременным нажатием...

Выбираете ту самую первую ячейку, в которую вы вписали формулу.
Щёлкаете по любому месту строки формул (или нажимаете F2).
Нажимаете Ctrl+Shift+Enter (в отображении формулы появляются фигурные скобки).
"Растягиваете" формулу на нужный диапазон...

Skype: andre.tm.007
Donate: Qiwi: 9517375010

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 21:14 | Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

AndreTM,

Спасибо получилось.

Сообщение отредактировал koyaanisqatsi - Четверг, 25.07.2013, 21:51

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 23:17 | Сообщение № 11

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

_Boroda_,
До сих пор сражаюсь с вашей формулой.

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

И не понял в чем подвох не отображения значений 0/0 иногда копирую вашу формулу как массив и бывает нулевые значения начинают снова отображаться. В формате ячеек я не нашел шаманства с вашей стороны.

вопрос №2
В исходном файле есть формулы которые считают количество двадцаток десяток пятерок троек и двоек по определенным правилам спец веса вообще не делят.
Вывод результатов пока у меня оформлен в корявом виде пример в прилагаемом файле на вкладке расходники. Можно ли вывести результат также как вы предложили с огурцами ? (то есть избавится от шапки которая объясняет какого развеса это количество а просто также по возрастающей без нулевых значений вывести все результаты.)

К сообщению приложен файл: Realnost.xlsx (15.7 Kb)

Ответить

koyaanisqatsi

Дата: Четверг, 25.07.2013, 23:49 | Сообщение № 12

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

_Boroda_,

Код

=СТРОКА(овощи!$EC2:$EC150)-1)*овощи!$EC2:$EC150;СТОЛБЕЦ()-1))&"/"&НАИБОЛЬШИЙ((ПОИСКПОЗ(-овощи!$EC2:$EC150;-овощи!$EC2:$EC150;)=СТРОКА(овощи!$EC2:$EC150)-1)*овощи!$EC2:$EC150;СТОЛБЕЦ()-1))

справился с вашей формулой

:booze: Но появилась одна проблемка. Он мне считает количество нулевых позиций. То есть у меня в столбике в не заполненной таблице как выводит в промежуточном варианте 149 нулей так он и считает эту сумму и пишет 149/0

К сообщению приложен файл: Realnost_2.xlsx (16.2 Kb)

Сообщение отредактировал koyaanisqatsi - Пятница, 26.07.2013, 12:01

Ответить

koyaanisqatsi

Дата: Пятница, 26.07.2013, 12:49 | Сообщение № 13

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

_Boroda_,
Помогите пожалуйста. Я удалил все лишне в таблице она теперь весит в три раза меньше.
http://koyaanisqatsi.spb.ru/exel/123.xlsx

Вы можете посмотреть как выводится информация по капусте и репе на вкладке расходник ?
Моно сделать так как у вас получилось с помощью вашей формулы ?

Желательно числа с десятыми выводить в капусте после целых а для репы не актуально. Но можно и так и так. Люди смогут привыкнуть думаю.