И снова Здраствуйте !!! Задачу которую мне было нужно решить, реализовал ещё 2 года назад. Сейчас решил выложить логику запроса который хотел реализовать. Вдруг кому нибудь да пригодиться !
Сделал простенькую табличку. На её основе сделал запрос. На Листе1 расположена Яейка с возможностью выбора значения - Фильтр_Иванов или Сортировка_Сумма. В зависимости от того что выбрано в ячейке запрос выполняет то или иное действие. А именно или фильтрует Таблицу по полю "ФИО" Значению "Иванов" или сортирует Таблицу по полю "Сумма" Смысла в данном примере особо нет, просто описать логику работы оператора. Пример очень простой думаю всем будет понятно !!!
Логика в моей задаче была намного сложнее. В таблице было много столбцов условий по полям было много. Шагов в запросе тоже было много. Но благодаря логике if then else я всё равно намного упростил запрос и сделал его короче и лаконичнее. Иначе он был бы просто монструозным и не читаемым.
Ещё раз спасибо - PooHkrd - за то что помог разобраться!!!
Нашел ещё одно интересное ограничение Excel. Ну как нашёл .... Excel опять стукнул меня по носу неизвестной ошибкой и я опять начал играть в игру найди 10 отличий ))) Суть в том что у меня порядка 13 таблиц расположено на одном листе по порядку слева направо. Из которых я и вытягиваю данные посредством sql запросов.
Между таблицами заложен резерв столбцов на всякий случай если таблицы будут расти по количеству столбцов. Начиная с 5 таблицы запрос просто отказался работать. Причем если в текущий программный код подставлял предидущие таблицы то код отрабатывал. Методом научного тыка нашёл причину. Оказалось данный запрос не работает если приходится вытаскивать данные из диапазона ячеек где крайний правый столбец по своему индексу больше чем 255 или 256 что то такое .
Перекидываю эту же таблицу на новый лист в диапазон с первого столбца и всё работает. Жаль конечно, у меня и так много листов занято. А тут ещё и под сами данные нужно будет использовать больше чем 1 лист. Но ничего не поделаешь буду плодить листы.
Есть такой вопрос. Нельзя ли усовершенствовать редактор кода VBE?
Так как в последнее время начал писать большой по объёму программный код с большим количеством процедур и функций хотелось бы чтобы весь код конкретной процедуры или функции сворачивался и оставался только один заголовок. Так сделано наверное во всех современных редакторах разных языков программирования - в 1С, C# и многих других
Скрытый текст
Слева от программного кода есть иерархия при нажатию на элементы которой программный код может сворачиваться или разворачиваться. Раз уж Майкрософт не заботится об этом нет ли каких-нибудь надстроек для такого усовершенствования?
Спасибо Большое Дмитрий всё работает !!! Опять очень выручили !!! Можно ещё вопрос на засыпку.
Хочу в Эксель реализовать что то типа Базы Данных. Задумка такая есть порядка 12 Таблиц связанных между собой по Id.
Одна таблица главная, а остальные подчинённые. При перемещении курсора по главной таблице в 11 подчинённых таблицах вышеуказанным запросом ( Точнее немного видоизменённым, фильтр будет по уникальному id ) будут извлекаться данные.
Вопрос насколько 11 таких запросов будут нагружать систему. Книга каждый год будет создаваться новая. Т.е. таблицы будут очищаться.
Ниже приведен снимок примерного количества строк в таблицах. Главная Талица - Таблица 01
Дмитрий как считаете это будет жизнеспособная система или же система не справится с обработкой такого количества данных?
Изначально начинал эту тему с использованием запросов Power Query, но как оказалось после одновременного обновления2-х 3-х подчиненных запросов скорость заметно упала. И от данной затеи пришлось отказаться.
Теперь пробую реализовать данную затею с помощью RecordSet. Было бы интересно услышать ваше мнение по данному вопросу.
Недавно решал задачу с получением данных с помощью 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 .
Перепробовал кучу разных вариаций ничего не получается.
Может хотя бы подскажите в какую сторону "Рыть". И вообще возможно это или нет ?
Дмитрий подскажите пожалуйста есть ли разница если получать данные не с текущего листа а с другого листа этой же книги.
У меня Ваш код почему то работает только если получаешь данные с текущего листа а если пытаешься получить данные с другого листа этой же книги то код не срабатывает.
Здраствуйте. К сожалению не работает. Переменная Data1 и Data2 видятся как даты. Проблема как мне кажется в том, что Данные в столбце f9 определяются как строка ....
У меня возникла проблема в реализации следующей задачи. Нужно реализовать выполнение программного запроса «в коде 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 – переменная в которой хранится третье условие фильтра. Данные берутся с ячейки на листе.
Пример Неработающего запроса с фильтром по полю типа Дата
В тексте после получения отфильтрованной таблицы не происходит никаких операций так как основная задача это правильно написать текст запроса. Чтобы получить отфильтрованную и отсортированную таблицу.
DrillPipe написал: В зависимости от задачи, можно и аналог switch сделать, а не мастерить сложные конструкции из if-then-else
Добрый день. Посмотрел Функцию switch. И это не то что мне нужно. Как я понял функция switch может вернуть одному конкретному столбцу один из вариантов значений в зависимости от условия. Мне нужно в зависимости от условия не присваивать значение какому то конкретному столбцу а выполнить тот или инной шаг в списке применённые шаги
Допустим -
Код
If a=1 then
Шаг 1 (Строки с примененным фильтром по столбцу "Имя" )
Else
Шаг2 (Удалённый Столбец "Фамилия")
Шаг3
Шаг4
Шаг5
ит.д.
.....
in
.....
Хотел задать вопрос по поводу оператора 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 Прошу прощения что без картинок так и не разобрался как их прикрепить вместо них сайт отображает много-много текста. Хотя при редактировании темы картинки отображаются корректно.
Смысл в том чтобы получать значения с ячеек по индексу. Если в новый столбец присвоить значение тупо другого столбца то присвоится значение с другого столбца но с этой же строки.
Пример
Код
= Table.AddColumn(#"Добавлен пользовательский объект", "Дата3", each [Дата])
. Мне же нужно получить значение с нужной мне строки. Пример
Код
= Table.AddColumn(#"Добавлен индекс", "Дата2", each {[Индекс]-1}[Дата]
В Данном случае я добавил в запросе столбец индекса а потом в стобец Дата2 хочу получить значение из столбца Дата Но из предидущей строки. Смысл не важен можно получить значение из следующей строки суть вопроса получение значения из другого столбца по индексу строки. пробовал всяко и = Table.AddColumn(#"Добавлен индекс", "Дата2", each [Дата]{[Индекс]-1}) Пишет ошибку и всё.
Источник = 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 к сожалению не могу понять как мне это реализовать.
Суть в том чтобы фильтровать данные из исходной таблицы по дате, по массе и если введен номер разгрузки то и по нему иначе отображать все разгрузки/ Подскажите мне пожалуйста Недалёкому )))