Excel + football-data.co.uk
#1 OFFLINE
Добавлено 17 March 2015 - 20:25
Кто-нибудь пробовал работать в екселе с данными с football-data.co.uk?
Интересует такой момент: как можно посмотреть в сводной таблице результаты игры одной команды так, чтобы не делить их на домашние и выездные матчи? Так, чтобы выбрав, например, Arsenal, видить подряд его домашние и выездные матчи.
#2 OFFLINE
Добавлено 17 March 2015 - 22:26
Самый колхозный вариант примерно такой, если руками делать--берёте таблицу и копируете её ниже (то есть получим одно и тоже таб под таб) потом для каждой таб делаете таб с заголовками чтоб фильтровать можно было, потом в одной таб. выбираете галочкой Арсенал дом а во второй Арсенал гость..если так трудновато обозревать тогда копируете всю эту хрень и вставляете ещё ниже, а потом сортируете по датам и получаете то чё вам надо...а если программой, то вот нечто похожее но просто вам тогда можно всё из одной таблицы брать поочерёдно выбирая Арсенал там и сям (дом и потом гость)....итд...
Отредактировано Bambuk, 17 March 2015 - 22:26.
#3 OFFLINE
Добавлено 17 March 2015 - 23:07
Bambuk, on 17 March 2015 - 22:26, сказал:
Самый колхозный вариант примерно такой, ...
#4 OFFLINE
Добавлено 18 March 2015 - 00:17
#5 OFFLINE
Добавлено 18 March 2015 - 07:30
Betamin, on 18 March 2015 - 00:17, сказал:
А у вас, скорее всего, на эти вопросы пока ответов нет )
Отредактировано tester.nt, 18 March 2015 - 07:35.
#6 OFFLINE
Добавлено 18 March 2015 - 08:01
tester.nt, on 17 March 2015 - 23:07, сказал:
В том виде как таблицы приведены в файлах вы универсально задачу не разрулите--потому что завтра вы скажите,-- что вам и раздельно надо смотреть данные и дом и гость итд итп.
поэтому надо что-то добавлять и формулами или программами разруливать. Вы же должны как-то собрать данные по разным столбикам...причём я подозреваю что Вы хотите сразу это осуществить для всех команд. Тогда программе надо ж как-то понять чё от неё хотят---можно например сделать столбик объединяя команды ...например создавая запись вида
А1&"*"&A2 тогда записи будут типа Челси*Арсенал или Арсенал*Бамбук в каком-то новом столбике и тогда уже можно допустим по вхождению в строковую переменную другой переменной--- Арсенал
распознать запись (но эту конкатенцию можно и например делать внутри модуля или функции ексель итд....
Отредактировано Bambuk, 18 March 2015 - 08:03.
#7 OFFLINE
Добавлено 18 March 2015 - 09:08
Bambuk, on 18 March 2015 - 08:01, сказал:
В том виде как таблицы приведены в файлах вы универсально задачу не разрулите--потому что завтра вы скажите,-- что вам и раздельно надо смотреть данные и дом и гость итд итп.
поэтому надо что-то добавлять и формулами или программами разруливать. Вы же должны как-то собрать данные по разным столбикам...причём я подозреваю что Вы хотите сразу это осуществить для всех команд. Тогда программе надо ж как-то понять чё от неё хотят---можно например сделать столбик объединяя команды ...например создавая запись вида
А1&"*"&A2 тогда записи будут типа Челси*Арсенал или Арсенал*Бамбук в каком-то новом столбике и тогда уже можно допустим по вхождению в строковую переменную другой переменной--- Арсенал
распознать запись (но эту конкатенцию можно и например делать внутри модуля или функции ексель итд....
Конечно, надо смотреть данные и вместе и раздельно. Конечно, надо смотреть для всех команд. И также надо собирать данные по всем полям, относящимся к выбранной команде. Объединение двух столбцов - не вариант, т.к. тогда мы не поймем, какие поля относятся к какой команде.
Как вариант, решение может быть в изменении структуры данных. Но пока не знаю как.
#8 OFFLINE
Добавлено 18 March 2015 - 10:12
tester.nt, on 18 March 2015 - 09:08, сказал:
и получите вот такое (привожу часть таблицы так как не влезет полностью)
Div Date HomeTeam AwayTeam FTHG FTAG FTR HTHG HTAG HTR Referee HS
E0 17.08.2013 Arsenal Aston Villa 1 3 A 1 1 D A Taylor 16
E0 24.08.2013 Fulham Arsenal 1 3 A 0 2 A H Webb 16
E0 01.09.2013 Arsenal Tottenham 1 0 H 1 0 H M Oliver 12
E0 14.09.2013 Sunderland Arsenal 1 3 A 0 1 A M Atkinson 17
E0 22.09.2013 Arsenal Stoke 3 1 H 2 1 H M Dean 16
E0 28.09.2013 Swansea Arsenal 1 2 A 0 0 D M Clattenburg 10
E0 06.10.2013 West Brom Arsenal 1 1 D 1 0 H L Mason 12
E0 19.10.2013 Arsenal Norwich 4 1 H 1 0 H L Probert 20
E0 26.10.2013 Crystal Palace Arsenal 0 2 A 0 0 D C Foy 13
E0 02.11.2013 Arsenal Liverpool 2 0 H 1 0 H M Atkinson 12
E0 10.11.2013 Man United Arsenal 1 0 H 1 0 H M Oliver 5
E0 23.11.2013 Arsenal Southampton 2 0 H 1 0 H M Clattenburg 9
E0 30.11.2013 Cardiff Arsenal 0 3 A 0 1 A L Mason 10
E0 04.12.2013 Arsenal Hull 2 0 H 1 0 H A Marriner 20
E0 08.12.2013 Arsenal Everton 1 1 D 0 0 D H Webb 11
E0 14.12.2013 Man City Arsenal 6 3 H 2 1 H M Atkinson 22
E0 23.12.2013 Arsenal Chelsea 0 0 D 0 0 D M Dean 7
#9 OFFLINE
Добавлено 18 March 2015 - 10:44
#10 OFFLINE
Добавлено 18 March 2015 - 11:36
Bambuk, on 18 March 2015 - 10:12, сказал:
Я прихожу к выводу, что структура данных должна как-то напоминать ту, которая используется в реляционных БД (т.е. несколько отдельных таблиц на команды, на чемпионаты, и еще на что-то). Тогда, мы сможем сделать выборку по ИД команды и получить все ее матчи. Здесь как вариант загнать эти данные, например, в Access, а потом подключить эту БД к Excel и уже работать с данными. Или другой вариант - менять структуру исходной таблицы так, чтобы на выходе у нас суммировались показатели команд. Можно даже делать другую таблицу именно под анализ показателей команд без привязки "Дом"/"Выезд". Как-то так.
#11 OFFLINE
Добавлено 18 March 2015 - 13:46
Если что-то универсальное Вы пытаетесь делать, то ексель скорее не совсем подходит...а тогда возможно лучше перенести данные в БД и там использовать язык запросов....но там наверно тоже придётся подумать об организации связей и как оптимально эту базу сделать.
Вам ни чего глядеть не надо---надо в таблицу добить то, что Вы считаете нужным в качестве критериев--тогда вот этих проблем не будет возникать...а останется только накладывать ограничения на ваши данные. Это можно делать или макросами или встроенными функциями и рассчитанные данные потом перезаносить в таблицу как значения иначе ексель начнёт сильно тормозить (так как будет пересчитывать лист например). А завтра вам может тоталы понадобятся или данные по минутам забитых и пропущенных? Так и добивайте всё это в таблицу в свои столбцы. Тогда Вы убиваете сразу двух зайцев--потому что послезавтра Вы возможно захотите загрузить данные в статистические проги или нейросетевые пакеты и Вам данные придётся готовить именно похожим образом, а так они уже формально готовы (например можно взять часть столбцов и загрузить в текстовый файл с которыми многие другие проги могут общаться)..так зачем создавать лишние заморочки по просмотру того или этого?.... (сразу берём и заносим напротив пары то чё считаем целесообразным для анализа).
#12 OFFLINE
Добавлено 18 March 2015 - 15:03
tester.nt, on 18 March 2015 - 07:30, сказал:
А у вас, скорее всего, на эти вопросы пока ответов нет )
Я тему веду в гандикаперах - я там все расчеты делаю в Google Sheets, который как я понимаю сделан по образу и подобию Excel.
Вот вам "выбрав, например, Arsenal, видить подряд его домашние и выездные матчи." (в Arsenal!A4 можно посмотреть формулу):
https://docs.google....dit?usp=sharing
Если использовать гугловскую QUERY (возможно и в Excel есть аналог), то можно вообще делать SQL-like выборки.
#13 OFFLINE
Добавлено 18 March 2015 - 15:19
Bambuk, on 18 March 2015 - 13:46, сказал:
Bambuk, on 18 March 2015 - 13:46, сказал:
Bambuk, on 18 March 2015 - 13:46, сказал:
Bambuk, on 18 March 2015 - 13:46, сказал:
#14 OFFLINE
Добавлено 18 March 2015 - 15:39
Betamin, on 18 March 2015 - 15:03, сказал:
Вот вам "выбрав, например, Arsenal, видить подряд его домашние и выездные матчи." (в Arsenal!A4 можно посмотреть формулу):
https://docs.google....dit?usp=sharing
Если использовать гугловскую QUERY (возможно и в Excel есть аналог), то можно вообще делать SQL-like выборки.
#15 OFFLINE
Добавлено 18 March 2015 - 15:50
tester.nt, on 18 March 2015 - 15:39, сказал:
Ну не знаю... есть всякие sumifs, averageifs и т.д. можно делать через промежуточные листы, можно через QUERY. Вообщем, я считаю, что сделать там можно все.
добавлю - кроме какой-то очень сложной математики. бесселевские функции я считал скриптом на python
#16 OFFLINE
Добавлено 18 March 2015 - 16:08
Zaragoza_Lugo_0_1
Tenerife_Lugo_0_1
Alaves_Lugo_2_1
Numancia_Lugo_0_1
Cordoba_Lugo_1_1
Lugo_Recreativo_2_0
Lugo_Mallorca_2_1
Murcia_Lugo_3_1
Lugo_La Coruna_2_2
Hercules_Lugo_1_1
=C14&"_"&D14&"_"&E14&"_"&F14
Допустим хочу посмотреть все игры где Lugo дома забила 2 мяча а в гостях 1
В пользовательском фильтре указываю «начинается с» Lugo_*_2_
ИЛИ
«заканчивается на» _Lugo_*_1
#17 OFFLINE
Добавлено 18 March 2015 - 16:18
Bambuk, on 18 March 2015 - 16:08, сказал:
Zaragoza_Lugo_0_1
Tenerife_Lugo_0_1
Alaves_Lugo_2_1
Numancia_Lugo_0_1
Cordoba_Lugo_1_1
Lugo_Recreativo_2_0
Lugo_Mallorca_2_1
Murcia_Lugo_3_1
Lugo_La Coruna_2_2
Hercules_Lugo_1_1
=C14&"_"&D14&"_"&E14&"_"&F14
Допустим хочу посмотреть все игры где Lugo дома забила 2 мяча а в гостях 1
В пользовательском фильтре указываю «начинается с» Lugo_*_2_
ИЛИ
«заканчивается на» _Lugo_*_1
А если показателей 50? Для каждой команды. И в процессе исследования нам надо постоянно менять как их набор, так и фильтруемые значения.
Пока единственный рабочий вариант, который мне предложили:
- вместо полей @HomeTeam и @AwayTeam сделать поля @Team1/@Team2 или @Team/@Opponent
- добавить поле @Field со значениями Home/Away
- дублировать все записи
- поменять местами команды
- поменять местами все показатели: FTHG<->FTAG, HS<->AS, ...
- вместо значений поля H/D/A поля @FTR высчитать значения W/D/L
На рисунках привожу простой пример, как должно получиться на выходе.
Только там, конечно, расчет идет только для домашнего поля. А надо и для выездного тоже) Ну и в реальности слайсеров и критериев намного больше.
Screen Shot 2015-03-18 at 17.01.02.png 97.15K 8 Количество загрузок
Screen Shot 2015-03-18 at 17.03.27.png 89.18K 7 Количество загрузок
Отредактировано tester.nt, 18 March 2015 - 16:26.
#18 OFFLINE
Добавлено 18 March 2015 - 18:41
"Челсигость" вот если мы их сольём вместе, то что мы собственно выясним и в чём выиграем в плане анализа? Ну можно исследовать допустим абстракции слитых вместе Арсеналдом и Челсигость что по смыслу примерно тоже самое как ЧелсидомЧелсигость для некоторых характеристик команд (но не для всех конечно).
Если показателей хоть 250 и они вбиты в строку напротив пары, то в чём собственно могут возникнуть проблемы? Берите и накладывайте ограничения на эти показатели.
Если Вам конечно так видится и так удобнее то это Ваш выбор, но тогда вот допустим мы заносим в записи расклад по голам-минутам...и что их тоже что ли инвертировать и заменять 0-1 65мин 1-1 75мин 2-1 90 мин на 1-0 1-1 1-2 ? Мне кажется надо ещё что-то подумать наверно.... и потом исследовать можно конечно только если критериев 200 штук то перебрать их даже тройками допустим в 7-10 диапазонах руками весьма проблематично.
Допустим 50 характеристик пары если брать их тройками то 1960 вариантов если 4-ками то 230300
при этом если имеем 5 градаций (уровней критерия) то для троек это 243 варианта, а для троек с тремя градациями 27
даже по скромному 27*1960 ---почти 53 тысячи вариантов---которые как я подозреваю вы предлагаете тыкать руками....поэтому я не издеваюсь, а просто исходя из упомянутых трудностей считаю рациональным писать характеристики напротив пары и тогда можно куда-то экспортировать данные для некоторой предварительной(а возможно и окончательной) обработки или писать какие-то вспомогательные программы в екселе например, которым проще указать столбец(цы) который уже вычислен и имеется готовая характеристика...иначе могут возникнуть трудности из-за обилия возможных вариантов.....
Вот смотрите--допустим нам нужны такие характеристики для дома: среднее по заб. дома//среднее по проп. дома// среднее по заб по общим дан. //и пропущенных по общим дан.
и для гостевой по аналогии. Теперь допустим эти данные Вы просто вычисляете посредством простановки галочек-палочек каких-то...ну а дальше-то что? ну вот смотрите Вы на эти циферы и суть решения на чём тогда надо основывать? или смотрим графики банков по 1Х2 с такой-то точки для домашних игр домашней и гостевых для гостевой и допустим у дом.ком. рост и +++ в П1 а у гостя мы в минусе...--какое должно быть решение? Вот на что надо ставить? ну не ответите Вы на сии вопросы.....
Вам же для всех команд надо получить данные а потом что-то там смотреть--я так думаю...но может оно конечно надо как-то иначе делать? Или я просто не совсем понял вашу методу...
#19 OFFLINE
Добавлено 18 March 2015 - 21:51
Если Вы мне покажете как именно используя Ваш подход можно получить возможности сводных таблиц, как по возможностям (высчитывание сум, средних, замена как исходных, так и рассчитываемого значения, автоматическое построение графика по сводной таблице) так и по удобству пользования (все действия делаются в пару движений мышкой и у нас уже совсем другая картина), то я приму Ваш ответ. А
По поводу большого кол-ва критериев.
Здесь понятно, что нам исследовать все со всеми не надо. Поэтому те цифры, которые Вы привели математически, конечно, верные, но на практике зависимостей намного меньше. В основном на выходе меня интересует кол-во забитых и пропущенных голов, результат матча или баланс банка на некотором отрезке времени.
По поводу решений.
Это уже совсем другая тема. Каждый принимает решения опираясь на те данные, которые, как он считает, влияют на результат матча. Например, на состоянии команды перед матчем, на кол-ве забитых/пропущенных голов, на месте в турнирной таблице, на форме команды, на ее рейтинге. А можно придумать какой-то интегральный показатель, который будет рассчитываться на основе вышеприведенных характеристик.
Но, все же, у меня есть конкретная задача, целесообразность которой для меня понятна. И по-моему, это Вы склоняете ситуацию к усложнению, наводя десяток аргументов, почему этого делать не надо. Если не знаете, как сделать - так и скажите.
#20 OFFLINE
Добавлено 19 March 2015 - 13:59
вот маленький кусок таблицы--как пример. Тут просто все критерии и прочие данные уже стоят напротив пары. Это ни коем образом не мешает Вам создавать сводные таблицы (их же можно по идее и несколько сделать из каких-то соображений)
КОМ1 КОМ2 СЧ1 СЧ2 З1 П1 З2 П2 Г1 Г2 ОЧ12 V1 D1 L1 V2 D2 L2
Cardiff Aston Villa 0 0 1 1,5 1,357142857 1,285714286 0,857071506 1,727072047 -0,24 0,333333333 0,25 0,416666667 0,357142857 0,357142857 0,285714286
West Ham Norwich 2 0 1,357142857 1,5 0,846153846 2,153846154 1,927528825 1,066412619 0 0,285714286 0,285714286 0,428571429 0,230769231 0,153846154 0,615384615
Sunderland Norwich 0 0 1,066666667 1,533333333 0,785714286 2,357142857 1,672371996 1,096639479 -0,5625 0,2 0,266666667 0,533333333 0,214285714 0,071428571 0,714285714
Вот пример встроенной пользовательской функции когда данные идут снизу вверх(верхние-более свежие) можно делать по аналогии для данных идущих сверху вниз.
Функция производит суммирование данных расположенных ниже ячейки 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) и протаскиваем...все данные таб. готовы
K8—начальная ячейка диапазона в котором ищется совпадение с назван. команды. J8-область суммирования
5-число последних игр
100- размер просматриваемой области (брать более 20*n в примере n=5)
По идее можно вообще сделать макрос который будет готовить таблицу перед созданием сводной таб., добивая нужными данными (которые считаем нужными).
Если допустим исходить из того что мы хотим облегчить предельно себе жизнь и просто забрав данные с сайта простыми манипуляциями что-то посмотреть...то это наверно не самы лучший вариант---так как какие-то данные всё равно лучше рассчитать и занести. Например Вам могут потребоваться коэффициенты корреляции(которые без априорно вычисленных критериев получить будет более проблематично) какого-то критерия(признака) с выходными полями какой-то линии БК допустим с 1Х2 тогда Вам один хрен придётся в таблице заменить их обозначения по ВНП на нули и единицы (прописав три столбика типа 100 010 и так далее 1--исход состоялся. Далее --для тренда банка вам могут вот эти столбики вполне понадобится..а так же Вы например можете перемножить кефы линии на эти данные и тогда просуммировав за определённое число игр получите баланс в окне..ну и так далее...поэтому без добивания таблицы Вы всё равно не обойдётесь.
Отредактировано Bambuk, 19 March 2015 - 14:01.