Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Объединить показатели и вычислить их среднее
 
#1
27.11.2015 01:21:46
Помогите составить таблицу в которой будут объединяться показатели и вычисляться их среднее.

Таблица которая справа обновляется каждый день. Т.е. на след день при совершении новых сделок, текущие удаляются, а новые появляются. Во первых аналогично должна обновляться и вторая таблиц.

Во вторую таблицу мне нужно выводить среднюю цену каждой сделки. Для этого необходимо чтобы объединялись все строки с одинаковым "Бумага сокр" и "Операция", складывались их показатели количество, а по показателям цена определялось среднее (каждое количество*каждую цену/общее количество) Например в первых 4 строках отображается сделка по покупке РТС которая выполнилась 1 контрактом по цене 83500, 2мя по 83700, 1 - 83750 и 1 - 83800. Соответственно эти все четыре строки объединяются в одну во второй таблице, так как у них одинаковая бумага и операция (если даже строки не будут идти подряд но данные параметры у них одинаковые, то всё равно нужно чтобы объединялись) Кол-во во второй таблице образовалось в результате сложения всех колиечеств, а средняя цена на основе вычисления (1*83500+2*83700+1*83750+1*83800)/5=83690

Дата и время нужно чтобы бралось первое из потенциального списка сделки.
Для лучшего понимания отделили в первой таблице каждую сделку отдельным цветом
Заполненность первой таблицы вниз может быть ориентировочно до 200-той строки
 
 
 
#2
27.11.2015 01:36:25
Может обычная сводная подойдет?
 
 
 
#3
27.11.2015 08:48:26
Может и подойдёт, но в представленном варианте не то что нужно.
1. Нет времени начала открытия сделки.
2. Столбцы бумага и операция нужно поменять местами.
3. Промежуточные и общие итоги в данной таблице не нужны.
4. И самое главное - текущая сводная объединила 4 сделки по SBRF в 2
Нужно чтобы сделка считалась одной до тех пор пока по ней идёт набор, т.е. если в гланой таблице эта бумага появляется с одной операцией, например Купля, то всё это одна сделка, как только появились операции по этой бумаге другого типа Продажа, то это уже началась вторая сделка, и когда после продажи уже опять появлются купля, то это уже пошла третья.
 
 
 
#4
27.11.2015 11:29:02
на Сводной - ПКМ - Показать список полей - можете и время добавить и поля местами поменять... промежуточные и общие итоги тоже убрать можно (ПКМ - Параметры сводной таблицы - Итоги)... более подробные ответы на эти вопросы можно найти в Приёмах (вкладка вверху этой страницы)... да и во вкладке Конструктор (которая появляется в xl при выделении любой ячейки сводной) можно много интересного найти
Изменено: JeyCi - 27.11.2015 11:31:09
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#5
28.11.2015 16:24:06
По настройке сводной то понятно, но вот не получилось сделать так, чтобы не объелинялись разные сделки
Нужно чтобы сделка считалась одной до тех пор пока по ней идёт набор, т.е. если в гланой таблице эта бумага появляется с одной операцией, например Купля, то всё это одна сделка, как только появились операции по этой бумаге другого типа Продажа, то это уже началась вторая сделка, и когда после продажи уже опять появлются купля, то это уже пошла третья.
 
 
 
#6
28.11.2015 17:22:01
Доброе время суток
Вариант на Power Query. Вопрос, а секунды больше 59 - чтобы удержать порядок сортировки в пределах сделки?

Успехов.
 
 
 
#7
28.11.2015 17:54:32
Быстро глянул - вроде то что нужно. А вот вопрос ваш не понял, можете пояснить?
 
 
 
#8
28.11.2015 18:23:59
Я собственно об этом. Первая же сделка RTS-12.15 [FORTS], Купля содержит следующие времена: 10:15:59, 10:15:60, 10:15:61, 10:15:62. Время содержит секунды, большие 59. Я предположил, что это необходимо как маркер одной сделки. Чтобы, если, допустим, таблицу отсортировали по Кол-во, то можно было восстановить исходный порядок сделок по дате, времени.
Хотя, на мой взгляд - это не надёжно, лучше при открытии сделки задавать ей уникальный номер, а время писать в естественном виде. Чтобы избежать ситуации когда время становиться 10:15:100 - при этом сортировка будет выполнена не правильно. Может быть, конечно, у вас таких коллизий и не происходит.
 
 
 
#9
28.11.2015 19:00:29
добрый вечер протестируйте макрос,кнопка yyy в файл-примере.
 
Код
Sub yyy()
    Dim z(), i&, j&, m&, t$
    With CreateObject("scripting.dictionary"): .comparemode = 1
        z = Range("A1").CurrentRegion.Value
        For i = 1 To UBound(z)
        t = z(i, 3) & z(i, 4)
            If Not .exists(t) Then
                m = m + 1: .Item(t) = m: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next
            Else
             z(.Item(t), 5) = z(.Item(t), 5) + z(i, 5)
             z(.Item(t), 6) = z(.Item(t), 6) + z(i, 6) * z(i, 5)
            End If
        Next
    Range("H1").Resize(.Count, UBound(z, 2)) = z
   For i = 2 To .Count: Range("M" & i) = Range("M" & i) / Range("L" & i): Next
   End With
End Sub
Изменено: sv2013 - 28.11.2015 19:17:07 (убрал лишнюю переменную)
 
 
 
#10
28.11.2015 19:28:47
Всё тот же вариант сводной с исправлениями и время изменено в числовой формат
 
 
 
#11
29.11.2015 00:52:29
Андрей VG - Нет время там неправильное, поскольку я просто добавил несуществующих сделок и растянул время. По нему ориентироваться не пойдет.
А вот на счет дополнительного якоря есть вариант.

sv2013 - Та же проблема разные сделки объединяются

gling - этот вариант уже больше похож на то что нужно, но есть пару моментов - во первых непонятно что за блок цифр в столбце О, во вторых, если записи двух сделок перемешиваются, что бывает весьма часто, например покупается одновременно и РТС и ГАЗР, то в таблице могут чередоваться их записи и тогда таблица каждый новую группу покупок будет обозначать как отдельную, хотя в реале их будет всего две.

Есть вариант добавить к таблице ещё один столбец "Заявка" после столбца цена. Там будет 9 значный код, который формируется индивидуальный к каждой отдельной сделке, можно взять его за основу.
Прикладываю пример с новым столбцом-идентификатором и сделками вперемешку (РТС и ГАЗр последние)

Дополнение - стоит также учесть, что таблицы I1:O9 в необходимой версии не будет. Это я для наглядности добавил. Также в некоторых вариантах присланых заметил что неправильно вычисляется средняя сумма.
Изменено: vladcodex - 29.11.2015 01:13:43
 
 
 
#12
29.11.2015 01:18:10
Вызнаете как у Вас разделяются сделки а я нет, в столбце 0 попытка присвоить каждой сделке свой номер. Если одна и та же сделка появляется после других сделок то её как то надо отметить тем номером к которому она относится.
Цитата
Там будет 9 значный код, который формируется индивидуальный
Не можно, а нужно и доп. столбец 0 тогда не нужен.
Изменено: gling - 29.11.2015 01:18:41
 
 
 
#13
29.11.2015 04:14:05
Доброе время суток
Цитата
vladcodex написал:
9 значный код, который формируется индивидуальный к каждой отдельной сделке
Мда, "А слона то я и не примерил"™  ;)  значит не зря вопросы задавал. vladcodex, а не выяснится потом, что данные хранятся на Oracle, MS SQL, MySQL и т. д. сервере, и вопрос можно решить простым аналитическим запросом?
Код
Select Distinct "Заявка", Sum("Кол-во") Over (Partition By "Заявка" Order By "Заявка") As "Кол-во" From БазаСделок

Во вложении, вариант на Power Query.
Цитата
vladcodex написал:
Также в некоторых вариантах присланых заметил что неправильно вычисляется средняя сумма.
Коллеги не замечают, что в первом посте указано - разговор идёт не о среднем значении по полю "Цена", а о средневзвешенном, вес поле "Кол-во".

Успехов.
P. S. Время в исходной таблице привёл к человеческому виду.
Изменено: Андрей VG - 29.11.2015 04:21:54
 
 
 
#14
29.11.2015 11:17:29
Еще вариант.
 
 
 
#15
29.11.2015 12:01:12
gling, нужно же средне весовое значение центы по заявке. Соответственно в данных источнике сводной нужно сделать вычисление суммы операции как произведение цены на количество. А в сводной создать вычисляемое поле.
 
 
 
#16
29.11.2015 12:56:21
Цитата
Андрей VG написал: в данных источнике сводной нужно сделать вычисление суммы операции как произведение цены на количество. А в сводной создать вычисляемое поле.
Андрей VG  :D я уже 2 часа занимаюсь этим (пытаюсь)... а ваши ответы всегда, как очередная открывшаяся дверь (причём сама по себе и даже взламывать не пришлось) - в нужное время в нужном месте... respect!..
p.s.
я, кстати, внешний вид таблицы ТСа в принципе не одобряю... думаю, любые данные такого рода (где деньги и количество) можно/нужно представлять в виде обыкновенной оборотки (операции купли - продажи по столбцам, сделки по строкам)... - как-то привычнее и логичнее... (не изобретать америку)... вобщем, сама пока присматриваюсь к сводным (на примере данной ветки - просто руки запутались в клавиатуре - поэтому не выкладываю мысль файлом)... но ТСу вариант для размышлений (вести бух.обсчёты, как любую др. бухгалтерию - покупки-продажи в шапку, обороты потом удобнее видеть)... да и взвешивание может не надо, чтобы считать PnL, но если цель - статистика - можно и повзвешивать)... всем успехов... я пока поковыряюсь в сводной      
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#17
29.11.2015 13:21:38
Цитата
нужно же средне весовое значение центы по заявке
Андрей VG Спасибо за разъяснения. Дело в том что я не связан с взвешиванием цен, а в примере показал вычисляемое поле и в нем среднее значение и по строкам и по количеству сделок. Считал что автор сам решит какую формулу применить, а в О2 даже и не заглянул. От меня поступило предложение как сделать, окончательное решение за автором темы. Возможно сводная ему тоже не подходит, ведь там время не в том столбце. Но за разъяснение спасибо, теперь буду обращать внимание на эту разницу средних значений.
 
 
 
#18
29.11.2015 15:17:29
Цитата
JeyCi написал: бух.обсчёты, как любую др. бухгалтерию - покупки-продажи в шапку,
выглядит примерно так (пром.итоги и общие можно выставлять по вкусу, время можно убирать - т.к. для данных такого рода не значимо!)... в общем и целом, советую не перегружать любую репрезентацию итоговых данных (отображать только итоги)... а трейдерская статистика, вообще, вполне достаточно предоставлена в торговом терминале... не люблю считать лишнее  ;)  ... файл приложила, как вариант
Изменено: JeyCi - 29.11.2015 18:38:57
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#19
30.11.2015 06:22:25
Доброе время суток
Цитата
gling написал:
Дело в том что я не связан с взвешиванием цен,
Да, я тоже  ;)  Теодолитный и нивелирные сети уравнивал, опорные точки гравиметрической сети - тоже, статистику геофизикам и геологам рассчитывал. А вот с биржей - разве что рядом со зданием стоял  :)

JeyCi, я понимаю, посчитать доход от покупки по более дешёвой цене и продаже по более дорогой, а вот средневзвешенное - что такая информация может дать в рамках одного дня? Про святите - вдруг где и пригодится. ТС, похоже, уже и не забежит в свою тему.
Изменено: Андрей VG - 30.11.2015 06:23:05
 
 
 
#20
30.11.2015 07:50:50
Цитата
Андрей VG написал: я понимаю, посчитать доход от покупки по более дешёвой цене и продаже по более дорогой, а вот средневзвешенное - что такая информация может дать в рамках одного дня?
Андрей VG. я тоже не вижу смысла взвешивать цены, о чём и написала выше (разве что для какой-то надуманной статистики), а в бух-рии статистика - лишнее... честно говоря, логичнее (и так это обычно и делается) взвешивать количество по цене (с учётом того, что цены производных активом - сами по себе производные от базовых активов - вот и взвешивают спрос и предложение на производные активы, чтобы найти статистически наиболее вероятную равновесную цену между спросом и предложением... (то есть грубо говоря, точка пересечения кривой спроса и кривой предложения)... но даже эта информация - чисто информативна (потому что всегда есть более точные ориентиры равновесной (а значит и максимально "справедливой" цены) - например, паритет (для трейдеров) - и у каждой группы участников рынка свои интересы и цели... да и понятие равновесной - расплывчато - это также точка, где максимальное количество покупателей и продавцов согласны заключить сделку (меня устраивает такая трактовка)... можно отдельно взвесить цены спроса и отдельно цены предложения - "мат.ожидание" - будет коридор , в рамках которого, цена, "вероятно", может колебаться... но всё это "вилами по воде", как и вся теория вероятностей (либо да - либо нет  :))... и, как всегда, есть более точные ориентиры - это конкретные цифры, а не усреднённые... поскольку валидными усреднённые данные становятся только на длительном промежутке времени (ну, или большом количестве вариант в выборке - "закон больших чисел", как мы все помним)
P.S. а обсчёт доходов-расходов и оборотов денежных средств, вообще, не терпит усреднения... с такой информацией обращаться лучше чисто по-бухгалтерски - кол-во*цену и вывести сальдо... бухгалтерия усреднений не приемлет... как и налоговая  ;)  
P.P.S. да, и уточню: всё, что я написала, - это для анализа биржевой отчётности... но не сделок одного трейдера (трейдеру лучше считать свои сделки, как есть)...
Изменено: JeyCi - 30.11.2015 08:23:01
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#21
30.11.2015 08:15:55
Поясню) Усреднённая цена нужна для ведения журнала сделок. Текущая  страница является одной из общего журнала на которую переносятся данные из терминала, а далее в общий журнал где в деталях разбирается каждая сделка.
Это не бухгалтерская отчетность, поскольку для этих целей подходят и отчёты брокеров, а именно трейдерский журнал, кто занимался биржевой торговлей поймёт для чего он нужен,
ПС- а ТС не забегает потому что с Крыма я, у нас электроэнергетическая диета)
Что касается вариантов реализации таблицы, то в последних вариантах я вовсе запутался, там по 3-4 таблицы ещё и на нескольких листах, а времени разобраться не так уж много..
Изменено: vladcodex - 30.11.2015 08:55:40
 
 
 
#22
30.11.2015 08:59:32
JeyCi, большое спасибо за разъяснения. Может и впрямь пригодится через полгода...

Цитата
о в последних вариантах я вовсе запутался, там по 3-4 таблицы
Вариант с одной сводной и дополнительным столбцом в таблице исходных данных. Кстати, мой последний вариант на Power Query тоже без 3-4 таблиц  ;)
Цитата
ПС- а ТС не забегает потому что с Крыма я, у нас электроэнергетическая диета)
Мда, тогда держитесь, тут, увы, больше, кроме слов, помочь не могу - с Якутии электричеством быстро не поделишься  :)

Успехов.
Изменено: Андрей VG - 30.11.2015 09:00:43
 
 
 
#23
30.11.2015 09:23:38
Андрей Ваш вариант мне подходит. Но не могли бы вы пояснить как мне:
- такую таблицу реализовать на других страницах?
- как переместить дату и время в самое начало?
- как обновлять данные в этой таблице, поскуольку при попытке добавить новые данные в исходную, и обновлении выдаёт ошибку?
- тот факт что исходная таблица будет полностью перезаписываться каждый день (но структура останется) не повлияет на работу сорировочной таблицы (данные сохранять не нужно, только чтобы она дальше также работала)? А то пока возможности детально проверить не имеется
 
 
 
#24
30.11.2015 10:46:02
Цитата
- как переместить дату и время в самое начало?
Если вы про сводную, то увы никак.
Цитата
как обновлять данные в этой таблице, поскуольку при попытке добавить новые данные в исходную, и обновлении выдаёт ошибку?
Покажите кусочек добавленных данных - нужно посмотреть, да, и сообщите используемую версию Excel.
Цитата
тот факт что исходная таблица будет полностью перезаписываться каждый день (но структура останется) не повлияет на работу сорировочной таблицы (данные сохранять не нужно, только чтобы она дальше также работала)?
По идее, нет. Лучше исходный диапазон изменить на "умную" таблицу, тогда не нужно будет перенастраивать сводную на изменённый диапазон данных или отфильтровывать пустые значения.
 
 
 
#25
30.11.2015 16:51:09
Цитата
sv2013 написал: добрый вечер протестируйте макрос,кнопка yyy в файл-примере.
А вы можете добавить в макрос фильтр по колонке заявка?
 
 
 
#26
03.12.2015 01:15:37
Может кто-то другой поможет дописать макрос. Мне такой вариант проще и удобнее, а автор макроса не отвечает(
 
 
 
#27
04.12.2015 02:44:52
Ни у кого нет никаких идей? Осталось только чуть-чуть макрос дописать и готово. Помогите пожалуйста
 
 
 
Страницы: 1
Наверх