Страницы: 1
RSS
Поиск минимального/максимального значения, Использование формулы массива в VBA
 
Дано:
В ячейках B1:B4 правильные даты в текстовом формате, выгружаются из внешней СУБД. мне нужно найти минимальную дату и максимальную дату. Есть вариант через вспомогательный столбик =Значен("B1", есть вариант через формулу массива  {=МИН(ЗНАЧЕН(B1:B4))}
Мне нужно это сделать через VBA без вспомогательных ячеек. Макрорекордер записал Range("B6"].FormulaArray = "=MIN(VALUE(R[-5]C:R[-2]C))" , а мне нужно что то типа ааа = MIN(VALUE(R[-5]C:R[-2]C))

Подскажите пожалуйста как это сделать?
 
А почему не преобразовать в числа?  
 
а Поиск не помогает? :) - например, здесь... переменные брать типа As Date по логике тех строк - и будет вам вариант получше формулы на вба, полагаю... (изначально весь ваш Range возьмите в массив - arr)... успехов
Изменено: JeyCi - 17.03.2015 21:41:32
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Выводит максимальную дату в выделенном диапазоне
Код
Sub tt()
MsgBox (Format(Application.WorksheetFunction.Max(Selection), "dd/mm/yyyy"))
End Sub

 
Уважаемый tvit!
Зачем нужны такие "заковыристые" формулы?
См. макрос: ищет MIN (MAX) в любом количестве дат в столбе А.
Изменено: Мотя - 17.03.2015 21:54:23
 
Спасибо за ответы. но к сожалению все не то.

to Gling: то что предлагаете Вы у меня уже есть в моем вопросе. но тут используется дополнительная ячейка. а мне нужно сразу присвоить переменной.
to MBT: Ваш вариант вообще не работает
to Мотя: вариант решения через цикл я знаю. но мне нужно именно как одной формулой присвоить значение переменной.
Прошу прощения если не точно сформулировал свой вопрос

 
 
Может так? (честно не совсем понял как надо)
 
Вы сначала вычисления присваиваете ячейке, а затем значение ячейки присваиваете переменной. А я спрашиваю как сразу присвоить переменной, не используя вспомогательных ячеек
 
Цитата
tvit написал: А я спрашиваю как сразу присвоить переменной, не используя вспомогательных ячеек
tvit я вам посоветовала воспользоваться Поиском (линки сбились - но эта вкладка находится вверху страницы около Пользователи и Правила) - MIN в Поиск - и уже с первого листа Поисковика видны примеры... вариант для любого числа (включая дробное)
Код
Sub е()
Dim arr(), min#, max#
'arr = Selection.Value
min = Application.min(Selection)
    MsgBox "min - " & min
max = Application.max(Selection)
    MsgBox "max - " & max
End Sub
в вашей ситуации, как видите, проблема НЕ в способе поиска мин и макс, а в нач форматах ваших данных... ищите способ объяснить xl что он смотрит на даты - я с ходу точно не знаю как... успехов
P.S. если же вы хотите использовать формулу ячейки для расчёта значения, которое нужно в переменную, то придётся итог работы этой формулы сначала выгрузить в ячейку... имхо... потом взять в переменную
Изменено: JeyCi - 09.05.2015 14:40:38
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
tvit, думаю, что без цикла никак. Попытки запихнуть вашу функцию в Evaluate не увенчались успехом (что странно, так как массивные функции обрабатываются ею нормально), возможно, причина в том, что EVALUATE не хочет работать с вложенной VALUE или DATEVALUE из-за того, что даты не в буржуйском формате.
условно говоря, вот это работает:
Код
Worksheets(1).Evaluate("=VALUE(" & "" & "1+1" & "" & ")" & "" ) = 2 

а это нет
Код
Worksheets(1).Evaluate("=VALUE(" & "" & "05.05.15" & "" & ")" & "" ) = Error 2015

а вот это - работает, если в С1:С4 поместить например 1,2,3,4 в текстовом формате:
Код
Worksheets(1).Evaluate("=MIN(VALUE(C1:C4))")
F1 творит чудеса
 
tvit,вообще-то, работает, просто не присваивает значения переменной, а выводит в текстовом формате. А для того, чтобы код знал, что вам нужна дата, то и объявите переменную, как дату:
Код
Sub tt()
Dim D As Date
D = Application.WorksheetFunction.Max(Selection)
MsgBox (D + 2)
End Sub
У меня, во всяком случае, этот макрос работает
 
Цитата
Максим Зеленский написал: думаю, что без цикла никак.
думаю так же... ещё один вариант (от поисковика ;) yandex)
Код
Sub DateMinMax()
'http://www.mrexcel.com/forum/excel-questions/426072-compare-dates-visual-basic-applications.html
'compare dates in VBA
'ZVI Oct 29th, 2009, 10:57 PM
  Dim d1, min As Date, max As Date, c As Range
    min = [b1].Value: max = [b1].Value
    For Each c In Range("b1:b4")
        d1 = Split(c.Text, ".")
        d1 = DateSerial(d1(2), d1(1), d1(0))
            If d1 < min Then
                min = d1
            ElseIf d1 > max Then
                max = d1            
            End If
    Next c
  Debug.Print min & "-min; max-" & max
End Sub
Изменено: JeyCi - 09.05.2015 14:40:53
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
МВТ написал: У меня, во всяком случае, этот макрос работает
у меня выдает 01.01.1900 - xl 2010 ru -
может быть дело в системных настройках, наверно...
Изменено: JeyCi - 09.05.2015 14:41:04
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, а Вы диапазон с датами выделили? Макрос работает с Selection
 
Спасибо всем откликнувшимся. Если без цикла никак, тогда проще найти свободную ячейку в Excel и воспользоваться формулой массива :-)
 
Цитата
МВТ написал: макрос работает с Selection
в том то и дело, что как только не выделяла - работает только на две ячейки, в которых вставлена формула (b6:b7) - на (b1:b4) не работает... выдаёт то 00:00:00, то 01.01.1900... пробовала поколдовать с форматом ячеек - тоже какие-то странные итоги... вобщем, слабо поняла, как меня понимает xl... ну и ладно, :) не тревожьтесь...  - решение по ветке общими усилиями нашли и отлично... всем успехов  
Изменено: JeyCi - 09.05.2015 14:41:16
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Прикрепляю файл с вставленными макросом и пояснениями
 
МВТ, так в том и дело, что изначально даты сохранены как текст.
Вы попробуйте макрос запустить на B1:B4 в исходном файле автора. Получите результат 0.
F1 творит чудеса
 
Максим Зеленский,понял: протупил - я сам даты вводил. Так может, проще поменять формат дат на Дату :)? Нет, можно и без этого, но не очень удобно. Тогда можно чтобы макрос сам преобразовывал текстовые значения в даты:
Код
Sub tt()
Dim D As Date, Rng As Range
Application.ScreenUpdating = False
If Selection.Count < 2 Then
    MsgBox ("Выделено менее двух ячеек. Выделите диапазон дат, например, А1:А5")
    Exit Sub
End If
Set Rng = Selection
For Each Cell In Rng
If Not IsNumeric(Cell) Then Cell.Value = CDate(Cell.Value)
On Error Resume Next
Next Cell
Rng.NumberFormat = "dd/mm/yyyy"
Rng.Value = Rng.Value
D = Application.WorksheetFunction.Max(Rng)
Application.ScreenUpdating = True
MsgBox ("Максимальная дата в выделенном диапазоне " & Replace(Selection.Address, "$", "") & ": " & D)
End Sub


Изменено: МВТ - 18.03.2015 15:00:00 (Добавил макрос)
 
Цитата
МВТ написал: проще поменять формат дат на Дату
как вариант (с Selection) без цикла
Код
Sub e1()
Dim min As Date, max As Date
Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 4)  '4 - DMY Date format
min = Application.min(Selection)
max = Application.max(Selection)
MsgBox min & vbNewLine & max
End Sub
ВСЁ  - думаю, короче и без цикла у меня уже точно не получится... 8)  (поправила)
Изменено: JeyCi - 09.05.2015 14:41:29
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написал: ВСЁ  - думаю, короче и без цикла у меня уже точно не получится...
Немного не то, что я просил (все таки идет изменение исходного файла), но решение очень красивое, нужно запомнить. Мне часто нужно делать нечто подобное, но все как-то извращенными способами приходилось это делать :D
Изменено: tvit - 09.05.2015 14:41:47
 
Максим Зеленский, пытался осмыслить, что Вы написали. Действительно, если в исходном файле заменить в датах точку на слеш "/" , то все начинает работать. Похоже глюк VBA, в оболочке русификация отрабатывает отлично,  а в VBA нет.
 
tvit,если для вас настолько критично сохранять даты в текстовом формате, можете прописать в макросе создание в свободной колонке временного массива в правильном формате, его обработку и последующее удаление
 
Обрабатываемые данные выгружаются в текстовые файлы из централизованной АС и на них я повлиять ни как не могу. А поскольку объем каждого из этих файлов может занимать 300-400 Мб по 500 тысяч строк и более, то вопрос с нехваткой памяти стоит достаточно остро, поэтому прежде чем добавить вспомогательный столбец, я 10 раз подумаю.
 
Цитата
tvit написал: Обрабатываемые данные выгружаются в текстовые файлы из централизованной АС и на них я повлиять ни как не могу
Но Вы можете влиять на импорт текстов в Excel. Используйте Мастер импорта текстов (он запускается автоматически при открытии файлов .txt) и задайте формат Дата:ДМГ нужному столбцу.
Можно написать макросы для импорта каждого типа файлов.
 
Цитата
tvit написал: Похоже глюк VBA, в оболочке русификация отрабатывает отлично,  а в VBA нет.
связано скорее с тем, что функции преобразования типов в VBA не работают с массивами напрямую (только с элементами), а прямого аналога функции ЗНАЧЕН в объекте WorksheetFunction нет. Фактически, в рамках поставленных вами условий (без циклов, без задействования дополнительных столбцов) получить результат можно было только при использовании метода Evaluate, запихивая в него готовую формулу. EVALUATE - наследие XML, предшественника VBA, и кроме даты в US-формате, есть еще некоторые ограничения. Для прочих сложностей с международными стандартами в VBA есть Application.International с кучей региональных настроек.
Изменено: Максим Зеленский - 09.05.2015 14:42:48
F1 творит чудеса
Страницы: 1
Наверх