14
Применение в ставках Excel and VBA для чайников
Bambuk, Feb 10 2014 08:42
#1
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-ячейки что означает что передаваться будут и содержимое и адрес.....
создания критериев, баз и экспертных оценок для ставок на спорт.
Поводом и послужила необходимость в помощи программистов---необходимо создать пользовательскую функцию на 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
10 February 2014 - 09:00
Возможно для лучшего понимания сути можно взять два первых столбца где счёт матча и допустим нам надо подсчитать как-то сколько забил Арсенал дома и в гостях за пять последних игр (начинаем считать вниз в примере с момента выделенного синим цветом Арсенала не считая этой записи так как счёт мы ещё не знаем)...
тут мы два раза должны использовать функцию записав её в разные ячейки и передав разумеется разные параметры в её тело...
тут мы два раза должны использовать функцию записав её в разные ячейки и передав разумеется разные параметры в её тело...
#3
10 February 2014 - 11:44
Bambuk, ты, главное, не нервничай
"...какие-то данные для гостя..." - это круто
"...какие-то данные для гостя..." - это круто
#4
10 February 2014 - 11:56
P.S. Если ты ищешь програмера, то твое ТЗ обойдется тебе в $1000, минимум
#5
10 February 2014 - 12:24
P.S. Если ты ищешь програмера, то твое ТЗ обойдется тебе в $1000, минимум
На крайняк я сам навояю что-то корявое но работающее...а для спеца я не вижу тут вообще проблем---надо только ещё в цикле учесть что там ниже вообще может команд не быть с таким названием и ограничить спуск(или подъём) каким-то числом итераций (допустим смотреть не более 5*20 ячеек где 5 --размер окна )....
#6
10 February 2014 - 16:31
один оффтоп... Bambuk, без обид, но тут или $1000 програмеру или отсыпь, что сам куришь )))
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках
#7
10 February 2014 - 18:28
Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)
#8
10 February 2014 - 19:22
Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)
А если допустим в ячейке нет данных или ошибка...то это исключение то же наверно надо обработать..ну я не программист....но вариантов тут наверно много как такую функцию прописать. В функцию мы передаём адрес ячейки где команда, размер окна и столбец-сателлит(можно наверно диапазон какой-то) из которого данные извлекаются в массив внутри функции или в коллекцию или ХЗ куда там ещё можно... число этих данных и определяется размером окна. потом там уже проще прописать что делать с этими данными.
можно например привязаться к адресу ячейки команды и спускаться вниз (или вверх смотря как база организована) потом проверять ниже расположенную ячейку на предмет совпадения с название ком. (Арсенал) если совпало берём туже строку но столбец-сателлит и записываем в массив первое значение...далее присваиваем какой-то переменной А
значение А+1 (изначально обнулив А перед циклом) потом это А используем для выхода из цикла по достижению размера окна а второй параметр выхода--число всех итераций (это если допустим нет данных по команде долго и она может быть новой вошедшей в лигу итд итп...)
#9
10 February 2014 - 19:32
Работу тут можно наверное и за час сделать, но чтобы понять, что хочет автор темы, думаю нужно минимум неделю потратить)
Замечательный напиток текила: выпил - почувствовал себя кактусом. А если выпить напитка из бамбука, то почувствуешь себя Бамбуком и все сразу понятно... Но ненадолго, увы....
#10
10 February 2014 - 19:43
Замечательный напиток текила: выпил - почувствовал себя кактусом. А если выпить напитка из бамбука, то почувствуешь себя Бамбуком и все сразу понятно... Но ненадолго, увы....
#11
10 February 2014 - 21:26
Бамбук, а может для Ваших целей лучше подойдет работа с какой-либо базой данных и с языком запросов к этой базе? Я в этом не шарю, но тут на форуме есть люди, работающие так. Может они лучше подскажут.
#12
10 February 2014 - 22:55
Бамбук, а может для Ваших целей лучше подойдет работа с какой-либо базой данных и с языком запросов к этой базе? Я в этом не шарю, но тут на форуме есть люди, работающие так. Может они лучше подскажут.
#13
11 February 2014 - 00:50
один оффтоп... Bambuk, без обид, но тут или $1000 програмеру или отсыпь, что сам куришь )))
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках
я в VBA не полезу, но, если захотите, помогу с любым алгоритмом в гуглодоках
в екселе у меня лично собранная (только начал) вручную база, ряд колонок в которые вписываю разные цифры-полный бред!
мне нужно, чтобы при выборе фильтра, фильтр выбирал разные значения из разных колонок, но не брал те которые уже встречались ранее...
что-то примерно так
не могу разобраться в функциях и написать формулу вычисления....
если бы Вы мне смогли помочь, я бы Вам в личку или на мыло прислал свою рукопись, а Вы бы посмотрели
#15
11 February 2014 - 12:08
я писал, что могу помочь с документами гугла, там используется javascript на котором легко и просто можно творить чудеса - и сайты парсить и обрабатывать как угодно. просто я не дружу с бэйсиком, да и экселя у меня нет
Мне очень нужна -эта функция
Обрабатывать.... Опять всё делаю вручную
Эксель есть, но делаю всё вручную, не знаю как его использовать на все 100%, а про javascript вообще даже не слышал (((
Если не очень трудно для Вас, не могли бы Вы...
Очень хочется всё систематизировать и автоматизировать по заданным алгоритмам
#16
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)
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
11 February 2014 - 17:00
Примечание.
для работы с функцией записи должны идти снизу вверх--то есть верхние записи самые свежие.
для работы с функцией записи должны идти снизу вверх--то есть верхние записи самые свежие.
#18
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)
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)
Лучше чем Ексель иметь Аксесс.
Если у тебя установлен Аксесс (MS Access2003 и выше), то в нем намного удобнее, чем в Екселе, создавать таблицы, а затем пополнять их данными, сортировать , выбирать, фильтровать данные, а потом создавать постепенно формы для ввода новых данных и отчеты (выборки по условиям в т. числе с программированием (постепенно по мере усвоения усложняя программный код) фильтров и условий отбора из разных таблиц). По возможности помогу освоить основные приемы создания рациональных баз данных, да в пакете должны быть учебные БД с элементарными программами в открытом коде на VBA. Я с этого начинал (самоучка с 10-летним стажем - занимался вечерами, выкраивая свободные минуты.).
#19
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)
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
12 February 2014 - 13:56
Вот так наверно понятнее будет K8—начальная ячейка диапазона в котором ищется совпадение с назван. команды.