Страницы: 1
RSS
Подсчет количества числовых строк в массиве
 
Добрый день! Есть 3 столбца ячеек с данными: цифрами и кавычками = " ", а также пустые ячейки без чисел и без кавычек. Требуется подсчитать количество строк с ЧИСЛОВЫМИ данными внутри массива с данными. Под строкой в данном примере понимается размерность матрицы из ячеек по горизонтали - выделил на скриншоте эллипсом. В данном примере строка состоит из 3 ячеек.
1. Строка берётся в расчет, если она содержит хотя бы ОДНО из числовых значений от отрицательных, нуля до положительных значений. Пустые ячейки и ячейки с кавычками не берутся в расчет.
2. Количество строк и столбцов может быть больше.
3. Учесть в формуле, что если данных в массиве нет, то кол-во строк = 0.
4. Решить задачу элементарными формулами без использования массивных скобок  { }.

Скриншот результатов привел ниже. Файл прикрепил.
Изменено: Tidus - 21.05.2017 12:59:34
 
.
 
Еще вариант
=СУММПРОИЗВ(--((ЕЧИСЛО(B4:B14)+ЕЧИСЛО(C4:C14)+ЕЧИСЛО(D4:D14))>0))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
copper-top, Вы Ввели доп. столбец. Ваша формула не подходит.
 
Tidus, о чём Ваша "цитата"? Обратиться по имени можно, нажав кнопку "Имя".
 
Bema, спасибо. Формула работает! :) А короче формулу нереально сделать? Ато у меня тут итак внутри функции пятиэтажные формулы получаются :)
 
Цитата
Tidus написал:
А короче формулу нереально сделать?
А зачем?
Цитата
Tidus написал:
Вы Ввели доп. столбец. Ваша формула не подходит.
Вы бы для начала поблагодарили copper-top, а потом уже высказывали свои замечания. А то складывается впечатление, что Вы жюри, а помогающие участники конкурса на лучшую формулу.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Tidus, Вы не знаете, что такое цитата. Поэтому, если я ещё раз увижу, что Вы используете кнопку цитирования для ответа, я буду закрывать/удалять Ваши темы.
 
=СУММПРОИЗВ(ЕЧИСЛО(B4:D14)*(B4:D14>0))
Алексей М.
 
=СЧЁТ(1/МУМНОЖ(--(B4:D14<>"");ТРАНСП(СТОЛБЕЦ(B:D)^0)))
Формула массива, ввод тремя клавишами
 
АlехМ, спасибо! Ваша формула шикарная. Но она не учитывает строки с 0 значениями (цифра 0).
Изменено: Tidus - 21.05.2017 15:31:56
 
Bema, спасибо за замечания, я их учту в будущем, не держите обиды.
Изменено: Tidus - 21.05.2017 15:34:25
 
Спасибо.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Bema, столбцов очень много в реальной задаче. Ваша формула слишком громоздная получается, из-за этого код трудночитаем внутри функции. Если бы как у AlexM выделялся массив, была бы очень удачная компактная формула.
 
Tidus, Я не понял задание.
Используйте формулу Виктора.
Изменено: АlехМ - 21.05.2017 15:48:27
Алексей М.
 
АlехМ,введите в любую пустую строчку цифру 0. И у вас формула её не возьмет в расчет. Хотя должна считать.
 
сначала
Цитата
Tidus написал:
элементарными формулаМИ
затем
Цитата
Tidus написал:
Ввели доп. столбец и т.д.
я, наверное, на экзамен попал. :)  
 
copper-top,не обижайтесь, но с доп. столбцом все могут решить :)
 
Заметил, что формула Виктора работает не совсем корректно.
Предлагаю доработанный вариант этой формулы.
=СЧЁТ(1/МУМНОЖ(--ЕСЛИ(ЕЧИСЛО(B4:D14);1^B4:D14);ТРАНСП(СТОЛБЕЦ(B4:D14)^0)))
В файле можно сравнить работу двух формул.
PS. Если столбцов всегда три
=СЧЁТ(1/МУМНОЖ(--ЕСЛИ(ЕЧИСЛО(B4:D14);1^B4:D14);{1:1:1}))
Изменено: АlехМ - 21.05.2017 20:43:47
Алексей М.
 
АlехМ,а зачем формула Виктора? Она массивная же. Её будет затруднительно использовать в конструкциях других немассивных формул. Bema дал корректную отличную немассивную формулу, правда "громоздкую" в случае увеличения кол-ва столбцов без массивов: =СУММПРОИЗВ(--((ЕЧИСЛО(B4:B14)+ЕЧИСЛО(C4:C14)+ЕЧИСЛО(D4:D14))>0))

В моей задаче пока 5 столбцов, поэтому это некритично. Для тех, у кого более 10 столбцов, формула будет уже нечитаема :)
Изменено: Tidus - 21.05.2017 23:28:49
 
Цитата
Tidus написал:
Она массивная же. Её будет затруднительно использовать в конструкциях других немассивных формул
Если честно, то в первый раз такое слышу. Хотя вводить формулы массива в три раза сложнее, так как для ввода надо нажать не одну, а три клавиши.
Цитата
Tidus написал:
Для тех, у кого более 10 столбцов, формула будет уже нечитаема
Поэтому не отказывайтесь от массивных формул.
Изменено: АlехМ - 21.05.2017 23:31:32
Алексей М.
 
АlехМ,из 10-летнего опыта работы в Excel (как пользователя), у меня есть файлы под 50 Мб с формулами и однажды были ситуации, что в 1-й из строк не были введены фигурные скобки. Причем строк этих тысячи! Вы не представляете сколько времени я потратил, чтобы отыскать ошибку. В одной из ячеек не было фигурных скобок! За это время можно было создать кучу немассивных формул, они надежнее и эффективнее в работе :) Не массивные формулы - это верх совершенства в Excel.
Изменено: Tidus - 21.05.2017 23:31:57
 
Цитата
Tidus написал:
файлы под 50 Мб с формулами
В этом случае надо отказаться от любых формул и использовать VBA
Алексей М.
 
АlехМ, смысла не вижу. Ведь файл очень быстро работает и делает расчеты. Конструкции элементарными формулами проще строить по блок-схеме и главное быстрее их можно откорректировать и глубоких знаний программирования не требуется.
Изменено: Tidus - 21.05.2017 23:38:38
 
А все-таки задумайтесь. Excel не любит большие объемы. Чтобы потом не искать локти, об которые зубы точить )
 
Цитата
Tidus написал: строк этих тысячи
Тем более нужно уходить от формул. И не будет файлов по 50М. И работать будет быстрее.
 
Юрий М, я с Вами не согласен. Мне важно видеть все данные на листе, как они обрабатываются и что с ними происходит в процессе расчета, чтобы быстро обнаружить ошибку. С таблицами всегда удобнее работать, ну и платить приходится тем, что файлы большого объема. А если всё делать в памяти внутри кода, то к коде из 1000 строк гораздо сложнее найти ошибку в формулах, чем в таблице, где все данные на виду. Я имею ввиду не про отладку, а про ошибки работы самих алгоритмов - их блок-схемы. Блок-схемы отладчик Вам не покажет.
Изменено: Tidus - 22.05.2017 13:33:54
 
Вы ошибаетесь. В коде можно оставлять комментарии и весь код будет прозрачным. Но, видимо, Вас с прямиго пути (правильного ли?) не столкнуть :)
 
Цитата
Tidus написал:
А если всё делать в памяти внутри кода, то к коде из 1000 строк гораздо сложнее найти ошибку в формулах, чем в таблице, где все данные на виду.
Уважаемый Tidus касательного вашего примера, код который выполняет действия согласно Вашего примера менее 1000 строк, конечно его можно усложнить при желании.
Код
Sub КоличествоСтрок_с_Числами()
Dim rng As Range, vl, temp, i  As Long, Summ As Long
Const ClmnCount As Long = 3 'констатнта количество столбцов (полей) в массиве
With ThisWorkbook
  Set rng = .Sheets("Лист1").Range("b4:b14") 'диаппазон первого поля массива
  For Each vl In rng
    For i = 0 To ClmnCount - 1 'анализ массива (поиск чиловых значений)
      temp = .Sheets("Лист1").Cells(vl.Row, vl.Column + i).Value
      If Not IsEmpty(temp) Then If IsNumeric(temp) Then Summ = Summ + 1: Exit For
    Next i
  Next
End With
Set rng = Nothing 'финальная стадия. очистка памяти вывод результата
MsgBox "Результат = " & Summ, vbInformation, "ОТВЕТ"
End Sub

Изменено: TSN - 22.05.2017 17:05:16
Страницы: 1
Наверх