Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Как получить желаемое отображение номера телефона
 
Вау! Идеально.
Спасибо
Как получить желаемое отображение номера телефона
 
Привет всем! Кто знает как добиваться вот такого отображения телефона?
375 (44) 123 45 67 или 375(44) 1234567

После применения кастомного формата появляются ненужные пробелы.
??? (??) ??? ???? => ??? (??) ? ??? ???
Как будто пытается разбить на тысячи.
Запрет вставлять ячейки при копировании, вставить только значения
 
Спасибо.
Если попытаться заполнять таблицу руками, скрипт отменит написанное и вставит пустоту.
Оставить только дублирующие строки по наименованию и упорядочить
 
Если я правильно понял, то 1. выделить всю нужную колонку 2. Главная - Стили - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения 3. Данные - Сортировка и фильтр - Сортировка - Добавить уровень +( Добавить уровень второй раз) 4. на обоих уровнях выбрать столбец с данными в параметре "Столбец" 5. на первом уровне в параметре сортировка развернуть список и вместо "Значения ячеек" выбрать "Цвет ячейки". Думаю есть и другие способы.
Можно ли применить динамические имена в условном форматировании
 
Пользуюсь динамическими таблицами. В "Применяется к" можно указать только абсолютные ссылки. Но умная табл. сама скорректирует значения, если вставить строку. Проверил сейчас: в случае, если правило применяется к колонке(рис.)(#2,3,4) или двум(#1) - все работает. Но последнее правило(#5) все таки глючит. Оно ссылалось на всю площадь области значений. При чем пока писал удавалось добиться, чтобы и оно работало, но после переоткрытия файла и итогового тестирование опять заглючило.
Мне кажется, тут(в случае дин.табл.) главное, чтобы было указано ровно вся область от первой до последней строчки, тогда в умной таблице срабатывает какой-то обработчик. Это напоминает как работает авторастягивание формул в таких таблицах. Если настройка включена, но хотя бы в одной ячейке неформула, авторастягивания не произойдет. На обычном листе (без динам.табл.) не кому за этим следить.
Изменено: Alexey795 - 12 окт 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 - 6 окт 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 май 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 - 5 дек 2019 18:49:31
Повышение заголовков Power Query без повышения, работа с заголовками таблицы при загрузки данных с помощью power query
 
Привет, Планета.

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

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

а. Есть ли способ как-нибудь по хитрому расправится с заголовками?
б. Может есть другой способ добиться того, чтобы каждая строка с данными была "промаркирована" откуда она пришла (без изменения структуры выгрузки исходных файлов).
Изменено: Alexey795 - 17 мар 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 ноя 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 авг 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 часов, чтобы потом получить сумму всех часов в разрезе каждого человека
Отображение в процессе работы макроса
 
A и D это числа как в математике блин
если цикл выполнит команду 4 раза, то результатом будет значение_ячейки + 4
если команда +1 исполнится 104 раза, результатом будет значение_ячейки + 104

я всего лишь спросил, можно ли сделать так, чтобы отображалось то, как значение в ячейке меняется
т.е. в самой ячейке на листе иксель,
от числа A до значения D (это как пример)
чтоб в этой ячейке ПО ОЧЕРЕДИ появлялись значения число_А, число_B, число_C, число_D
пока макрос не остановится и не оставит итоговое значение
Отображение в процессе работы макроса
 
допустим есть цикл
самый простой. он делает +1 к значению в ячейке
в итоге значение в ячейке меняется от A до D
можно ли сделать так, чтобы выдавало не сразу "D" в ячейку, а "A" "B" "C" "D" по очереди
главное чтобы отображалось все будто я зажал кнопку F8

я пролистал пару раз. там про полоску прогресса и про форму.
это как раз соответствет заголовку. я себе сохранил статью.
но я пытаюсь спросить про другое
Страницы: 1 2 След.
Наверх