Господа, у меня довольно специфическая задача, проконсультируйте, пожалуйста. В ячейках таблицы может содержаться натуральное число или несколько чисел через запятую с пробелом. При этом большинство клеток - пустые. Мне нужно добиться проверки корректности каждого столбца (например, в первой клетке столбца пусть будет значение TRUE/FALSE, корректен ли он). Столбец в моём случае считается "корректным", если в нём встречаются все числа от 1 до максимального значения по столбцу, без повторов и пропусков. Для каждого столбца максимум может быть своим + номера идут не по порядку. Могу ли я написать логическое выражение, которое проверяло бы это?
Отдельный подвопрос - мне бы хотелось иметь возможность вычислять значения сродни функциям MAX, COUNT, SUM при условии того, что в отдельных (редких) клетках может быть несколько чисел через запятую. Поясню: допустим в одном столбце есть клетки, содержащие "1", "2, 5" и "4, 3", а остальные пусты. Такой столбец корректен, при этом я хочу, чтобы посчитался MAX равный 5. COUNT и SUM будут считать не по столбцам, а по строкам. Мне нужно, чтобы COUNT считал вторую и третью клетки за 2, а SUM за 7.
Прикрепляю файл с примерами и правильными ответами и заранее благодарю.
Господа, у меня довольно специфическая задача, проконсультируйте, пожалуйста. В ячейках таблицы может содержаться натуральное число или несколько чисел через запятую с пробелом. При этом большинство клеток - пустые. Мне нужно добиться проверки корректности каждого столбца (например, в первой клетке столбца пусть будет значение TRUE/FALSE, корректен ли он). Столбец в моём случае считается "корректным", если в нём встречаются все числа от 1 до максимального значения по столбцу, без повторов и пропусков. Для каждого столбца максимум может быть своим + номера идут не по порядку. Могу ли я написать логическое выражение, которое проверяло бы это?
Отдельный подвопрос - мне бы хотелось иметь возможность вычислять значения сродни функциям MAX, COUNT, SUM при условии того, что в отдельных (редких) клетках может быть несколько чисел через запятую. Поясню: допустим в одном столбце есть клетки, содержащие "1", "2, 5" и "4, 3", а остальные пусты. Такой столбец корректен, при этом я хочу, чтобы посчитался MAX равный 5. COUNT и SUM будут считать не по столбцам, а по строкам. Мне нужно, чтобы COUNT считал вторую и третью клетки за 2, а SUM за 7.
Прикрепляю файл с примерами и правильными ответами и заранее благодарю.dum
Подождем Гуру, наверняка придумают что-то более логичное, ну или подскажут, как это реализовать через VBA.
А так, если:
1) Рабочая область не в несколько тысяч ячеек. 2) Данные однородны (т.е. в случае, если в ячейке несколько чисел, то между ними всегда стоит запятая + пробел). 3) Кол-во чисел в одной ячейке не больше, чем 3. 4) Числа однозначные.
То через вспомогательные таблицы можно это решить вот таким образом.
В принципе, пункты 2-4 корректируемые, т.е. просто нужно будет немного видоизменить функции. Но на мой взгляд это весьма условные костыли и лучше все же попробовать это сделать в VBA.
Подождем Гуру, наверняка придумают что-то более логичное, ну или подскажут, как это реализовать через VBA.
А так, если:
1) Рабочая область не в несколько тысяч ячеек. 2) Данные однородны (т.е. в случае, если в ячейке несколько чисел, то между ними всегда стоит запятая + пробел). 3) Кол-во чисел в одной ячейке не больше, чем 3. 4) Числа однозначные.
То через вспомогательные таблицы можно это решить вот таким образом.
В принципе, пункты 2-4 корректируемые, т.е. просто нужно будет немного видоизменить функции. Но на мой взгляд это весьма условные костыли и лучше все же попробовать это сделать в VBA.ArkaIIIa
Function OK(r As Range) Dim d As Object, c As Range, x, e, v&, m& Set d = CreateObject("scripting.dictionary") For Each c In r If Not IsEmpty(c) Then x = Split(CStr(c.Value), ",") For Each e In x v = Val(Trim(e)): d(v) = 0& If v > m Then m = v Next End If Next OK = Application.Transpose(Array(m, d.Count = m)) End Function
[/vba] пример использования на листе - для B1:B2 формула массива
Код
=OK(B3:B27)
и можно тянуть вправо
udf[vba]
Код
Function OK(r As Range) Dim d As Object, c As Range, x, e, v&, m& Set d = CreateObject("scripting.dictionary") For Each c In r If Not IsEmpty(c) Then x = Split(CStr(c.Value), ",") For Each e In x v = Val(Trim(e)): d(v) = 0& If v > m Then m = v Next End If Next OK = Application.Transpose(Array(m, d.Count = m)) End Function
[/vba] пример использования на листе - для B1:B2 формула массива
моя udf сейчас равнодушна к повторам. дополнил:[vba]
Код
Function OK(r As Range) Dim d As Object, c As Range, x, e, v&, m&, n& Set d = CreateObject("scripting.dictionary") For Each c In r If Not IsEmpty(c) Then x = Split(CStr(c.Value), ",") For Each e In x v = Val(Trim(e)): d(v) = 0&: n = n + 1 If v > m Then m = v Next End If Next OK = Application.Transpose(Array(m, IIf(n = m, d.Count = m, False))) End Function
моя udf сейчас равнодушна к повторам. дополнил:[vba]
Код
Function OK(r As Range) Dim d As Object, c As Range, x, e, v&, m&, n& Set d = CreateObject("scripting.dictionary") For Each c In r If Not IsEmpty(c) Then x = Split(CStr(c.Value), ",") For Each e In x v = Val(Trim(e)): d(v) = 0&: n = n + 1 If v > m Then m = v Next End If Next OK = Application.Transpose(Array(m, IIf(n = m, d.Count = m, False))) End Function
ArkaIIIa, спасибо, нро числа не однозначные (пока доходит до 40) + доп. таблиц не хочется + пустых много... Но благодарю, что откликнулись первым.
ikki, спасибо. Это вполне себе крепкое решение.
Но вот я всё думаю, нельзя ли это сделать стандартными функциями, через формулы массива, например. Больше года назад у меня была похожая, в чём-то более сложная, но всё же существенно отличная задача (там допускались повторы, например). Как же коллеги её прекрасно решили тогда - http://www.excelworld.ru/forum/2-4595-1 ! И вы, ikki, тоже заходили тогда.
ArkaIIIa, спасибо, нро числа не однозначные (пока доходит до 40) + доп. таблиц не хочется + пустых много... Но благодарю, что откликнулись первым.
ikki, спасибо. Это вполне себе крепкое решение.
Но вот я всё думаю, нельзя ли это сделать стандартными функциями, через формулы массива, например. Больше года назад у меня была похожая, в чём-то более сложная, но всё же существенно отличная задача (там допускались повторы, например). Как же коллеги её прекрасно решили тогда - http://www.excelworld.ru/forum/2-4595-1 ! И вы, ikki, тоже заходили тогда.dum
Ну я не ожидал и того, что ту прошлую задачу удастся решить стандартными функциями, но гуру сделали - до сих пор работает Я так понимаю, что ключевая проблема в новой задаче, которую я поставил сейчас, состоит в том, что недопустимы повторы, потому использованные ранее механизм MATCH(ROW(INDIRECT("1:"&MAX(...))) работать не будет - он повторы не просечёт...
Ну я не ожидал и того, что ту прошлую задачу удастся решить стандартными функциями, но гуру сделали - до сих пор работает Я так понимаю, что ключевая проблема в новой задаче, которую я поставил сейчас, состоит в том, что недопустимы повторы, потому использованные ранее механизм MATCH(ROW(INDIRECT("1:"&MAX(...))) работать не будет - он повторы не просечёт...dum
Только позвольте ещё спросить - как таким же образом сделать сумму. Чтобы "1, 4, 5" плюс "3, 7" плюс "5" было 25. Тут повторение пятёрки становится проблемой. Сумма нужна по строкам, потому на корректность повторы не влияют.
Только позвольте ещё спросить - как таким же образом сделать сумму. Чтобы "1, 4, 5" плюс "3, 7" плюс "5" было 25. Тут повторение пятёрки становится проблемой. Сумма нужна по строкам, потому на корректность повторы не влияют.dum
Спасибо. Но меня интересует, в первую очередь, может ли быть вариант с помощью стандартных функий и формул массива. С макрами или vba, понятно, что это можно сделать.
Спасибо. Но меня интересует, в первую очередь, может ли быть вариант с помощью стандартных функий и формул массива. С макрами или vba, понятно, что это можно сделать.dum
А что именно мешает в данном случае? Каков признак этой невозможности? Я бы хотел как раз научиться понимать, когда стандартными функциями задача не решается.
Или, может, кто-то предложит решение?
А что именно мешает в данном случае? Каков признак этой невозможности? Я бы хотел как раз научиться понимать, когда стандартными функциями задача не решается.
Формула правильно вычислит сумму чисел в ячейку С7, если количество знаков до 9, и все числа однозначные. Для семи ячеек в строке таблицы формула будет в 7 раз длинее.
Стандартными функциями, только для ячейки С7, в файле из сообщения №14. Формула массива
Формула правильно вычислит сумму чисел в ячейку С7, если количество знаков до 9, и все числа однозначные. Для семи ячеек в строке таблицы формула будет в 7 раз длинее.AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
И даже не формулы массива. Считает числа до 99. Если нужно больше, то в формуле замените 99 на 999 (или еще больше) Единственное ограничение - если в одной ячейке написано что-то типа 2, 2, 2, 2, 2, 2, то посчитает первую двойку, последнюю и одну из тех, что в середине - 2+2+0+0+0+2 (если 1, 2, 3, 2, 3, 3 - даст 1+2+3+0+0+3). Но вроде, если я верно понял самый первый пост, в одной ячейке не может быть одинаковых чисел
И даже не формулы массива. Считает числа до 99. Если нужно больше, то в формуле замените 99 на 999 (или еще больше) Единственное ограничение - если в одной ячейке написано что-то типа 2, 2, 2, 2, 2, 2, то посчитает первую двойку, последнюю и одну из тех, что в середине - 2+2+0+0+0+2 (если 1, 2, 3, 2, 3, 3 - даст 1+2+3+0+0+3). Но вроде, если я верно понял самый первый пост, в одной ячейке не может быть одинаковых чисел_Boroda_
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
