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

Страницы: 1 2 След.
создание вспомогательного списка в каждой строке, each _ выражения в power qwery
 
Спасибо)
создание вспомогательного списка в каждой строке, each _ выражения в power qwery
 
Форумчанам Привет !

Вот упрощенный пример моей проблемы. Нужно получить значение-оценку, которая основывается на нескольких полях в строке. Эти 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
Изменено: Alexey795 - 18.11.2021 15:14:41
Как получить желаемое отображение номера телефона
 
Вау! Идеально.
Спасибо
Как получить желаемое отображение номера телефона
 
Привет всем! Кто знает как добиваться вот такого отображения телефона?
375 (44) 123 45 67 или 375(44) 1234567

После применения кастомного формата появляются ненужные пробелы.
??? (??) ??? ???? => ??? (??) ? ??? ???
Как будто пытается разбить на тысячи.
Запрет вставлять ячейки при копировании, вставить только значения
 
Спасибо.
Если попытаться заполнять таблицу руками, скрипт отменит написанное и вставит пустоту.
Оставить только дублирующие строки по наименованию и упорядочить
 
Если я правильно понял, то 1. выделить всю нужную колонку 2. Главная - Стили - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения 3. Данные - Сортировка и фильтр - Сортировка - Добавить уровень +( Добавить уровень второй раз) 4. на обоих уровнях выбрать столбец с данными в параметре "Столбец" 5. на первом уровне в параметре сортировка развернуть список и вместо "Значения ячеек" выбрать "Цвет ячейки". Думаю есть и другие способы.
Можно ли применить динамические имена в условном форматировании
 
Пользуюсь динамическими таблицами. В "Применяется к" можно указать только абсолютные ссылки. Но умная табл. сама скорректирует значения, если вставить строку. Проверил сейчас: в случае, если правило применяется к колонке(рис.)(#2,3,4) или двум(#1) - все работает. Но последнее правило(#5) все таки глючит. Оно ссылалось на всю площадь области значений. При чем пока писал удавалось добиться, чтобы и оно работало, но после переоткрытия файла и итогового тестирование опять заглючило.
Мне кажется, тут(в случае дин.табл.) главное, чтобы было указано ровно вся область от первой до последней строчки, тогда в умной таблице срабатывает какой-то обработчик. Это напоминает как работает авторастягивание формул в таких таблицах. Если настройка включена, но хотя бы в одной ячейке неформула, авторастягивания не произойдет. На обычном листе (без динам.табл.) не кому за этим следить.
Изменено: Alexey795 - 12.10.2020 17:36:46
PQ. Преобразование переноса строки
 
статья и видео на близкую тему из раздела Приемы https://www.planetaexcel.ru/techniques/7/7615/
Запрет вставлять ячейки при копировании, вставить только значения
 
Добрый день!

Пользователи таблицы забывают при копировании вставлять как значение. И своим копированием ячеек затирают выпадающие списки и условное форматирование в этой таблице. После их работы файл как после бомбежки - по всей таблице сотни кусочков старых правил( По смыслу должно быть так, если пользователь сознательно меняет заливку - то у него должна быть такая возможность. Но если он копирует диапазон - то обязан вставить его как значения. Я пытался защитить лист от форматирования, но для вставки ячеек это не оказалось преградой.
Кто-нибудь знает как можно бороться с этой проблемой. Можно ли средствами VBA отлавливать вставку ренжа и чтоб это надежно работало в пределах конкретной умной таблицы?
Неверная ссылка вперед или ссылка на неоткомпилированный тип
 
Код
'работает
Set a = Workbooks(название_файла)

'работает
Set a = Workbooks(название_файла).Worksheets(название_листа)

'не работает
Set a = Workbooks(название_файла).Worksheets(название_листа).ListObjects(название_таблицы)

У меня такая ошибка возникает при сохранении объекта-таблицы в переменную.

Проблема очень существенная, из-за этого весь процесс буксует( Как чинить не ясно.
Создал "микро-таблицы" на разных листах, попробовал к ним обратится - и такая же ошибка.
Изменено: Alexey795 - 06.10.2020 12:33:22
бесконечная загрузка запроса Power Query, данные не попадают в модель данных
 
Web-Zahar, я избавился от проблемы, только когда переустановил офис
Определить номер строки внутри умной таблицы, зная адрес ячейки на Листе
 
Долго не мог понять, что это не дефис, а минус. Думал какая-то конструкция, которую я никогда не видел)
Сейчас понял: находим номер 1-го столбца родительской таблицы и вычитываем это из номера колонки на листе.
Или из номера строки на листе вычесть номер строки заголовка.
Спасибо
Определить номер строки внутри умной таблицы, зная адрес ячейки на Листе
 
Цитата
БМВ написал:
зачем по всем листам гонять, если и так понятно на каком может быть таблица.
согласен, поправлю. спасибо
Определить номер строки внутри умной таблицы, зная адрес ячейки на Листе
 
Всем привет!

Когда-то я сделал для себя функцию, которая возвращает информацию о выделенной ячейке. Название Книги,Листа, (Таблицы,Колонки если есть).
Для тех задач ее вполне хватало. Но ни тогда ни сейчас я не могу придумать как получить информацию к какой строке таблицы принадлежит ячейка. Тогда я это дело оставил, сейчас появилось потребность уметь определять номер элемента в диапазоне.
Функция возвращает словарь. Хочу расширить словарь, чтобы под ключом "n_row" сохранять номер строки при условии, если ячейка "принадлежит" какой-нибудь таблице.
Пробовал использовать .parent , но там возвращает в качестве родителя Лист, а не умную таблицу или колонку.
Подозреваю, что это не самый удобный способ решения задачи. Если есть способ короче, подскажите пожалуйста.
В файле есть моя функция, таблица и пример правильных ответов.
Список из номеров месяцев между двумя датами
 
Осенило. Тестирую.
Код
// в исходной таблице две колонки c датами [A] и [B]
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],

    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"A", type datetime}, {"B", type datetime}}),

    #"Вставлено: месяц.A" = Table.AddColumn(#"Измененный тип", "Месяц.A", each Date.Month([A]), Int64.Type),

    #"Вставлено: месяц.B" = Table.AddColumn(#"Вставлено: месяц.A", "Месяц.B", each Date.Month([B]), Int64.Type),

    #"Добавлен пользовательский объект" = Table.AddColumn(#"Вставлено: месяц.B", "Мес", each 
        if [Месяц.A]>[Месяц.B] then List.Combine({{[Месяц.A].. 12},{1.. [Месяц.B]}}) else {[Месяц.A] .. [Месяц.B]}),

    #"Развернутый элемент Пользовательский" = Table.ExpandListColumn(#"Добавлен пользовательский объект", "Мес"),

    #"Другие удаленные столбцы" = Table.SelectColumns(#"Развернутый элемент Пользовательский",{"A", "B", "Мес"})

in
    #"Другие удаленные столбцы"
Изменено: Alexey795 - 21.05.2020 20:30:32
Список из номеров месяцев между двумя датами
 
Приветствую всех!

Прошу посоветовать как красиво решить задачу. Может кто-то реализовывал подобное
Введена дата начала и завершения контракта, нужно получить в ячейку список номеров месяцев.
примеры:
#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}

осложнение:
#2020/12/05# - #2021/01/31# - - - - - > {12,1}
PQ. Число рабочих дней дней между двумя датами в колонку
 
Андрей VG, Спасибо! Все получилось.
PQ. Число рабочих дней дней между двумя датами в колонку
 
Добрый день!

Задача:
Есть таблица, в ней две колонки с датами. Разница между датами в одной строке может быть от 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  - это значение в формате дата, нижний и верхний край диапазона соответственно.
сам код ориентировочный и не должен работать
бесконечная загрузка запроса Power Query, данные не попадают в модель данных
 
Файл - Учетная запись - Сведения о пользователе
А там ни у кого из вас не пишет про "ошибку учетной записи"? Потому что я переустанавливал Excel два раза и все работало как надо. Но, когда начинается этот глюк с запросами, там также появляется "ошибка учетной записи".
бесконечная загрузка запроса Power Query, данные не попадают в модель данных
 
Цитата
romma написал:
Абсолютно та же проблема, причем именно в Excel с последними обновлениями версия 1910 (сборка 12130.20344 нажми и работай) .
Моя версия 1910 (сборка 12130.20272)
бесконечная загрузка запроса Power Query, данные не попадают в модель данных
 
На след день проверил на рабочем месте, запрос из моего файла запустился с первого раза без изменений.
Дома попробовал запустить быстрое восстановление офиса из панели управления windows - не помогло.
Скачал ваш файл, продублировал запросы. Сохраненные вами запросы работают исправно, даже, если вношу свои изменения и сохраняю, даже пока виснут продублированные мной ваши исправные запросы.
На втором запросе убрал галочку "Добавить эти данные в модель данных", прошли изменения, затем попытался обратно поставить галочку и загрузить - бесконечная загрузка. При это в самом редакторе все в порядке, ни на что не ругается.
И последний эксперимент: Открыл по новой скачанный файл => На первом запросе выбрал "только подключение" => обновилось=>обратно поставил "Таблица" => первые 100 строк и зависание.
Теперь понятно, что проблема чисто у меня, и в файле она не сохраняется. А вот что делать с этим не понято.
бесконечная загрузка запроса Power Query, данные не попадают в модель данных
 
Приветствую всех!

Столкнулся с проблемой с домашним Excelем. Power query странно себя ведет.
Далее пошагово:

для чистоты эксперимента я создал элементарный столбец с данными - арифметическую прогрессию от 1 до 555 и нажал загрузить "Из таблицы". Ячейки превратились в умную таблицу с заголовком "Столбец1", открылось окно PQ. Все 555 строк можно просмотреть в редакторе запросов.
Нажимаю "Закрыть и загрузить", закрывается окно PQ, на панели запросов справа бесконечно крутится кружок загрузки и ничего не происходит. Процессор в диспетчере задач в это время отдыхает. Если открыть запрос, он стает ругаться что "Невозможно изменить запрос, пока он обновляется."

Прерываю обновление, сохраняю первые 5 строк, загружаю в таблицу на новом листе. Загрузка завершается успешно до конца.
Редактирую запрос, сохраняю теперь 105 верхних строк, нажимаю загрузить, на листе появляется 100 номерков и в 101 ячейке (102-ая строка Икселя) отображается многоточие и бесконечно вращается кружок загрузки запроса. Аналогично происходит со всеми созданными запросами в остальных файлах, Старые запросы работают как раньше.

Есть ли у кого какие идеи с чем это связано и как это перебороть (перед тем как переустанавливать весь офис). Прикреплю файл, может запрос из этого файла некорректно сработает и на других машинах.
Изменено: Alexey795 - 05.12.2019 18:49:31
Повышение заголовков Power Query без повышения, работа с заголовками таблицы при загрузки данных с помощью power query
 
Привет, Планета.

Попрошу у Вас совета по следующей проблеме.
Есть папка с файлами-выгрузками с одинаковой структурой, которую я не могу менять. В каждом файле таблица-диапазон.
Строится запрос PQ, который объединяет все в одну таблицу. Пример запроса в файле с примером. При этом есть потребность, чтобы напротив каждой строчки было указано из какого файла взята эта строка и когда тот файл был создан. То есть +две колонки вначале таблицы [Имя] и [Дата].
В результате удается получить эти данные в нужные колонки.

Но проблема начинается здесь.
В запросе применяю команду "повысить заголовки" и она "затирает" правильные заголовки в первых двух столбцах значениями из первой строки. В то время как в остальной часть таблицы значения из первой строки справедливо занимают места заголовков. Из-за того, что заголовки первых двух столбцов могут меняться, я не смогу больше  к ним обращаться и работать дальше с таким запросом

а. Есть ли способ как-нибудь по хитрому расправится с заголовками?
б. Может есть другой способ добиться того, чтобы каждая строка с данными была "промаркирована" откуда она пришла (без изменения структуры выгрузки исходных файлов).
Изменено: Alexey795 - 17.03.2019 05:00:52
Настрой ленты в редакторе запросов Power Query, на ленте нет вкладки управление параметрами
 
жаль что на рабочем месте не смогу обновить и собственно использовать(
Настрой ленты в редакторе запросов Power Query, на ленте нет вкладки управление параметрами
 
Добрый день!

По видимому многие решения в Power Query становятся удобнее,
если задавать в запросе "относительный путь" с помощью параметров.

"на вкладке Главная выбрать Управление параметрами - Создать параметр"

Пытался научится такому способу, но столкнулся с проблемой,
Что делать, если не получается найти вкладку на ленте в разделе Главная.
В пошаговых руководствах не затрагивается такой нюанс (например как с "что делать если в меню Ексель нет раздела "Разработчик" )

скриншот ленты
https://cdn1.savepice.ru/uploads/2018/11/22/3250171782268d03b2465cd655be3f5c-full.png
лента из пошаг. руков.
https://cdn1.savepice.ru/uploads/2018/11/22/ec3e3790c4750afeddf9625424e04b5a-full.png

может кто активно пользуется Power Query и сможет что-нибудь подсказать?
как настроить ленту или как найти эти команды без ленты
Изменено: Alexey795 - 22.11.2018 09:45:45
Как получить такой формат даты 2016_06
 
buchlotnik, спасибо! формула работает! разъяснение я тоже понял. благодарю за оперативность
Как получить такой формат даты 2016_06
 
Добрый день
подскажите пожалуйста в чем фокус
иксель убирает нижнее "_" между цифрами
как получить такой формат даты 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

версия excel 2016
Отображение в процессе работы макроса
 
Sub ttt()
   Range("a1") = "00:00:00"
   Do While CDate(Range("a1")) < CDate("00:04:59")
      Range("a1") = Range("a1") + CDate("00:00:01")
      Call lag
   Loop
End Sub

Sub lag()
   Dim a As Single
   a = Timer
   Do While Timer < a + 0.007
   Loop
End Sub
Изменено: Alexey795 - 27.08.2018 14:22:19
Преобразование времени из текстового формата в числовой макросом.
 
Спасибо!  все утро искал имено эту вещь
Преобразование времени из текстового формата в числовой макросом.
 
имеется массив (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 часов, чтобы потом получить сумму всех часов в разрезе каждого человека
Страницы: 1 2 След.
Наверх