Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Соединение Excel с базой СУБД SQL Server, для дальнейшего использования команд SQL внутри ячейки
 
Здравствуйте! Прошу помощи у всех знающих..))
Буду краток и опишу основные требования к документу.
Для начала хотелось бы спросить, возможно ли в ячейке таблицы Excel ввести SQL запрос? Ну или не в ячейке, а в формуле и т.п., чтобы к ячейке применился sql запрос?
Дана такая таблица: в столбце А1 указан код, в столбце А2 указана дата, в столбце А3 должен быть результат SQL запроса, который берется из столбца tarif_otoplenie

select tarif_otoplenie from tarif where kod=A1 and date=A2

Т.е. программа должна просмотреть ячейку A1 с кодом тарифа, ячейку A2 с датой и выдать тариф, соответствующий коду и дате.

Если можно будет задействовать для просчета SQL Server management studio будет воооообще замечательно.

Буду очень признателен за помощь, такой запрос очень важен для меня.

Не могу прикрепить шаблон расчета, размер более 1 мб. https://yadi.sk/i/LCrFOsyE3EM5mW

Изменено: detrop2011 - 21 Фев 2017 19:13:51
 
detrop2011 вот конкретно штатных методов того, что вы описали - нет, Конечно можно наваять собственную функцию, которая будет выполнять запрос, который должен возвращать всего одну строку и одно поле, но это какой-то бред.   При чем тут SQL Server management studio  ? Где у вас эти таблицы  в базе на  SQL?  ....Даже если так, то проще получить выборку записей по нужным тарифам и обычными формулами необходимые значения получить исходя из условий.  А если тарифы в файле Excel?, то или запрос почти не нужен, или не нужен вовсе.  
 
БМВ, Попробую описать ситуацию подробнее.
Этот документ используется для формирования расчета задолженности для одного конкретного абонента. Я мог бы ВСЕ сделать в SQL, но проблема в том что база ограничена, информация по абонентам доступна только за период 2016 года, а если у абонента задолженность составляет более 100 тыс., при месячном начислении в 1000 руб, то база не покажет никакой информации, т.к. долг тянется вот уже 100 месяцев (8 лет). Документ же нужен для того чтобы понять с какого периода тянется задолженность абонента. Чтобы это понять, нужно много чего учитывать: все время, почти каждые полгода менялись тарифы. Тарифы нужны чтобы подсчитать месячное начисление (площадь умнож. на тариф). Месячное начисление нужно для подсчета суммы задолженности.
Я хочу все это как то автоматизировать.
 Вот у меня есть уже готовые тарифы, готовые конечные суммы задолженности (на сегодняшний день) и площади.
Если грубо: просмотреть таблицы с имеющимися данными и вытащить данные соответствующие имеющимся.
Наверно как то сложно описал ((
 
Хотя в принципе функция ВПР выручила бы, если бы можно было задавать 2 и более искомых значения.
Т.е. функция выглядела бы так: найти цену тарифа из таблицы tarifs, где дата и код соответствовали дате и коду из таблицы расчета.

Прошу прощения, нашел как такое произвести.  :oops:
Изменено: detrop2011 - 21 Фев 2017 20:44:24
 
Думаю все это  возможно, вы можете в вашем примере показать что куда должно попасть, например для меня неочевидно , что есть "Тариф отопления" Это нименование тарифа величина ....

Присмотритесь к этой функции, при уникальности тарифа и дат в справочнике она даст вам искомые значения, несмотря на название :-)

Однако я не понимаю, если данные в таблицах и у вас знания в SQL есть то почему не перейти в Access и экспортировав или прилинковав эти таблицы сделать необходимые SQL запросы. Собственно тоже можно сделать просто в Exceд через MSQuery (встроенный инструмент) или  PowerQuery (надстройка но уже в 2016 встроена)
Изменено: БМВ - 21 Фев 2017 20:57:36
 
БМВ, Попробую еще кое как подробнее описать..))
Кстати, ни один пример "двойного ВПР" не поможет мне в данном случае.

Мне нужно указав лицевой счет, чтобы программа расписала все начисления за определенный период.
Что я имею: к каждому лицевому привязан код тарифа. К каждому коду тарифа, в свою очередь, привязана дата и стоимость тарифа. Т.е. имеется лицевой счет 124124, код тарифа 63, стоимость тарифа по данному коду менялась несколько раз в течении года, т.е. в определенные периоды цена оставалась неизменной. Скажем, с 01.01.2015г. по 01.05.2015г. цена составляла 17 рублей, затем она повысилась и уже с 01.06.2015г по 01.12.2015г. цена стала 20 рублей.
И вот мне нужно, чтобы программа просмотрела строки с датами, сравнила их с датами и ценами на тарифы и расписала в какой период какая цена тарифа была.

Приложу обновленный вариант документа, открыв его, я думаю вы поймете все сами..)) Там при вводе лицевого счета - подбирается код тарифа. Но код тарифа не правильно подбирает цену. https://yadi.sk/i/LCrFOsyE3EM5mW
P.S. Документ из 3 связанный между собой листов.
Важна лишь левая половина расчета, правая половина - элементарное суммирование.

Access использовать не могу, т.к. этим расчетом будет пользоваться другой персонал, у меня же указания разработать максимально простой в использовании шаблон расчетов. Так же ограничено время для формирования всех отчетов (около 200 в день).
Изменено: detrop2011 - 21 Фев 2017 21:25:53
 
В документе цена тарифа подбирается исходя из даты тарифа, функцией ВПР. Но если бы теперь можно было связать дату тарифа и код - получился бы нужный результат.  
 
Это не решение, а скорее то как можно реализовать используя ваши знания SQL

Часть данных я удалил для уменьшения размеров, вернуть  можете .
На доп. листе MS Query, но запрос надо вам доработать. Я не силен в запросах ибо редко пишу, но тут есть  как минимум один специалист Андрей, который  неодноератно меня выручал и наверно сможет подсказать..
Область Print_Area просто для выделения нужной области использовал.
Код
SELECT `Расчет$Print_Area`.`№`, `Расчет$Print_Area`.Дата, `Расчет$Print_Area`.`Код тарифа`, `Тарифы$`.Тар_ГВС, `Тарифы$`.Тар_отоп
FROM `Расчет$Print_Area` `Расчет$Print_Area` LEFT OUTER JOIN `Тарифы$` `Тарифы$` ON (`Расчет$Print_Area`.Дата >= `Тарифы$`.Дата) and (`Расчет$Print_Area`.`Код тарифа` = `Тарифы$`.Код)
Изменено: БМВ - 21 Фев 2017 23:46:36
 
БМВ,Не совсем понял действия в вашем файле.. не могли бы вы описать подробнее шаги?
 
F1 , там MSquery , или https://yandex.ru/search/?text=msquery&clid=2233627&lr=2 ....
Я исходил из того
Цитата
detrop2011 написал:  мог бы ВСЕ сделать в SQL,
и только нужен инструмент в Excel. В запросе надо только Last получить из тарифов.
 
Зачем вы сюда SQL приплели?
поставили бы вопрос нормально - есть таблицы с такими то данными. как подтянуть из таблицы А в таблицу Б по таким то критериям такую то информацию.
И нету "многабукафф", и помогающих было бы больше.
сейчас поди разберись в теме - что есть и что надо)

Если я правильно понял, то задача элементарная - на ИНДЕКС и ПОИСКПОЗ.
(удалил третий лист чтобы вписаться в размер)
 
Доброе время суток.
Цитата
Dima S написал:
как подтянуть из таблицы А в таблицу Б
:)
По заданному на листе "Расчёт" лицевому счёту в С1 (у меня) найти на листе "Коды тарифов" код тарифа обслуживания этого лицевого счёта.
Для каждой даты на листе "Расчёт" и найденного тарифа на листе "Тарифы" найти стоимость "Тар_отоп" и "Тар_ГВС" по ближайшей меньшей или равной дате (в случае если даты меньше или равно нет, то брать самую минимальную дату) найденного тарифа и вывести в соответствующий столбец "Тариф отопление", "Тариф ГВС" листа "Расчёт".

Собственно, табличку с SQL и самосоединением сделал. Жирный случай не обрабатывается - озаботьтесь существованием на листе "Тарифы" наличия даты по условию, ну, или переделайте SQL ;)  
Файл сохранить в папку c:\Path, в С1 вводите ЛС и ПКМ в таблице листа "Расчёт" - обновить.
Путь, имя файла и SQL можно поменять в свойствах подключения.
Успехов.
P. S. Excel не очень хорошо подходит для запросов такого сорта - у меня обновляет около минуты.
Скрытый текст
Изменено: Андрей VG - 22 Фев 2017 07:12:15
 
Андрей,  вы правильно поняли кого я подразумевал :-)
Dima S  Еслиб не надо было брать последний тариф перед месяцем искомой даты, то все правильно, простым действием получаем, но как я и андрей поняли, это не так.

detrop2011 Утро вечера мудренее, если тарифы упорядочены по дате, а в примере это так, то
или
Код
=IF(LARGE((ROW(tarifs[Дата])-1)*(tarifs[Дата]<=[@Дата])*(tarifs[Код]=[@[Код тарифа]]);1)=0;;INDEX(tarifs[Тар_отоп];LARGE((ROW(tarifs[Дата])-1)*(tarifs[Дата]<=[@Дата])*(tarifs[Код]=[@[Код тарифа]]);1)))
или
Код
=IFERROR(INDEX(tarifs[Тар_отоп];AGGREGATE(14;6;(ROW(tarifs[Дата])-1)/(tarifs[Дата]<=[@Дата])/(tarifs[Код]=[@[Код тарифа]]);1));0)
для второго сделал доп столбец. Вроде работат
Данные убрал для компактности.
 
Цитата
БМВ написал:
последний тариф перед месяцем искомой даты
Каким образом это делает запрос
select tarif_otoplenie from tarif where kod=A1 and date=A2, предоставленный автором?

П.С. че то не получается у меня ванговать последнее время)
П.П.С. вот, нечто похожее на вариант БМВ
Изменено: Dima S - 22 Фев 2017 13:22:29
 
Dima S,

Если честно, то ушли от темы очень далеко. Продвинулись к результатуу, но вопрос был  о использовании SQL запроса .
Конечно не сразу, но про  тарифы и их смену прозвучало
Цитата
detrop2011 написал:
Скажем, с 01.01.2015г. по 01.05.2015г. цена составляла 17 рублей, затем она повысилась и уже с 01.06.2015г по 01.12.2015г. цена стала 20 рублей.
И вот мне нужно, чтобы программа просмотрела строки с датами, сравнила их с датами и ценами на тарифы и расписала в какой период какая цена тарифа была.
Хотя я это уже тогда понял.

А в целом следует проверить ваш вариант составного индексого поля. Мне кажется ,может сработать и в этом случае , только надо искать не точное совпадений.
 
Спасибо всем большое за отзывы!
Сработал способ от товарища БМВ
Мне остается лишь добавить все цены, все лицевые счета и все тарифы в документ. Просчет вроде идет нормально.  
 
Цитата
detrop2011 написал:
Сработал способ от товарища БМВ
А в чём расхождения с вариантом на SQL? Требую доказательства ошибки.  8)
 
Цитата
SQL запрос внутри ячейки Excel,
О чем писали в 15-ти сообщениях, если
Цитата
Андрей VG написал: А в чём расхождения с вариантом на SQL?
 
vikttur, Андрей VG,
Цитата
Андрей VG написал:
А в чём расхождения с вариантом на SQL? Требую доказательства ошибки.
К сожалению пока нету времени изучить все предложенные варианты.
Расхождения в том, что в базе которая работает под управлением SQL нету информации за периоды ДО 2016 года, нету ни тарифов, ни цен на них, которые нужны аж с 2002 года. По этому приходиться все делать в екселе..
Простите, не понял вопроса..)) Так же не понял как работает предложенный вами документ..  
Изменено: detrop2011 - 23 Фев 2017 09:26:28
 
Вопрос - о названии темы. Похоже, оно совершенно не отражает того, что внутри.
Название темы должно отражать суть задачи. Предложите новое. Модераторы переименуют тему.
 
vikttur, Суть темы: соединение документа Excel с базой данных под СУБД SQL Server, для дальнейшего использования команд SQL внутри ячейки.
Или же не внутри конкретной ячейки, а так, чтобы при исполнении запроса в ячейку (столбец, строку) записывался результат.
Простым соединением из вкладки Данные - Создать запрос такое исполнить (как я думаю) нельзя, т.к. нужно задавать условия.
 
Но в таком случае предложенный мной вариант ваще не в тему, а тот к которому склонял сперва и который сделал Андрей, более правильный, хотя и не относится к одной ячейке.  
 
БМВ, Ну как то получилось, что ваш вариант как раз и работает как нужно..))
Единственно, коды тарифов подбираются уже не совсем верно.. т.к. они тоже менялись, точнее их кол-во с годами увеличивалось.
Посоветуйте пожалуйста, как правильно сделать?
https://yadi.sk/i/28X7JfyW3ESbwB
Как видно в таблице первая половина - нули, т.к. на ту дату не было такого кода тарифа. На ту дату а данного абонента был другой код, указанный на листе "Тарифы", соответсвующий дате на листе расчета.
Изменено: detrop2011 - 23 Фев 2017 16:05:42
 
До субботы с телефона, да и тема иная. Уверен, смогут другие помочь. А вариант рабочий мой только по тому, что реализация на результат, а не на первый вопрос. Хотя уверен, вариант Андрея дает тот же результат но чуть сложнее для понимания. Так что правильно формулируйте вопрос, получайте ответ. Как говорил наш математик, в вопросе есть половина ответа  
Изменено: БМВ - 23 Фев 2017 16:29:33
 
Доброе время суток.
Цитата
detrop2011 написал:
для дальнейшего использования команд SQL внутри ячейки.
Вот функция, возвращающая сумму чека покупки заданного покупателя ближайшую меньшую или равную к заданной дате с таблицы базы на SQL Server.
Код
Public Function GetLastCheckAmount(ByVal OnDate As Date, ByVal CustomerId As Long) As Double
    Const connStr = "Driver={SQL Server Native Client 11.0};Server=(localdb)\mssqllocaldb;Database=AdventureWorks2014;Trusted_Connection=yes;"
    Const baseSQL = "Select Top(1) SubTotal From Sales.SalesOrderHeader Where OrderDate <= '$orderdate' And CustomerID = $custid Order By OrderDate Desc;"
    Dim sSQL As String
    sSQL = Replace$(baseSQL, "$orderdate", Format$(OnDate, "yyyyMMdd"))
    sSQL = Replace$(sSQL, "$custid", CStr(CustomerId))
    With CreateObject("ADODB.Connection")
        .Open connStr
        GetLastCheckAmount = .Execute(sSQL)(0).Value
        .Close
    End With
End Function

Поможет? Что-то меня сомнения берут.
 
А меня не сомнения - грусть одолевает... Автор говорит об одном, но решение принимает совсем другое...
detrop2011, если Вы понимаете сами себя :), предложите название темы, которое раскрывает обсуждаемую задачу. Модераторы заменят.
 
Offtop
Цитата
vikttur написал:
Автор говорит об одном, но решение принимает совсем другое...
Цитата
... Так выпьем же за то, чтобы наши желания всегда совпадали с нашими возможностями!
 
vikttur,Название темы раскрывает мою задачу, но сработал способ не совсем связанный с задачей.
В функции предложенной товарищем БМВ я еще не разобрался (времени как то не было), но результат в какой то степени дает :D.
Попробую еще раз расписать как бы в идеале выглядела моя задача:
Я хочу, связав базу под SQL и документ Excel, вводить команды, для просчета строк и столбцов ИЗ документа ексель.
Т.е. чтобы я мог в ячейке написать запрос: select kod from kods where nc=B5. т.е. выбрать код тарифа из листа kods где лицевой соответствует значению ячейки B5 из листа Расчет. Таким образом я хочу получить код тарифа соответствующий лицевому на листе Расчета.
Получив код, пишу запрос в следующую ячейку :
select tar_ot from tarifs where kod=C7 and data=B7 где я получу значение цены тарифа соответствующий коду тарифа из ячейки С7 и даты из B7
Испробовал все способы связки документа с СУБД, но нигде не могу задать условия вычислений. Оператор WHERE нигде не задействован.
 
М да, уже не знаю - то ли плакать, то ли смеяться...
Цитата
использования команд SQL внутри ячейки
Поменяем SQL на Excel, получим
использование команд Excel внутри ячейки - есть ли в этой фразе что-нибудь осмысленное? Что есть результат этого использования?
 
Хорошо, может я слишком все усложнил, теперь видимо задача уже другая. Приложу готовый вариант, где осталось решить одну проблемку.. Если посмотрите документ, видно что расчет цены тарифа ведется ОТ 01.07.2014 года, так как на листе Тарифы  ДО 01.07.2014г. было меньше тарифов. Т.е. за предыдущие периоды для данного лицевого счета код тарифа был уже не 70 а другой. Помогите пожалуйста решить задачу. Буду очень благодарен.
https://yadi.sk/i/hVeh3pPZ3EVnTU
Страницы: 1 2 След.
Читают тему (гостей: 1)