Вот упрощенный пример моей проблемы. Нужно получить значение-оценку, которая основывается на нескольких полях в строке. Эти n-полей будут собраны в список. Список будет использоваться два раза. Идея заключается в том, чтобы создать список 1 раз, использовать его два раза по переменной li, и сохранить только результирующее значение. Я могу решить проблему создав доп. столбец, где будет валяться этот список (шаг добавитьСписок). Затем добавить колонку с результатом (шаг попытка2). Затем удалить колонку со списками. Но может-быть кто-нибудь знает, как объявить и создать его прямо внутри шага вычисления результата (как в шаг итогОценка)?
Код
let
// как буд-то 1 строчка таблицы
Источник = Table.FromRecords ( { [ A = 1, B = 1, C = 1, D = - 2, E = 1 ] } ),
// чтобы работало нужны значения-цифры
#"Измененный тип" = Table.TransformColumnTypes (
Источник,
{
{ "A", Int64.Type },
{ "B", Int64.Type },
{ "C", Int64.Type },
{ "D", Int64.Type },
{ "E", Int64.Type }
}
),
// добавляю колонку, в которой храню списки
добавитьСписок = Table.AddColumn ( #"Измененный тип", "list", each { [A], [B], [C], [D], [E] } ),
// потыка 1 угадать синтаксис - неуспешная
итогОценка = Table.AddColumn (
добавитьСписок,
"оценка",
let
li = { [A], [B], [C], [D], [E] }
in
each
if List.MatchesAll ( li, each _ = - 2 ) then
"искл"
else if List.MatchesAny ( li, each _ = 1 or _ = 4 ) then
"проблема"
else
"ок",
type number
),
// вот так работает как задуманно, но используется нежелательный столбец [list]
попытка2 = Table.AddColumn (
добавитьСписок,
"оценка",
each
if List.MatchesAll ( [list], each _ = - 2 ) then
"искл"
else if List.MatchesAny ( [list], each _ = 1 or _ = 4 ) then
"проблема"
else
"ок",
type text
)
in
попытка2
Пользователи таблицы забывают при копировании вставлять как значение. И своим копированием ячеек затирают выпадающие списки и условное форматирование в этой таблице. После их работы файл как после бомбежки - по всей таблице сотни кусочков старых правил( По смыслу должно быть так, если пользователь сознательно меняет заливку - то у него должна быть такая возможность. Но если он копирует диапазон - то обязан вставить его как значения. Я пытался защитить лист от форматирования, но для вставки ячеек это не оказалось преградой. Кто-нибудь знает как можно бороться с этой проблемой. Можно ли средствами VBA отлавливать вставку ренжа и чтоб это надежно работало в пределах конкретной умной таблицы?
Когда-то я сделал для себя функцию, которая возвращает информацию о выделенной ячейке. Название Книги,Листа, (Таблицы,Колонки если есть). Для тех задач ее вполне хватало. Но ни тогда ни сейчас я не могу придумать как получить информацию к какой строке таблицы принадлежит ячейка. Тогда я это дело оставил, сейчас появилось потребность уметь определять номер элемента в диапазоне. Функция возвращает словарь. Хочу расширить словарь, чтобы под ключом "n_row" сохранять номер строки при условии, если ячейка "принадлежит" какой-нибудь таблице. Пробовал использовать .parent , но там возвращает в качестве родителя Лист, а не умную таблицу или колонку. Подозреваю, что это не самый удобный способ решения задачи. Если есть способ короче, подскажите пожалуйста. В файле есть моя функция, таблица и пример правильных ответов.
Прошу посоветовать как красиво решить задачу. Может кто-то реализовывал подобное Введена дата начала и завершения контракта, нужно получить в ячейку список номеров месяцев. примеры: #2020/04/01# - #2020/06/30# - - - - - > {4,5,6} #2020/05/12# - #2020/05/30# - - - - - > {5} #2020/01/31# - #2020/05/01# - - - - - > {1,2,3,4,5}
Задача: Есть таблица, в ней две колонки с датами. Разница между датами в одной строке может быть от 0 до 14 дней. Но нужно показать разницу учитывая только количество рабочих дней.
Таблица Основная_таблица колонка 1 - дата А колонка 2 - дата Б колонка 3 - прошло рабочих дней (целое)
Таблица мини_календарь колонка 1 - дата {от мин дата .. до максимальная дата} колонка 2 - день недели {1..7} колонка 3 - БУДНИЙ ДЕНЬ {0,1}
Пытаюсь решить: Создана таблица мини_календарь. В ней перечислены все даты от Min до Max и в доп колонке [БУДНИЙ ДЕНЬ] указано какой день считать рабочим. В каждую строку основной таблицы подставляю мини календарь. Теперь внутри каждой ячейке есть таблица... В каждой ячейке мини таблицу нужно отфильтровать на основе 2-х значений из текущей строки основной таблицы. След функция выдаст мне интересующий меня ответ List.Sum(#"отфильтрованный_мини_календарь")[БУДНИЙ ДЕНЬ])
Где стопорюсь: как получить таблицу в переменную #"отфильтрованный_мини_календарь"
Моя попытка:
Код
#"Прошло_рабочих_дней" =
Table.AddColumn(
#"Основная_таблица",
"прошло будних дней",
each (a),(b)=>
List.Sum(
Table.SelectRows(
мини_календарь,
each [дата] >= a
each [дата] <= b
)
[БУДНИЙ ДЕНЬ]
)
)
in
#"Прошло_рабочих_дней"
как применить стрелочные функции с двумя значениями a,b? a , b - это значение в формате дата, нижний и верхний край диапазона соответственно. сам код ориентировочный и не должен работать
Столкнулся с проблемой с домашним Excelем. Power query странно себя ведет. Далее пошагово:
для чистоты эксперимента я создал элементарный столбец с данными - арифметическую прогрессию от 1 до 555 и нажал загрузить "Из таблицы". Ячейки превратились в умную таблицу с заголовком "Столбец1", открылось окно PQ. Все 555 строк можно просмотреть в редакторе запросов. Нажимаю "Закрыть и загрузить", закрывается окно PQ, на панели запросов справа бесконечно крутится кружок загрузки и ничего не происходит. Процессор в диспетчере задач в это время отдыхает. Если открыть запрос, он стает ругаться что "Невозможно изменить запрос, пока он обновляется."
Прерываю обновление, сохраняю первые 5 строк, загружаю в таблицу на новом листе. Загрузка завершается успешно до конца. Редактирую запрос, сохраняю теперь 105 верхних строк, нажимаю загрузить, на листе появляется 100 номерков и в 101 ячейке (102-ая строка Икселя) отображается многоточие и бесконечно вращается кружок загрузки запроса. Аналогично происходит со всеми созданными запросами в остальных файлах, Старые запросы работают как раньше.
Есть ли у кого какие идеи с чем это связано и как это перебороть (перед тем как переустанавливать весь офис). Прикреплю файл, может запрос из этого файла некорректно сработает и на других машинах.
Попрошу у Вас совета по следующей проблеме. Есть папка с файлами-выгрузками с одинаковой структурой, которую я не могу менять. В каждом файле таблица-диапазон. Строится запрос PQ, который объединяет все в одну таблицу. Пример запроса в файле с примером. При этом есть потребность, чтобы напротив каждой строчки было указано из какого файла взята эта строка и когда тот файл был создан. То есть +две колонки вначале таблицы [Имя] и [Дата]. В результате удается получить эти данные в нужные колонки.
Но проблема начинается здесь. В запросе применяю команду "повысить заголовки" и она "затирает" правильные заголовки в первых двух столбцах значениями из первой строки. В то время как в остальной часть таблицы значения из первой строки справедливо занимают места заголовков. Из-за того, что заголовки первых двух столбцов могут меняться, я не смогу больше к ним обращаться и работать дальше с таким запросом
а. Есть ли способ как-нибудь по хитрому расправится с заголовками? б. Может есть другой способ добиться того, чтобы каждая строка с данными была "промаркирована" откуда она пришла (без изменения структуры выгрузки исходных файлов).
По видимому многие решения в Power Query становятся удобнее, если задавать в запросе "относительный путь" с помощью параметров.
"на вкладке Главная выбрать Управление параметрами - Создать параметр"
Пытался научится такому способу, но столкнулся с проблемой, Что делать, если не получается найти вкладку на ленте в разделе Главная. В пошаговых руководствах не затрагивается такой нюанс (например как с "что делать если в меню Ексель нет раздела "Разработчик" )
Добрый день подскажите пожалуйста в чем фокус иксель убирает нижнее "_" между цифрами как получить такой формат даты 2016_06 ? =ТЕКСТ(A2;"ГГГГ_ММ") возвращает 2016 6 =ТЕКСТ(A2;"ГГГГ_МММ") ==> 2016 06 =ТЕКСТ(A2;"ГГГГ+ММ") 2016+06 (с + и др знаками работает норм) =ТЕКСТ(A2;"ГГГГ + ММ") 2016 + 06 =ТЕКСТ(A10;"ГГГГ _ ММ") 2016дваПробела06 =ТЕКСТ(A7;"ГГГГ"&"_"&"ММ") 2016 6 =ТЕКСТ(A9;"ГГГГ&_&ММ") 2016& 06 помогите пожалуйста разобраться что происходит или как получить 2016_06
имеется массив (8 на 50), который содержит данные такого вида "7:00-17:00" то есть как таблица 8 стлб 50 стр, а в ячейках текстом записан проежуток "начало-конец"
необходио удобны способом подменить заначения вместо "7:00-17:00" сделать 10 (10 ччасов) вместо "8:30-:20:00" сделать 11,5 (11 с половиной часов ) то есть вычислить количетво часов
при этом крайне нежелательно создавать никаких листов и формул, а все вычислить в макросе на входе у меня идет массив с текстовой информацией (такого вида "7:00-17:00" ) на выходе ожидается такой же по размеру массив, но уже с числовой информацией
есть ли какая-нибудь функция, которая воспримет "18:35" как 18,5833 ,а "09:11" как 9,1833 и тогда я бы мог из "09:11-18:35" получить 9,4 часов, чтобы потом получить сумму всех часов в разрезе каждого человека
Вот допустим таймер. Если надо что цифры 05:00 поменялись на 00:01 так, чтоб я видел как мелькают промежуточные цифры. или чтоб полоса загрузки закрасила строку от 0% до 100% или чтоб я видел как квадратик перемещается по полям как в игре Снейк, а не телепортировался. (то есть чтоб не приходилось запускать макрос повторным нажатием 100 раз) любой такой пример подойдет
Задумка такая: по нажатии кнопки "обновить" из папки "Загрузки" открывается свежескачанный файл .xls его содержимое обрабатывается и прикрепляется в "основной" файл имя каждого нового загружаемого файла будет частично меняться, а адрес - нет имена похожи на это 4443_20180312_150003.xls 4443_20180226_003202.xls 4443_20180219_131050.xls 4443_год&мес&деньскачивания_рандомныецифры.xls
помогите сделать умный макрос, который будет облегчать жизнь и "угадывать" какой файл я хочу открыть по сути это всегда будет тот файл, который будет скачан "Сегодня"