Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: Пред. 1 2
RSS
Поиск последних 3 значений и возврат их среднего, нечто вроде VLOOKUP
 
#31
15.05.2015 16:54:08
Цитата
TheBestOfTheBest написал: Может, см. оператор TOP.
м-да, посмотрела на него несколько дней 8)... пришла к выводу, что ключ, видимо, надо использовать составной из полей Товар-Дата (т к по первонач задаче даже в один день была закупка одного товара по разным ценам)... в любом случае, даже как найти TOP по дате в рамках КАЖДОЙ группы по Товару - осталось большой загадкой??... кроме как, я наконец, поняла 3 правила Нормализации Таблиц для БД (хорошо расписано здесь) и попробовала рисовать Схему (которая по данной задаче тоже осталась загадкой)?... вобщем, очень уж "не-релятивная" задача оказалась... но всё-таки как-то, наверно, здесь можно рассмотреть одну таблицу, 2 раза обозвав её в запросе по-разному, и 1-й раз) сделать выборку по Товар, Count (группа по Товар) - выделив уникальные (если DISTINCT не пройдёт); 2-й раз) выборку TOP 3 Дата ordered by desc... и тут я потеряла все отношения между данными :(... не зная на предмет чего сравнивать дальше эти выборки, и понимая, что даты поставки у каждого товара свои...
TOP потерялся... а было очень интересно реализовать что-то вроде (только не по max, а по top 3) - если бы подошло:
Цитата
...такой запрос  требует соединения таблицы P1 с собой, чтобы можно было разделить ее на вложенную серию сгруппированных таблиц. После этого в каждой группе извлекается максимальное значение...
p.s. TheBest, но вам спасибо за наводку! (узнала новые слова)
Изменено: JeyCi - 15.05.2015 17:06:18
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#32
15.05.2015 17:16:34
Off
Если есть желание  - вникнуть в один из вариантов - это здесь -http://forumooo.ru/index.php/topic,4944.0.html ... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#33
15.05.2015 17:35:08
Route66, не пишие через строку.
Не используйте кнопку цитирования для ответа.
 
 
 
#34
15.05.2015 22:05:07
Цитата
Z написал: Off
:) очень резкий разворот событий после нескольких дней попыток применить TOP... (open office не имею, чем файл ods открыть не знаю, переводить формулы для именованных диапазонов - могу застрять ещё надолго)...
поэтому пока на скорую руку из ИСХОДНОГО ТЗ (данные, оставшиеся в пост№26) - не для больших выборок, но быстро
(правда шагов несколько):
0) доп. столбец id по мм стекла (для фильтра потом) - конкретно для данного примера
1) Свести Среднее по дате (т к они повторялись для одного товара) - и по Товару в Сводной на лист PTdate_AVG
2) далее Сводную из (1) раскидать по фильтру -- (находясь в сводной): Параметры--Сводная Таблица--Параметры-- Отобразить страницы фильтра отчёта...
3) выбрать любой - взяла Лист 10 -- отсортировать Сводную на убывание
4) в столбце E:F применить формулы (по примеру на contextures.com) --> для отбора 1-ых 3-х из Сводной --> найти Среднее
5) меняя фильтр по мм стекла - видеть данные по каждому...
- как альтернативный вариант (показался симпатичным)...
(как свести все средние в одну таблицу, как хотелось ТС, - вариантов др, кроме уже данных, пока не вижу)
p.s. решение из поста №26 через подключение - тоже оставлено в файле... для варианта, описанного в этом посте, оно не используется
Изменено: JeyCi - 15.05.2015 23:30:51
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#35
16.05.2015 20:40:52
Для последних 3-х дат могу предложить только сводную, пока.
Изменено: TheBestOfTheBest - 16.05.2015 21:02:53 (заменил файл)
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#36
17.05.2015 19:28:13
Цитата
TheBestOfTheBest написал: Для последних 3-х дат могу предложить только сводную, пока.
видимо, что-то не совсем то - посмотрела на данных из файла№34 - что-то с сортировкой, наверно... т к по 10см даёт среднее между средними (первыми 3мя и последними 3мя)... экзотика...
у меня -- методами google, проб, ошибок и адаптации  :oops: -- пока только вариант среднего за последний день:
Код
SELECT t1.Товар,  t1.Дата ,avg(t1.`Цена за ед без НДС`) as Стоимость  FROM table1 t1 
    INNER JOIN (SELECT Товар, MAX(Дата) AS maxДата FROM table1 GROUP BY Товар) t2
    ON (t2.Товар = t1.Товар and t2.maxДата = t1.Дата)
GROUP BY t1.Товар,  t1.Дата
Изменено: JeyCi - 17.05.2015 19:30:48
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#37
17.05.2015 20:39:53
Специально для JeyCi
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#38
19.05.2015 13:20:29
Цитата
TheBestOfTheBest написал: Специально для JeyCi
TheBest, насколько вижу ваш запрос отбирает макс не нулевые, потом следущие макс из оставшихся, и снова следующие из оставшихся после второго отбора, после чего, наверно, формирует итоговую строку среднего через очередной LEFT JOIN и RIGHT JOIN... оказалось трудным уловить последовательность действий ваших операторов при отборе - если найдётся время пояснить, было бы очень познавательно... или может быть, схемой было бы понятнее?.. и интересно уточнить, конкретно по данному запросу вы делали вручную или VS или MS Query?.. (ваш опыт работы с запросами привносит много нового и полезного в мои мысли :))
_________________________
у меня получился такой ход мыслей: ( отталкиваясь отсюда по 3-м последним)
отбор 3-х макс Дат
Скрытый текст

отбор 3-х макс Дат для каждого Товара для вывода их всех
Скрытый текст
добавив стоимость в запрос - на выходе будет 3 столбца: дата, товар, стоимость...

подсчёт Среднего по 3-м последним датам закупки для каждого Товара
Скрытый текст

- (есть рабочий файл с 3-м запросом - [его и в пост№34 можно вставить - у меня получился вес >100кб - пока не могу загрузить], но дошла до него очень окольными путями... - может быть есть прямой путь - проблема в том, что при корректировке запроса до желаемого происходит, насколько понимаю, неявное преобразование типа данных поля Стоимость - возможно, потому что "умная таблица" сама по себе помнит что-то... как это побороть в повседневной жизни (чтобы долго не искать выход)?.. есть ли какая-нибудь рабочая "фишка" побороть эту досадную неприятность?.. пробовала использовать операторы  CAST и  CONVERT - выдавал ошибку...

p/s/ для ТС - таблица начальная задана именованным диапазоном table1; если не хотите привязываться к имени диапазона в исходных данных - указывать в запросе лист и можно ещё и диапазон столбцов от A: до D (убрать _) [Закупка$A:_D] вместо table1
Изменено: JeyCi - 20.05.2015 07:49:41
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#39
19.05.2015 14:51:12
сократила размер файла - вот так он выглядит (xl2010) c 3-им запросом - см вложение ...
но на вопросы предыдущего поста ответы ещё очень волнуют - это неявное преобразование типа данных иногда жизни не даёт... какие способы его обходить?..
p.s. файл, как и в случае TheBest - располагать в папке с:\1\  
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#40
19.05.2015 15:02:53
Цитата
JeyCi написал: это неявное преобразование типа данных иногда жизни не даёт
Я уже писал и показывал, вы невнимательны. Либо заполняем образцом данных вторую строку источника, либо функции типа CData().
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#41
19.05.2015 15:16:39
1-й вариант не всегда подходит ввиду нач структуры данных, 2-й вариант не срабатывает
(проблемный файл не выкладываю - он стал большим в ходе экспериментов по данной теме)
Код
avg(CDbl(t4.Стоимость)) as [Ср за 3 последние]
и 
avg(CInt(t4.Стоимость)) as [Ср за 3 последние]
Изменено: JeyCi - 19.05.2015 15:18:30
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#42
19.05.2015 15:21:24
Код
CDbl(avg(t4.Стоимость)) as [Ср за 3 последние]
а так?
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#43
19.05.2015 15:30:39
Цитата
TheBestOfTheBest написал: а так?
тоже не работает...
приходится потом руками менять формат ячеек самой "умной" таблицы с даты на общий... а если при обновлении количество строк увеличивается - снова вылазят атавизмы Даты... странно, что CONVERT и CAST не работают - наверно, они только на sql server'e работают :(
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#44
19.05.2015 15:32:40
Цитата
JeyCi написал:с даты на общий
Так какой формат нужен? Непонятно, пишите CDbl, а хотите дату.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#45
19.05.2015 15:36:08
Цитата
TheBestOfTheBest написал: Непонятно, пишите CDbl, а хотите дату.
Дату не хочу, но она вылазит - надо принудительно в CDbl или float (видимо, на языке sql)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#46
19.05.2015 15:38:09
Проще умножить на 1 и в выходной таблице поставить формат столбца соответствующий.

Похоже что сам запрос не причем, это формат ячеек слетает.
Изменено: TheBestOfTheBest - 19.05.2015 15:39:07
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#47
19.05.2015 15:47:56
Цитата
TheBestOfTheBest написал: Проще умножить на 1 и в выходной таблице поставить формат столбца соответствующий.... это формат ячеек слетает.
1-е не помогло...  насчёт 2-го: думаю, формат Даты как-то изначально застрял в "кэше" умной таблицы (для конкретного столбца), и потом при смене запроса в эту же умную таблицу - он воспроизводится, как изначально застрял... наверно, было бы интересно понять, как его чистить? но к сожалению, не знаю...
p.s. гипотез много - (может, потому что агрегатная функция max изначально шла для столбца Дат, потом и на avg как-то автоматом пошла... может группировка как-то подвела) - рабочих версий пока нет
Изменено: JeyCi - 19.05.2015 15:53:34
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#48
19.05.2015 16:55:15
Я так понял, что ТС пропал? :) Хотелось бы начальные данные пополнее получить :)
Учусь программировать :)
 
 
 
#49
20.05.2015 11:46:58
Smiley могу лишь запрос пополнее предложить :) - (данные в файле чуть изменены для достижения универсальности, запрос немного исправлен - см Лист1) - вроде бы работает верно и универсально для данной задачи окончательный вариант

Скрытый текст
Изменено: JeyCi - 20.05.2015 11:49:31
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#50
21.05.2015 09:17:04
Цитата
JeyCi написал: думаю, формат Даты как-то изначально застрял в "кэше" умной таблицы (для конкретного столбца), и потом при смене запроса в эту же умную таблицу - он воспроизводится
Если в ходе экспериментов с подключением у кого-нибудь возникнет такая же проблема...
Проверить уже не на чем - НО решение данной проблемы видится в этом:
Скрытый текст
  1. Параметры Excel -- Дополнительно. -- Правка -- флажок Расширять форматы и формулы в диапазонах данных
  2. Чтобы отключить автоматическое форматирование диапазона данных, снимите этот флажок.
Изменено: JeyCi - 21.05.2015 09:21:44
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#51
21.05.2015 10:21:19
JeyCi, по Вашей табличке, как-то так, наверное :)
Код
Sub wiggle()
Dim Conn As New ADODB.Connection, this_wb As Workbook, LR&, RR As Range
Set this_wb = ThisWorkbook
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended Properties=""Excel 8.0; HDR=Yes;""; Data Source=" & this_wb.FullName
With Sheets("Лист1")
LR = .Cells(Rows.Count, "A").End(xlUp).Row
For Each RR In .Range("A2:A" & CStr(LR) & "")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.Range(.Cells(RR.Row, 2), .Cells(LR, 2)).CopyFromRecordset _
Conn.Execute("SELECT AVG([Цена за ед без НДС]) " _
           & "FROM (SELECT TOP 3 Дата, [Цена за ед без НДС]" _
                 & "FROM [Закупка$] WHERE Товар = '" & Cells(RR.Row, 1) & "' ORDER BY Дата DESC)")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Next RR
End With
Conn.Close
End Sub
ЗЫ: Сейчас подумаю, как сделать вообще без перебора ячеек.
Изменено: Smiley - 21.05.2015 10:29:19
Учусь программировать :)
 
 
 
#52
17.06.2015 14:51:27
Цитата
JeyCi написал: отключить автоматическое форматирование диапазона данных
оказался и вариант попроще (без настроек всего xl) - лишь снять птицу в свойствах таблицы результата-запроса... - если менять запрос уже существующий - чтобы без прошлого форматирования... (много приятных нюансов - в т.ч. и выбранная в запросе последовательность столбцов - а не автоприклеивание новых столбцов при изменении запроса в ту же таблицу)


p.s. потом птицу можно вернуть(если она нужна) - после однократного обновления с новым запросом
p.p.s. Smiley - спасибо за ADO... предлагала задачу ТС "рассмотреть руками"... ADODB.Connection - хорошее дополнение про запас  
Изменено: JeyCi - 17.06.2015 14:57:04
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#53
31.08.2015 16:19:07
Цитата
Smiley написал: Сейчас подумаю, как сделать вообще без перебора ячеек.
свершилось чудо! - я поняла свои проблемы того дня - почему у меня не работал запрос под спойлером, когда пыталась его адаптировать под эту задачу

Скрытый текст

потому что ещё не знала особенностей использования Агрегатных функций... (спасибо Андрей VG)
такое (под спойлером), видимо, могло бы работать на SQL Server, а мы ещё в Excel, похоже... вобщем, адаптировался вариант sql'я  в «старом стиле»
Код
SELECT s1.Товар, avg(s1.`Цена за ед без НДС`) AS av 
FROM table1 s1 
  WHERE s1.[Дата] IN 
    (SELECT top 3 s2.[Дата] 
        FROM table1 s2
        WHERE s1.[Товар] = s2.[Товар] 
        ORDER BY s2.[Дата] DESC)
GROUP BY s1.Товар
p.s. жаль, что ещё не сама написала, но делюсь новым пониманием... что искали в #31(см. цитата), то нашлось  :idea:
пример немного видоизменён для отображения универсальности... пока что так... тоже в папку c:\1\
Изменено: JeyCi - 31.08.2015 16:38:13
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
Страницы: Пред. 1 2
Наверх