•         

- - - - -

Применение в ставках Excel and VBA для чайников


#1   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 08:42

Решил создать тему где можно выкладывать некоторые формулы, макросы, пользовательские функции итд итп. которые могут использоваться для
создания критериев, баз и экспертных оценок для ставок на спорт.
Поводом и послужила необходимость в помощи программистов---необходимо создать пользовательскую функцию на VBA для работы с окнами разной размерности.

Теперь попробую пояснить суть задания.

Допустим имеется база в виде записей в ячейках эксель запись например вот такая (последний столбец красный,но за ним допустим ещё идут какие-то ячейки и записи и он просто как рабочий будет рассматриваться для пояснения сути.)

West Brom Everton 1 1 1,54 1,62 1,07 1,00 1,02 1,52 2,54
Chelsea Manchester United 3 1 2,14 0,86 2,21 1,57 2,26 1,71 3,97
Swansea Tottenham 1 3 1,38 1,54 1,46 1,31 1,22 2,04 3,26
Arsenal Fulham 2 0 1,86 0,64 0,86 1,93 2,40 0,50 2,90
Crystal Palace Stoke City 1 0 0,80 1,40 0,92 1,92 1,03 1,18 2,21
Liverpool Aston Villa 2 2 2,14 0,57 1,36 1,29 1,87 0,69 2,56
Manchester City Cardiff 4 2 3,14 0,71 0,50 1,70 3,82 0,31 4,13
Norwich Hull City 1 0 1,36 1,14 0,70 1,90 1,87 0,69 2,56
Sunderland Southampton 2 2 1,00 1,46 0,93 0,93 0,66 1,15 1,82
West Ham Newcastle Utd 1 3 1,43 1,43 1,07 1,50 1,53 1,29 2,82
Aston Villa Arsenal 1 2 1,14 1,43 1,86 0,93 0,74 2,25 2,99 какие-то данные для гостя
Newcastle Utd Manchester City 0 2 1,29 1,43 1,71 1,50 1,34 2,15 3,50
Stoke City Liverpool 3 5 1,00 1,00 2,14 1,36 0,94 1,88 2,82
Cardiff West Ham 0 2 1,00 1,50 0,64 1,36 0,94 0,85 1,78
Everton Norwich 2 0 1,79 0,64 0,79 2,07 2,60 0,43 3,04
Fulham Sunderland 1 4 1,27 2,07 0,71 1,79 1,60 1,28 2,89
Hull City Chelsea 0 2 1,50 0,60 1,67 1,07 1,13 0,88 2,02
Manchester United Swansea 2 0 1,29 1,00 1,00 1,36 1,19 0,90 2,09
Southampton West Brom 1 0 1,43 1,29 1,07 1,64 1,60 1,21 2,81
Tottenham Crystal Palace 2 0 1,20 1,13 0,50 1,50 1,23 0,49 1,72
Arsenal Cardiff 2 0 2,00 0,71 0,56 1,67 2,30 0,34 2,64
Crystal Palace Norwich 1 1 0,78 1,44 0,79 2,07 1,13 0,98 2,11
Fulham West Ham 2 1 1,21 2,14 0,57 1,36 1,13 1,08 2,21
Liverpool Hull City 2 0 2,15 0,62 0,78 1,89 2,80 0,42 3,23
База поднимается снизу вверх (ну это у меня--всё через Ж....) а вообще обычно наоборот идёт сверху вниз....поэтому надо рассмотреть два варианта работы---и с базой где данные идут сверху и для случая когда они идут снизу.....

Теперь что должна делать функция----допустим нас интересуют параметры для Arsenal параметры которые используются для подсчёта чего либо
находятся в каком-то столбце--допустим в последнем и следующим за ним...... допустим в последнем домашние параметры, а в следующем гостевые.
тогда задав размер окна (например 5) мы должны для домашнего Арсенала получить 5 ниже расположенных точек из таблицы пока допустим для домашнего состояния. Для этого нам разумеется надо передать в пользовательскую функцию адрес ячейки Arsenal--для идинтификации команды, столбец где производится просмотр (по Арсеналу),размер окна и столбец из которого надо выбрать записи ...записи например можно записать в массив какой-то внутри функции а потом выдать в виде строки с разделителем например в виде "/" или можно будет прописать в функции что делать с этими числами --допустим просуммировать 5 последних точек (но это пользователь сам может дописать главное рыбу сделать для функции)

Теперь для нашего примера--- таким числом будет в последнем 2,64 столбце, а так же другие идущие ниже но в приведённом куске просто не показанные.
теперь если такая функция будет работать то очевидно мы передав в качестве параметра не первый столбец (где арсенал дома), второй--гостевой и
следующий за последним столбцом столбец подсчитаем и гостевые характеристики. И разумеется функция будет работать и для гостевой команды при соответствующем задании параметров передаваемых в функцию..... наверно ещё надо задать ячейку определяющую начало просмотра (или определять её автоматически по параметру Arsenal-ячейки что означает что передаваться будут и содержимое и адрес.....

#2   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 09:00

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

#3   Noname

    Специалист


  • mp
  • 2445
349

10 February 2014 - 11:44

Bambuk, ты, главное, не нервничай :)
"...какие-то данные для гостя..." - это круто :D :D :D

#4   Noname

    Специалист


  • mp
  • 2445
349

10 February 2014 - 11:56

P.S. Если ты ищешь програмера, то твое ТЗ обойдется тебе в $1000, минимум

#5   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 12:24

P.S. Если ты ищешь програмера, то твое ТЗ обойдется тебе в $1000, минимум
Да мне просто влом терять время и разбираться во всех этих циклах и прочей хрени...тут работы на час я думаю. А потом такая функция она полезна будет не только мне любимому.
На крайняк я сам навояю что-то корявое но работающее...а для спеца я не вижу тут вообще проблем---надо только ещё в цикле учесть что там ниже вообще может команд не быть с таким названием и ограничить спуск(или подъём) каким-то числом итераций (допустим смотреть не более 5*20 ячеек где 5 --размер окна )....

#6   denvb

    Специалист


  • Участник II
  • 382
90

10 February 2014 - 16:31

один оффтоп... Bambuk, без обид, но тут или $1000 програмеру или отсыпь, что сам куришь )))
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках

#7   vit2811

    Специалист


  • Участник II
  • 167
8

10 February 2014 - 18:28

Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)

#8   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 19:22

Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)
Если Вам надо потратить неделю для понимания поста номер 2 то я собственно не знаю о чём тогда вообще говорить? Надо подсчитать сколько например забила команда в пяти домашних матчах...для этого надо вытащить из соответствующего столбца её голы...чё тут понимать-то? А если Вы захотите не пять а 25 то Вам в функцию и надо это окно передать.
А если допустим в ячейке нет данных или ошибка...то это исключение то же наверно надо обработать..ну я не программист....но вариантов тут наверно много как такую функцию прописать. В функцию мы передаём адрес ячейки где команда, размер окна и столбец-сателлит(можно наверно диапазон какой-то) из которого данные извлекаются в массив внутри функции или в коллекцию или ХЗ куда там ещё можно... число этих данных и определяется размером окна. потом там уже проще прописать что делать с этими данными.

можно например привязаться к адресу ячейки команды и спускаться вниз (или вверх смотря как база организована) потом проверять ниже расположенную ячейку на предмет совпадения с название ком. (Арсенал) если совпало берём туже строку но столбец-сателлит и записываем в массив первое значение...далее присваиваем какой-то переменной А
значение А+1 (изначально обнулив А перед циклом) потом это А используем для выхода из цикла по достижению размера окна а второй параметр выхода--число всех итераций (это если допустим нет данных по команде долго и она может быть новой вошедшей в лигу итд итп...)

#9   бостон

    Специалист


  • Участник II
  • 830
121
  • МестоположениеКрым, Алушта

10 February 2014 - 19:32

Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)

Замечательный напиток текила: выпил - почувствовал себя кактусом. А если выпить напитка из бамбука, то почувствуешь себя Бамбуком и все сразу понятно... Но ненадолго, увы.... :)

#10   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 19:43

Замечательный напиток текила: выпил - почувствовал себя кактусом. А если выпить напитка из бамбука, то почувствуешь себя Бамбуком и все сразу понятно... Но ненадолго, увы.... :)
Особенно если не знаком с функциями типа СУММЕСЛИ СЧЁТЕСЛИ итд итп.... ну всё ж по аналогии работает, но эта функция будет более актуальная можно назвать БАМБУКЕСЛИ .....

#11   ingener

    Специалист


  • Участник II
  • 530
62
  • МестоположениеКрым, Симферополь

10 February 2014 - 21:26

Бамбук, а может для Ваших целей лучше подойдет работа с какой-либо базой данных и с языком запросов к этой базе? Я в этом не шарю, но тут на форуме есть люди, работающие так. Может они лучше подскажут.

#12   Bambuk

    Специалист


  • mp
  • 6322
602

10 February 2014 - 22:55

Бамбук, а может для Ваших целей лучше подойдет работа с какой-либо базой данных и с языком запросов к этой базе? Я в этом не шарю, но тут на форуме есть люди, работающие так. Может они лучше подскажут.
Так придётся учиться как и что запрашивать и потом надо какую-то свою базу. Тут вот в сраном экселе в трёх соснах заблуждаешься, а в запросах там вообще, наверно, без пол-литра не разрулишь. Да я тут книжку скачал по VBA 200 листов уже прочитал так ни ХУ.. не могу понять с чего начать.... осталось ещё 740 листов прочитать и "золотой ключик у нас в кармане"....и вот тогда я точно скажу--"курить я буду, но пить не брошу".....

#13   ДИМА КЛЮКВИН

    Специалист


  • Участник II
  • 391
20
  • МестоположениеNIGERIA

11 February 2014 - 00:50

один оффтоп... Bambuk, без обид, но тут или $1000 програмеру или отсыпь, что сам куришь )))
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках
Не могли бы Вы мне помочь?
в екселе у меня лично собранная (только начал) вручную база, ряд колонок в которые вписываю разные цифры-полный бред!
мне нужно, чтобы при выборе фильтра, фильтр выбирал разные значения из разных колонок, но не брал те которые уже встречались ранее...
что-то примерно так
не могу разобраться в функциях и написать формулу вычисления....
если бы Вы мне смогли помочь, я бы Вам в личку или на мыло прислал свою рукопись, а Вы бы посмотрели

#14   denvb

    Специалист


  • Участник II
  • 382
90

11 February 2014 - 08:49

Не могли бы Вы мне помочь?
в екселе
я писал, что могу помочь с документами гугла, там используется javascript на котором легко и просто можно творить чудеса - и сайты парсить и обрабатывать как угодно. просто я не дружу с бэйсиком, да и экселя у меня нет

#15   ДИМА КЛЮКВИН

    Специалист


  • Участник II
  • 391
20
  • МестоположениеNIGERIA

11 February 2014 - 12:08


я писал, что могу помочь с документами гугла, там используется javascript на котором легко и просто можно творить чудеса - и сайты парсить и обрабатывать как угодно. просто я не дружу с бэйсиком, да и экселя у меня нет
Парсить сайты очень хорошая функция (я делаю -это вручную медленно, долго и очень муторно(((((()
Мне очень нужна -эта функция
Обрабатывать.... Опять всё делаю вручную
Эксель есть, но делаю всё вручную, не знаю как его использовать на все 100%, а про javascript вообще даже не слышал (((
Если не очень трудно для Вас, не могли бы Вы...
Очень хочется всё систематизировать и автоматизировать по заданным алгоритмам


#16   Bambuk

    Специалист


  • mp
  • 6322
602

11 February 2014 - 16:37

Функция производит суммирование данных расположенных ниже ячейки J8 при совпадении названия команды записанной в H8 в ячейках ниже H8.

Function Bambuk(ByRef rah1 As Range, ByRef rah2 As Range, n As Long, m As Long, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Set COMANDA = rah1
Set DIAPAZS = rah2

Dim SUMMA As Variant
Dim T As Long
Dim P As Long
T = 1
P = 1

Do Until P > n Or T > m
T = T + 1
If COMANDA.Cells(1, 1) = COMANDA.Cells(T, 1) Then P = P + 1
If COMANDA.Cells(1, 1) = COMANDA.Cells(T, 1) Then SUMMA = SUMMA + DIAPAZS.Cells(T, 1)

Loop

If P < n + 1 Then Bambuk = "NET_DAN"
If P = n + 1 Then Bambuk = SUMMA

End Function

=Bambuk(H8;J8;5;100)

H8-область команд
J8-область суммирования
5-число последних игр
100- размер просматриваемой области (брать более 20*n в примере n=5)

#17   Bambuk

    Специалист


  • mp
  • 6322
602

11 February 2014 - 17:00

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

#18   Вулкан

    Специалист


  • Участник II
  • 424
12

11 February 2014 - 19:18

Функция производит суммирование данных расположенных ниже ячейки J8 при совпадении названия команды записанной в H8 в ячейках ниже H8.

Function Bambuk(ByRef rah1 As Range, ByRef rah2 As Range, n As Long, m As Long, Optional VolatileOn As Boolean = True) As Variant
....

=Bambuk(H8;J8;5;100)

H8-область команд
J8-область суммирования
n = 5-число последних игр
m = 100- размер просматриваемой области (брать более 20*n в примере n=5)
для начала пойдет, но когда расширишь знания по VBA, поймешь что нужен более гибкое приложение, чем Ексель.
Лучше чем Ексель иметь Аксесс.
Если у тебя установлен Аксесс (MS Access2003 и выше), то в нем намного удобнее, чем в Екселе, создавать таблицы, а затем пополнять их данными, сортировать , выбирать, фильтровать данные, а потом создавать постепенно формы для ввода новых данных и отчеты (выборки по условиям в т. числе с программированием (постепенно по мере усвоения усложняя программный код) фильтров и условий отбора из разных таблиц). По возможности помогу освоить основные приемы создания рациональных баз данных, да в пакете должны быть учебные БД с элементарными программами в открытом коде на VBA. Я с этого начинал (самоучка с 10-летним стажем - занимался вечерами, выкраивая свободные минуты.).

#19   Bambuk

    Специалист


  • mp
  • 6322
602

12 February 2014 - 13:22

Добавим ещё одну функцию более удобную для некоторых подсчётов.

Function Bambs(ByRef rah1 As Range, ByRef rah2 As Range, n As Long, m As Long, ByRef rah3 As Range, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Set COMANDA = rah1
Set DIAPAZS = rah2
Set PROSMCOM = rah3
Dim SUMMA As Variant
Dim T As Long
Dim P As Long
T = 1
P = 1

Do Until P > n Or T > m
T = T + 1
If COMANDA.Cells(1, 1) = PROSMCOM.Cells(T, 1) Then P = P + 1
If COMANDA.Cells(1, 1) = PROSMCOM.Cells(T, 1) Then SUMMA = SUMMA + DIAPAZS.Cells(T, 1)

Loop

If P < n + 1 Then Bambs = "NET_DAN"
If P = n + 1 Then Bambs = SUMMA

End Function
=Bambs(H8;J8;5;100;K8)
H8-ячейка с назван. Команды (или иная запись)
J8-область суммирования
5-число последних игр
100- размер просматриваемой области (брать более 20*n в примере n=5)
K8—начальная ячейка в которой ищется совпадение с назван. команды.
ПРИМЕЧАНИЕ
Функции (и ф. Бамбук тоже)на самом деле имеют ещё один параметр и могут записываться в виде
=Bambs(H8;J8;5;100;K8;1)—по умолчанию

ИЛИ

=Bambs(H8;J8;5;100;K8;0)

Для чего это сделано—дело в том что при большом объёме записей работа будет сильно тормозиться
И можно ввести какую-то ячейку на листе например E20 и там указывать режим пересчёта (1) и не пересчитывать данные (0 ) это фактически параметр истина- ложь а ф формуле прописать ячейку в виде $E$20, тогда при протаскивании формулы по ячейкам эта ячейка останется без изменений и можно управлять режимом всех формул с использованием данных функций (хотите сделайте переключатель а не в ручную вводите 1 и 0)

Будет так примерно =Bambs(H8;J8;5;100;K8;$E$20)

=Bambuk(H8;J8;5;100;$E$20)

#20   Bambuk

    Специалист


  • mp
  • 6322
602

12 February 2014 - 13:56

Вот так наверно понятнее будет K8—начальная ячейка диапазона в котором ищется совпадение с назван. команды.