Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
Power Query и Оператор If-Then-Else
 
И снова Здраствуйте !!!
Задачу которую мне было нужно решить, реализовал ещё 2 года назад.
Сейчас решил выложить логику запроса который хотел реализовать.
Вдруг кому нибудь да пригодиться !

Сделал простенькую табличку.
На её основе сделал запрос.
На Листе1 расположена Яейка с возможностью выбора  значения - Фильтр_Иванов или Сортировка_Сумма.
В зависимости от того что выбрано в ячейке запрос выполняет то или иное действие.
А именно
              или фильтрует Таблицу по полю "ФИО" Значению "Иванов"
              или сортирует Таблицу по полю "Сумма"
Смысла в данном примере особо нет, просто описать логику работы оператора.
Пример очень простой думаю всем будет понятно !!!

Логика в моей задаче была намного сложнее.
В таблице было много столбцов
условий по полям было много.
Шагов в запросе тоже было много.
Но благодаря логике if then else я всё равно намного упростил запрос и сделал его короче и лаконичнее.
Иначе он был бы просто монструозным и не читаемым.

Ещё раз спасибо - PooHkrd - за то что помог разобраться!!!
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
Понял. Спасибо.
Изменено: vikttur - 25.08.2021 10:11:02
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
Я правильно понимаю -
Если делать запрос по именам столбцов а не по именованным диапазонам то и ограничения по столбцам не будет ?
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
Нашел ещё одно интересное ограничение Excel.
Ну как нашёл .... Excel опять стукнул меня по носу неизвестной ошибкой и я опять начал играть в игру найди 10 отличий )))
Суть в том что у меня порядка 13 таблиц расположено на одном листе по порядку слева направо. Из которых я и вытягиваю данные посредством sql запросов.

Между таблицами заложен резерв столбцов на всякий случай если таблицы будут расти по количеству столбцов.
Начиная с 5 таблицы запрос просто отказался работать. Причем если в текущий программный код подставлял предидущие таблицы то код отрабатывал.
Методом научного тыка нашёл причину.
Оказалось данный запрос не работает если приходится вытаскивать данные из диапазона ячеек где крайний правый столбец по своему индексу больше чем 255 или 256 что то такое .

Перекидываю эту же таблицу на новый лист в диапазон с первого столбца и всё работает.
Жаль конечно, у меня и так много листов занято. А тут ещё и под сами данные нужно будет использовать больше чем 1 лист.
Но ничего не поделаешь буду плодить листы.

Надеюсь кому нибудь пригодится мой опыт )))
Как улучшить редактор кода vbe
 
Добрый день форумчане.

Есть такой вопрос. Нельзя ли усовершенствовать редактор кода VBE?

Так как  в последнее время начал писать большой по объёму программный код с большим количеством процедур и функций хотелось бы чтобы весь код конкретной  процедуры или функции сворачивался и оставался только один заголовок.
Так сделано наверное во всех современных редакторах разных языков программирования - в 1С, C#  и многих других
Скрытый текст

Слева от программного кода есть иерархия при нажатию на элементы которой программный код может сворачиваться или разворачиваться.
Раз уж Майкрософт не заботится об этом нет ли каких-нибудь надстроек для такого усовершенствования?
Изменено: vikttur - 11.08.2021 09:43:14
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
А можно пожалуйста поподробнее, в чём заключается проблема?
Изменено: vikttur - 09.08.2021 13:32:57
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
Отдельное спасибо за функцию  DataSql !!!
Сам не догадался в функцию это загнать.
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 
Спасибо Большое Дмитрий всё работает !!!
Опять очень выручили !!!
Можно ещё вопрос на засыпку.

Хочу в Эксель реализовать что то типа Базы Данных.
Задумка такая есть порядка 12 Таблиц связанных между собой по Id.

Одна таблица главная, а остальные подчинённые.
При перемещении курсора по главной таблице в  11 подчинённых  таблицах вышеуказанным запросом ( Точнее немного видоизменённым, фильтр будет по уникальному id ) будут извлекаться данные.

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

Ниже приведен снимок примерного количества строк в таблицах. Главная Талица - Таблица 01




Дмитрий как считаете это будет жизнеспособная система или же система не справится с обработкой такого количества данных?

Изначально начинал эту тему с использованием запросов Power Query, но как оказалось после одновременного обновления2-х 3-х подчиненных запросов скорость заметно упала. И от данной затеи пришлось отказаться.

Теперь пробую реализовать данную затею с помощью RecordSet.
Было бы интересно услышать ваше мнение по данному вопросу.

В любом случае Спасибо за ваши ответы.
Изменено: Ермолов_А_П - 06.08.2021 17:10:40
SQL запрос с помощью ADODB.Recordset источник – именованная таблица excel
 

Добрый день Форумчане.

Недавно решал задачу с получением данных с помощью ADODB.Recordset   только источник был весь лист книги Эксель.

Есть ли возможность в запросе указывать источником не весь лист эксель а именованную таблицу?

Подскажите пожалуйста очень нужно !!!

В приложении файл с работающим запросом ко всему листу книги.

Макрос выполняется по кнопке "Фильтр" на Листе.

Результатом запроса является подсчёт количества строк в полученной таблице. Конечная цель конечно не простой подсчёт количества, я потом буду эти строки перебирать в цикле и анализировать.

Строка самого запроса

Код
sql_text = "Select f5,f6,f7,f8,f9,f10,f11 From [" & list_dannie & "$] 
Where (f9 > #" & Format(data1, "MM\/dd\/yy hh\:mm\:ss") & "#) and (f9 < #" & Format(data2, "MM\/dd\/yy hh\:mm\:ss") & "#)"


list_dannie – переменная типа String в ней хранится имя листа с которого происходит выборка данных.

table_istochnik  - переменная типа string  в которой хранится имя таблицы из которой нужно получить данные

Вопрос в том как использовать в запросе переменную table_istochnik вместо list_dannie .

Перепробовал кучу разных вариаций ничего не получается.

Может хотя бы подскажите в какую сторону "Рыть". И вообще возможно это или нет ?

Буду благодарен за советы.
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
Спасибо,
Добрый человек. (New Пользователь )
Изменено: vikttur - 07.08.2021 09:14:30
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
Дмитрий подскажите пожалуйста есть ли разница если получать данные не с текущего листа а с другого листа этой же книги.

У меня Ваш код почему то работает только если получаешь данные с текущего листа а если пытаешься получить данные с другого листа этой же книги то код не срабатывает.
Изменено: Ермолов_А_П - 05.08.2021 09:11:18
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
Всё проверил. Всё работает.

Очень выручил!!!
Спасибо за помощь !!!
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
В Изначальном варианте переменные обьявлялись так

Типа Variant  не было.
Сейчас переделал
 
Вставил ваш код
Код
sql_text = "Select f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19 From [" + list_dannie + "$] Where (f9 > " & Chr(34) 
& data1 & Chr(34) & ") and (f9 < " & Chr(34) & data2 & Chr(34) & ")"
и ничего не заработало.
Если не трудно выложите пожалуйста рабочий файл Эксель с программным кодом целиком.Может я что то не догоняю.
Спасибо
Изменено: vikttur - 07.08.2021 09:12:19
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
Здраствуйте.
К сожалению не работает.
Переменная Data1 и Data2 видятся как даты.
Проблема как мне кажется в том, что Данные в столбце f9 определяются как строка ....
Изменено: Ермолов_А_П - 05.08.2021 09:09:09
Power Query и Оператор If-Then-Else
 
Цитата
PooHkrd написал:...
Спасибо большое !!!
То что нужно !!!
Изменено: Ермолов_А_П - 05.08.2021 09:56:31
Фильтр и Сортировка ADODB.Recordset по полю типа дата
 
 Добрый день форумчане.

У меня возникла проблема в реализации следующей задачи.
Нужно реализовать выполнение программного запроса «в коде vba» выполняющего фильтрацию по определённым полям и помещение его в переменную, для дальнейшего перебора значений там же в коде VBA.

Файл с примером во вложении.
В  файле есть 2 листа  «Фильтр_По_Строкам» и «Фильтр_По_Дате». Фильтр по строкам отрабатывает а фильтр по дате нет.
Для решения данной задачи воспользовался объектом Recordset из библиотеки Microsoft ActiveХ
Фильтр по текстовым полям отрабатывает нормальн А когда пытаюсь отфильтровать по полю типа «Дата»  находящимся в промежутке между дата1 и Дата2. Фильтрация не отрабатывает

Моё предположение что поля таблицы по которой происходит фильтрация видятся в текстовом формате поэтому синтаксис
Код
Where (f9 > " & Chr(34) & data1 & Chr(34) & "),
Не отрабатывает.

Подскажите пожалуйста правильный синтаксис для запроса с фильтрацией по дате.
Также подскажите можно ли данный запрос отсортироватьпо заданным полям.

Пример работающего запроса с фильтром по текстовым полям.
Код
«sql_text = "Select f15,f17,f18,f19,f21 From [" + list_spiski + "$] Where (f18 LIKE " & Chr(34) & nomenklatura & Chr(34) & ")
 and (f19 LIKE " & Chr(34) & nabor & Chr(34) & ") and (f21 LIKE " & Chr(34) & flus & Chr(34) & ")"»

list_spiski – Имя листа
nomenklatura – переменная в которой хранится первое условие фильтра. Данные берутся с ячейки на листе.
nabor – переменная в которой хранится второе условие фильтра. Данные берутся с ячейки на листе.
flus – переменная в которой хранится третье условие фильтра. Данные берутся с ячейки на листе.

Пример Неработающего запроса с фильтром по полю типа Дата
Код
sql_text = "Select f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19 From [" + list_dannie + "$] 
 Where (f9 > " & Chr(34) & data1 & Chr(34) & ") and (f9 < " & Chr(34) & data2 & Chr(34) & ")" 

В тексте после получения отфильтрованной таблицы не происходит никаких операций так как основная задача это правильно написать текст запроса. Чтобы получить отфильтрованную и отсортированную таблицу.

Всем заранее спасибо. Жду советов

Для работы макроса нужно зайти в меню



И включить библиотеку

  Либо более раннюю версию 2.x
Изменено: Ермолов_А_П - 04.08.2021 04:21:41
Power Query и Оператор If-Then-Else
 
Здраствуйте.
Суть задачи от количества столбцов не меняется
Нужно именно решение типа
Код
If a=1 then                        
       Шаг 1 (Строки с примененным фильтром по столбцу "Имя" )                
   Else                              
       Шаг2 (Удалённый Столбец "Фамилия")
Шаг3
Шаг4
Шаг5
ит.д.
.....
in  
   .....


Изменено: Ермолов_А_П - 05.08.2021 09:55:45
Power Query и Оператор If-Then-Else
 
Цитата
DrillPipe написал:
В зависимости от задачи, можно и аналог switch сделать, а не мастерить сложные конструкции из if-then-else
Добрый день.
Посмотрел Функцию switch.
И это не то что мне нужно.
Как я понял функция switch может вернуть одному конкретному столбцу один из вариантов значений в зависимости от условия.
Мне нужно в зависимости от условия не присваивать значение какому то конкретному столбцу а выполнить тот или инной шаг в списке применённые шаги

Допустим -

Код
If a=1 then                        
       Шаг 1 (Строки с примененным фильтром по столбцу "Имя" )                
   Else                              
       Шаг2 (Удалённый Столбец "Фамилия")
Шаг3
Шаг4
Шаг5
ит.д.
.....
in  
   .....
Изменено: Ермолов_А_П - 02.08.2021 14:04:05
Power Query и Оператор If-Then-Else
 
Весь текст задачи в тему вставить?
Изменено: Ермолов_А_П - 05.08.2021 09:54:51
Power Query и Оператор If-Then-Else
 
[Добрый день уважаемые форумчане!!!

Хотел задать вопрос по поводу оператора If Then Else в запросе Power Query
Очень нужно сделать работающий код  “If Then Else “ как на vba  или ином языке. Принцип везде одинаков.

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

Есть исходная таблица. Есть ячейка условия с выбором «1» или «0». В зависимости от заданного  условия «1» или «0» в итоговом запросе происходит фильтрация по полю Флаг.
Ответы типа задать параметр и через него передавать в запрос «1» или «0» не устраивают. Так как задача не настоящая в реальных задачах есть условия не только фильтрации а допустим выполнять определенную операцию – шаг в коде запроса или нет.

Например -
Если истина  тогда -  Фиильтровать по определённой  колонке
Иначе  - удалить определённый столбец и всё в таком духе.

С обычной фильтрацией я конечно бы разобрался. Нужен именно синтаксис «If Then Else» .

Код типа
Код
If a=1 then
        let    Источник =…,    #"Измененный тип" …,  
        #"Строки с примененным фильтром" ..in  
       #"Строки с примененным фильтром"
else
        let    
        Источник = …    #"Измененный тип" = …,    
        #"Удалённый Столбец"
      = Table.SelectRows(#"Измененный тип", each [Флаг] = 0)in    #" Удалённый Столбец  "

Не устраивает !!!!
Т.е. если выполняется условие то выполняется один запрос включающий много шагов. Иначе выполняется другой запрос так же состоящий из нескольких шагов.
Этот вариант я приложил. Код можете посмотреть в приложенном файле.

Он тоже не устраивает если много параметров по которым происходит выбор условий  то код становится просто монструозным и не читабельным. Да и в принципе его тяжело создать а иногда и невозможно.


1 Пункт)
Нужен код типа
Код
let    Источник =…,    
#"Измененный тип" …,                
If a=1 then                        
        #"Строки с примененным фильтром" ..                
   Else                              
       #"Удалённый Столбец"
…
#"Сортированные строки"
#"Переупорядоченные столбцы"………
in  
#"Строки с примененным фильтром"


2 Пункт )
Также Хотелось при таком написании  чтобы каждый шаг виделся так же в редакторе кода, чтобы оставалась возможность удобно просматривать и редактировать код.
Если же в оператор Then и Else прописывать целиком весь запрос то шаги этих запросов не видны и в сложном запросе где много шагов его невозможно будет отлаживать.

Если откроете во вложенном файле запрос «Запрос_Основной» то увидите следущую картину

А в «Применённые шаги» хотелось бы видеть именно все шаги

Например

Повторюсь пример простой и шагов мало но когда запрос сложный где много условий и шагов хотелось бы в условие не записывать весь запрос целиком а выбор конкретного шага в этом запросе в зависимости от условия !!!

Буду ОЧЕНЬ БЛАГОДАРЕН  если кто-нибудь откликнется и поможет решить такую сложную для меня задачу !!!

PS
Прошу прощения что без картинок так и не разобрался как их прикрепить вместо них сайт отображает много-много текста. Хотя при редактировании темы картинки отображаются корректно.
Как ссылаться на отдельную ячейку строки по индексу в запросе Power Query?
 
Цитата
PooHkrd написал:
1= Table.AddColumn(#"Добавлен индекс", "Дата2", each #"Добавлен индекс"[Дата]{[Индекс]-1}?)
Большое Спасибо !!!
Как ссылаться на отдельную ячейку строки по индексу в запросе Power Query?
 
Смысл в том чтобы получать значения с ячеек по индексу.
Если в новый столбец присвоить значение тупо другого столбца то присвоится значение с другого столбца но с этой же строки.

Пример
Код
= Table.AddColumn(#"Добавлен пользовательский объект", "Дата3", each [Дата]) 
.
Мне же нужно получить значение с нужной мне строки.
Пример
Код
= Table.AddColumn(#"Добавлен индекс", "Дата2", each {[Индекс]-1}[Дата]

В Данном случае я добавил в запросе столбец индекса а потом в стобец Дата2 хочу получить значение из столбца Дата Но из предидущей строки.
Смысл не важен можно получить значение из следующей строки суть вопроса получение значения из другого столбца по индексу строки.
пробовал всяко и
                             = Table.AddColumn(#"Добавлен индекс", "Дата2", each [Дата]{[Индекс]-1})
Пишет ошибку и всё.

Синтаксис нарыл в интернете по ссылкам
http://excel-inside.pro/ru/blog/2018/07/13/%D0%B0%D0%B1%D1%81%D0%BE%D0%BB%D1%8E%­D1%82%D0%BD%D1%8B%D0...
https://needfordata.ru/blog/kak-ssylatsya-na-otdelnuyu-yachejku-tablitsy-v-power-query

Пример файла прикреплён.
Power Query и Оператор If-Then-Else ?
 
У меня тот же вопрос. Есть код

let
 
   
   Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
   #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([ДАТА] <> "Итог")),
   #"Измененный тип" = Table.TransformColumnTypes(#"Строки с примененным фильтром",{{"Мастер", type text}, {"Водитель", type text}, {"ДАТА", type date}, {"№ Разгрузки", Int64.Type}, {"Наименование сырья", type any}, {"Масса поставщика, кг", Int64.Type}, {"Вес погрузчика", Int64.Type}, {"Погрузчик + поддон, кг#(lf)( весы 10 т)", Int64.Type}, {"Масса брутто, кг#(lf)( весы 10 т)", Int64.Type}, {"Масса брутто, кг#(lf)( весы 3 т)", Int64.Type}, {"Разница весы 10т- весы 3т", Int64.Type}, {"% погрешности", type number}, {"Масса тары, кг", Int64.Type}, {"Примечания", type text}}),
   #"Замененное значение" = Table.ReplaceValue(#"Измененный тип",null,0,Replacer.ReplaceValue,{"Масса поставщика, кг"}),
   #"Замененное значение1" = Table.ReplaceValue(#"Замененное значение",null,0,Replacer.ReplaceValue,{"Вес погрузчика"}),
   #"Замененное значение2" = Table.ReplaceValue(#"Замененное значение1",null,0,Replacer.ReplaceValue,{"Погрузчик + поддон, кг#(lf)( весы 10 т)"}),
   #"Замененное значение3" = Table.ReplaceValue(#"Замененное значение2",null,0,Replacer.ReplaceValue,{"Масса брутто, кг#(lf)( весы 10 т)"}),
   #"Замененное значение4" = Table.ReplaceValue(#"Замененное значение3",null,0,Replacer.ReplaceValue,{"Масса брутто, кг#(lf)( весы 3 т)"}),
   #"Строки с примененным фильтром1" = Table.SelectRows(#"Замененное значение4", each [ДАТА] >= DateTime.Date(Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0}[Дата_1]) and [ДАТА] <= DateTime.Date(Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0}[Дата_2])),
   #"Строки с примененным фильтром2" = Table.SelectRows(#"Строки с примененным фильтром1", each [#"Масса брутто, кг#(lf)( весы 3 т)"] >= Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0}[Масса_1] and [#"Масса брутто, кг#(lf)( весы 3 т)"] <= Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0}[Масса_2])
   
in
   #"Строки с примененным фильтром2"


После операции "Строки с примененным фильтром2" должна быть проверка
                            Если "Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0} <>null"
                                                 тогда фильтруем опр поле по значению "Excel.CurrentWorkbook(){[Name="Таблица_фильтр_1"]}[Content]{0} "
                                                           иначе ничего .
Но что-то  с помощью  спецификации языка я не могу понять как это сделать. И по коду представленному Андрей VG к сожалению не могу понять как мне это реализовать.

Суть в том чтобы фильтровать данные из исходной таблицы по дате, по массе и если введен номер разгрузки то и по нему иначе отображать все разгрузки/
Подскажите мне пожалуйста Недалёкому )))  
Изменено: Ермолов_А_П_24_05_1777 - 14.05.2018 09:12:00
Страницы: 1
Наверх