Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Формат чисел Excel VBA / 23 сообщений из 23, страница 1 из 1
07.10.2012, 12:34
    #37986951
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Здравствуйте!

Волею судеб пришлось написать свою первую функцию на VBA. Никак не могу отойти от шока ;( Это же жуть какая-то, редактор постоянно пытается компилировать строки, показывая дурацкие диалоги с предложением открыть справку. Сохраняет функцию секунд 5. Как такое средство разработки может существовать в 2012 году??? ;(((

Теперь вопрос ) v1 - число с плавающей точкой. При его форматировании в качестве десятичного разделителя используется запятая, но функция CountIf понимает критерий только если десятичный разделитель - точка! Хак с Replace это нормальное решение или есть более правильное?

Ещё более удивительно то, что если сделать переменные n1 и n2 типа Integer, функция валится на вычислении (n1 * n2 * (n1 + n2 + 1). Как это возможно??? n1, n2 - меньше 50.
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Function MannWhitneyU(sample1 As Range, sample2 As Range)
    Dim n1 As Double
    Dim n2 As Double
    Dim u1 As Double
    Dim q As Double
    n1 = WorksheetFunction.Count(sample1)
    n2 = WorksheetFunction.Count(sample2)
    For Each v1 In sample1
        If WorksheetFunction.IsNumber(v1) Then
            u1 = u1 + WorksheetFunction.CountIf(sample2, Replace("<" & v1, ",", ".")) _
                    + WorksheetFunction.CountIf(sample2, Replace("=" & v1, ",", ".")) / 2
        End If
    Next v1
    MannWhitneyU = (u1 - n1 * n2 / 2) / Sqr(n1 * n2 * (n1 + n2 + 1) / 12)
End Function
...
Рейтинг: 0 / 0
07.10.2012, 12:53
    #37986956
Serge 007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekb...редактор постоянно пытается компилировать строки, показывая дурацкие диалоги с предложением открыть справку...Ежели длани кривые, неча на зеркало пенять :)
...
Рейтинг: 0 / 0
07.10.2012, 13:04
    #37986960
катастрофа
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekb,
>редактор постоянно пытается компилировать строки - быть такого в ВБА не может
Вы бы весь код показали, как Вы в эту функцию уходите
...
Рейтинг: 0 / 0
07.10.2012, 13:16
    #37986969
The_Prist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekbТеперь вопрос ) v1 - число с плавающей точкой. При его форматировании в качестве десятичного разделителя используется запятая, но функция CountIf понимает критерий только если десятичный разделитель - точка! Хак с Replace это нормальное решение или есть более правильное?

Ещё более удивительно то, что если сделать переменные n1 и n2 типа Integer, функция валится на вычислении (n1 * n2 * (n1 + n2 + 1). Как это возможно??? n1, n2 - меньше 50.Для VBA разделитель целой и дробной части - точка. Replace - нормальное решение.

А что, если переменные n1 и n2 меньше единицы? Тогда VBA при приведении их типу Integer вполне может округлить до нулей(как и положено). Что при Ваших условиях не может не вызвать ошибки.
...
Рейтинг: 0 / 0
07.10.2012, 13:19
    #37986970
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Serge 007,

а почему нельзя просто подсветить строку с ошибочным кодом (как это делается во всех остальных средах разработки), а не выводить дурацкое диалоговое окно? Хотя, наверное к этому можно привыкнуть... Возможно, после постоянного нажатия на Enter для закрытия диалога у меня действительно выпрямятся руки...
...
Рейтинг: 0 / 0
07.10.2012, 13:22
    #37986976
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
катастрофабыть такого в ВБА не можетНе знаю, что он делает, но называет это именно так.

Функцию вызываю с листа.
...
Рейтинг: 0 / 0
07.10.2012, 13:30
    #37986981
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
The_Prist,

А если я буду использовать эту функцию в английской локали, в которой запятая будет разделителем тысяч? Replace уже не прокатит.

Упростим функцию до такой:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
Function MannWhitneyU(sample1 As Range, sample2 As Range)
    Dim n1 As Integer
    Dim n2 As Integer
    n1 = WorksheetFunction.Count(sample1)
    n2 = WorksheetFunction.Count(sample2)
    MannWhitneyU = n1 * n2 * (n1 + n2 + 1)
End Function


Смотрим через отладку:
n1 = 27
n2 = 34

Функция выдает #ЗНАЧ!

Если заменить типы на Double, то 56916
...
Рейтинг: 0 / 0
07.10.2012, 13:32
    #37986984
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
[quot The_PristДля VBA разделитель целой и дробной части - точка.[/quot]Это вполне естественно, но почему тогда он форматирует числа с запятой, а не точкой??
...
Рейтинг: 0 / 0
07.10.2012, 13:42
    #37986991
Диклевич Александр
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
а какой возвращаемый тип у функции? почему не указан?
поэтому, т.к. используется тип Integer, то и результату присваивается этот тип, и, вероятно, происходит переполнение.
И вообще, вместо Integer в VBA надо использовать Long.
Ну и классика жанра, вверху модуля Option Explicit для надежности
вот так
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
Option Explicit    
    Function MannWhitneyU(ByVal sample1 As Range, ByVal sample2 As Range)  As Double
    Dim n1 As Long
    Dim n2 As Long
    n1 = WorksheetFunction.Count(sample1)
    n2 = WorksheetFunction.Count(sample2)
    MannWhitneyU = n1 * n2 * (n1 + n2 + 1)
End Function
...
Рейтинг: 0 / 0
07.10.2012, 13:48
    #37986997
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekbЭто же жуть какая-то, редактор постоянно пытается компилировать строки, показывая дурацкие диалоги с предложением открыть справку.Ares_ekbа почему нельзя просто подсветить строку с ошибочным кодом (как это делается во всех остальных средах разработки)Если сразу не впадать в панику и не начинать вопить на весь мир, можно успокоиться, расслабиться, зайти в Tools - Options - Editor и отключить галку Auto Syntax Check. Тогда неверные синтаксически строки будут просто подсвечиваться красным без сообщений.

ЗЫ: В любой новой для меня программе я прежде всего захожу в настройки и смотрю, что там есть.
...
Рейтинг: 0 / 0
07.10.2012, 13:49
    #37986999
Диклевич Александр
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekb1. Это же жуть какая-то, редактор постоянно пытается компилировать строки, показывая дурацкие диалоги с предложением открыть справку. 2. Сохраняет функцию секунд 5.

1. настраивается в Tools -> Options, дальше разберетесь
2. Сохраняется не функция, а вся рабочая книга, где эта функция находится. Соответсвенно, если данных там много, то и сохранение займет немало. Есть вариант вынести код в отдельную книгу с типом xla (Microsoft Excel add-in, так, по-моему, в версиях до 2003 включительно называется), потом эту книгу можно кинуть в автозвгрузку Excel и функция будет доступна во всех открытых книгах.
...
Рейтинг: 0 / 0
07.10.2012, 13:56
    #37987003
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Диклевич Александр, Shocker.Pro,

спасибо ) Я почему-то даже не задумывался о том, что в этой Вселенной Integer может быть 16-битным )))

Но форматирование чисел меня всё-равно смущает. Никак не пойму как отформатировать с точкой (
...
Рейтинг: 0 / 0
07.10.2012, 14:16
    #37987026
The_Prist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekbThe_Prist,

А если я буду использовать эту функцию в английской локали, в которой запятая будет разделителем тысяч? Replace уже не прокатит.То, немного подумав, можно определять разделитель внутри кода:
Код: vbnet
1.
2.
dim sDelim as string
sDelim = mid$(1/2,2,1)


и уже его заменять или нет на точку
...
Рейтинг: 0 / 0
07.10.2012, 14:28
    #37987036
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
The_PristТо, немного подумав, можно определять разделитель внутри кода:
Код: vbnet
1.
2.
dim sDelim as string
sDelim = mid$(1/2,2,1)

в ВБА можно и попроще:
Код: vbnet
1.
Application.DecimalSeparator
...
Рейтинг: 0 / 0
07.10.2012, 14:59
    #37987053
Диклевич Александр
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Shocker.Pro,

Код: vbnet
1.
Application.DecimalSeparator


это используемый в Excel, если UseSystemSeparators = False.
Но VBA всегда использует системный разделитель, который
Код: vbnet
1.
2.
dim sDelim as string
sDelim = mid$(1/2,2,1)


проверить, что они могут быть разные легко.
...
Рейтинг: 0 / 0
07.10.2012, 15:17
    #37987061
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Диклевич Александр, спасибо, буду знать
...
Рейтинг: 0 / 0
07.10.2012, 20:08
    #37987227
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
The_Prist,

спасибо! Хотя всё-равно такое решение выглядит очень странным...
...
Рейтинг: 0 / 0
07.10.2012, 20:29
    #37987233
mds_world
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Еще вариант получения системного разделителя дробной части
Код: vbnet
1.
2.
?format(0,".")
,
...
Рейтинг: 0 / 0
07.10.2012, 21:13
    #37987262
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Ares_ekbНикак не пойму как отформатировать с точкой (Функция Str(), например
Код: vbnet
1.
u1 = u1 + WorksheetFunction.CountIf(sample2, "<" & Str(v1))

Для положительных чисел Str возвращает строку с пробелом в начале, но в данном случае (и почти всегда) это не мешает.
...
Рейтинг: 0 / 0
07.10.2012, 21:22
    #37987270
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Казанский,

мдамс, всё оказалось так просто ) спасибо ))))
...
Рейтинг: 0 / 0
11.10.2012, 17:02
    #37994142
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
Нашел просто мега-вещь . Страшно представить сколько я писал бы это на VBA:

Код: c#
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
<DnaLibrary RuntimeVersion="v4.0" Name="Simple Statistics">
  <Project Language="CS">
    <Reference Path="MathNet.Numerics.dll" />
    <![CDATA[
using System;
using ExcelDna.Integration;
using MathNet.Numerics.LinearAlgebra.Double;

namespace SimpleStatistics
{
    public class LogisticRegression
    {
        public static object EstimateLogisticRegression(double[] dependent, double[,] predictor)
        {
            var n = dependent.Length;
            var dim = predictor.GetLength(1) + 1;
            var x = new DenseMatrix(n, 1, 1).Append(new DenseMatrix(predictor));
            var y = new DenseVector(dependent);
            var w = new DiagonalMatrix(n);
            var b = new DenseVector(dim);
            var b2 = new DenseVector(dim);
            var p = new DenseVector(n);
            for (int k = 0; k < 100; k++)
            {
                for (int i = 0; i < n; i++) p[i] = 1 / (1 + Math.Exp(-b * x.Row(i)));
                w.SetDiagonal(p.PointwiseMultiply(-p.Subtract(1)));
                var xtw = x.TransposeThisAndMultiply(w);
                b2 = (DenseVector)((xtw * x).Inverse() * xtw * (x * b + w.Inverse() * (y - p)));
                var precision = (b2 - b).AbsoluteMaximum();
                if (precision <= 0.000001) return (double[])b2;
                b = b2;
            }
            return ExcelError.ExcelErrorNA;
        }
    }
}
    ]]>
  </Project>
</DnaLibrary>
...
Рейтинг: 0 / 0
12.10.2012, 00:21
    #37994714
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
2Ares_ekbСтранно... А зачем Вам реализовывать уже имеющееся в аналитической геометрии (и векторной алгебре) с помощью именно VBA, если уже давно известно, что можно подключать любые необходимые (совместимые) библиотеки?
Впрочем, а причем здесь вообще VBA? - ведь не так давно был приведён пример по поводу 3D-моделирования и BlitzBASIC, скажем...
...
Рейтинг: 0 / 0
12.10.2012, 20:41
    #37996437
Ares_ekb
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Формат чисел Excel VBA
AndreTM,

чтобы совсем ничего не писать на VBA, придется подключить какой-нибудь статистический пакет... Про BlitzBASIC тему не нашел.

Чем больше разбираюсь с ExcelDNA, тем больше он мне нравится. Работает даже отладка в Visual Studio!

Единственная вещь, которую, похоже, не получится сделать - это сохранение результата функции в виде структуры в одной ячейке. Табличные формулы не очень подходят ;( Моя функция вычисляет несколько параметров. Приходится для каждого параметра вызывать её заново и считать то же самое несколько раз. Очень жалко, что Excel не позволяет хранить в ячейках структуры.
...
Рейтинг: 0 / 0
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Формат чисел Excel VBA / 23 сообщений из 23, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]