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

Страницы: 1 2 След.
Умная таблица - быстро удалить столбцы, не содержащие данных
 
Добрый день!
Возникла задача - удалить из "умной" таблицы столбцы, все ячейки которых пустые (кроме, естественно, заголовка столбца).
В файле примера - нужно удалять столбцы с желтыми ячейками (выделение цветом - условно).
Возможны пустые ячейки там, где они должны быть заполнены (выделены красным, пример ошибки "ручного" заполнения) - подобные столбцы остаются.

Может, у кого-то есть готовый макрос на удаление столбцов умной таблицы?

Нашел такое:
Код
Sub SelectColumn()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Columns.Count
If WorksheetFunction.CountA(diapaz1.Columns(i).EntireColumn) = 0 Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1.Columns(i).EntireColumn
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1.Columns(i).EntireColumn)
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодного пустого столбца!"
Else
diapaz2.Select
End If
End Sub
https://exceltable.com/vba-macros/makros-udaleniya-pustyh-stolbcov
Знаний по VBA недостаточно - замена "А1" на "А2" и "i=1" на "i=2" задачу, естественно не решила...

Если задачу можно быстро решить без VBA - прошу поделиться способом.
Работа функции СУММЕСЛИМН, аргументы из внешнего файла
 
Добрый день!
Столкнулся с проблемой - не понятна работа функции СУММЕСЛИМН, если диапазоны находятся в другом файле.
Если файл-источник, из которого берется часть аргументов ("диапазон_суммирования", "диапазон_условия1", "диапазон_условия2", "диапазон_условия3"), открыт, то функция в рабочем файле отрабатывает корректно.
Если файл-источник, из которого берутся диапазоны, закрыт, то при попытке сделать пересчет рабочего файла в ячейках, где используется функция СУММЕСЛИМН, появляется #ЗНАЧ!
В чем проблема, это особенность работы функции или что-то нужно поправить?
Изменено: jeka-irbis - 12 сен 2020 11:33:57
Очистка книги - не удаляются пустые строки на вкладках
 
Добрый вечер!
Столкнулся с проблемой - в файле порядка 70 вкладок, все имеют сотни пустых строк. Если переходить в каждую вкладку, то можно использовать кнопку "Удалить пустые строки", но в инструменте "Очистка книги" удаление пустых строк на всех вкладках не работает.
Объектная модель включена.
Надстройка 2020.1, Excel 2019 x64. На 2013 x64 также не работает.
PQ. Не удается преобразовать значение типа List в тип Text.
 
Доброе утро!
Собственно, проблема обозначена в названии темы.
Разбираюсь с решением предыдущей задачи. Возможность создания нескольких необходимых столбцов на основе предыдущего шага, оказывается, крайне полезная штука)

Возникла необходимость из частей одного столбца "собрать" другое значение. Если я правильно понимаю, в этом должна помочь функция Text.Middle, возвращающая определенный кусок исходного текста. Вроде все делаю по аналогии с другой текстовой функцией
Код
...{"Ссылки на РД",each Text.Combine([a],"; ")}...
но при
Код
...{"k",each Text.Middle([a], 5, 2)}...
выдает
Цитата
Expression.Error: Не удается преобразовать значение типа List в тип Text.
Что не так и как это побороть?
PQ. Доступность функции разделения столбца для разных версий Excel
 
Доброе утро!
Подскажите, кто знает, с какой версии PQ (или с какой версии Excel) доступна функция "Splitter.SplitTextByCharacterTransition"?
Получается, что в Excel 2013 с установленным отдельно PQ отсутствует соответствующий пункт в меню "Разделить столбец". В PQ Excel 2019 есть возможность разделения столбца по "переходу от одного типа символов к другому".

И как это можно обойти на старых версиях? Делить нужно ячейки типа AB1, N3, в некоторых ячейках цифра в конце может отсутствовать.
Проще, чем в 3 шага (создание столбца с буквами, столбца с цифрами, удаление исходного) это можно решить?
PQ. Возврат значения строки таблицы-фрагмента по условию
 
Добрый день!
Прошу помощи в решении очередной задачи.
С помощью группировки сворачиваю строки, в каждой вложенной таблице-фрагменте может быть от одной до нескольких строк.
Как по условию возвращать из таких таблиц-фрагментов только одну строку?

Реально такое в PQ сделать? С условиями немного знаком, но не сталкивался с их использованием в таблицах-фрагментах.
Изменено: jeka-irbis - 5 мар 2020 13:48:38
PQ. Преобразование чисел в текст с нулями в начале
 
Доброе утро!
Столбец с числами от 1 до произвольного двухзначного. Необходимо, чтобы для первого разряда перед цифрой ставился ноль (01, 02, ...09)
Задача вроде простая, но что-то с утра не догоняю)
Как посчитать в PQ суммы заказов по товарам?
 
Доброй ночи!
В книге Николая Павлова по PQ есть пример (во вложении). Как посчитать в PQ суммы заказов по товарам?
PQ. Замена текста по условию
 
Добрый день! Прошу помочь.

Есть таблица, в которой добавлен столбец "Исправление" (заполняется проверяющим).
Нужно добавить в PQ условие, по которому текст из ячейки столбца "Исправление" копировался бы с заменой в ячейку другого столбца (например, "Данные"). Если ячейка в "Исправление" пустая, то "Данные" остаются без изменений.
Застрял на формулировании условий
Код
...
    let
        Result
            if [Исправление] = null then
                #"Заменить на исправление" = ?
            else
                #"Заменить на исправление" = ?
    in
        Result
...
Это вообще правильная конструкция или должно быть как-то иначе?
Пожелание к командам фильтрации
 
Добрый вечер!
В PLEX есть группа команд на кнопке "Фильтровать". Все замечательно, но есть есть одно НО... Фильтрация привязана к полному содержимому ячейки. Нет возможности выполнять дальнейшие действия (переместить/скопировать/удалить отфильтрованное) по части содержимого.

Можно ли добавить подобный функционал в будущем обновлении?
Автоматизация экспорта таблиц из access в excel и наоборот
 
Всем всех благ в наступившем году!
Вопрос отражен в названии темы - есть ли готовые решения по экспорту таблиц ("одним махом") из Access в Excel и импорту обратно?

Решение "экспорт/импорт" в самом Access в случае десятков таблиц прошу не предлагать.
Загрузку данных в Excel через PQ - тоже, могут быть ошибки из-за использования зарезервированных имен.
Внешняя связь. Найти и уничтожить
 
Добрый день!
Был файл с некоторым количеством вкладок (20-30). Все вкладки файлов сохранил как отдельные файлы, а потом собрал в новом файле (для уменьшения размера и скрытия сведений о времени создания старого файла).

В новом файле появились ссылки в формулах на старый файл (в нем были перекрестные ссылки между вкладками).
Вручную заменил все ссылки на вкладки из старого файла на соответствующие вкладки в новом, искал по "[", названия вкладок в старом и новом файле одинаковые.
Но все равно, в сведениях о файле показывает связь со старым файлом.
Что еще нужно почистить?
PQ. Разделить текст на строки
 
Добрый вечер.
Колонка запроса содержит текст типа "А.В.С (9, 10, 11)".
Помогите, пожалуйста, это преобразовать в три строки из примера: "А.В.С (9), А.В.С (10), А.В.С (11)"?
Как разделять на строки - разобрался, а вот с заменой кусков текста пока туговато... Не догоняю, как применить https://docs.microsoft.com/ru-ru/powerquery-m/text-replace
-----------------------------
вопрос решил методом проб и ошибок, тему можно удалить
Изменено: jeka-irbis - 26 дек 2019 23:31:41
Перегруппировка столбцов (копирование данных между таблицами с разными шапками)
 
Добрый вечер!
Есть "старая" таблица с данными (несколько десятков столбцов и тысячи строк, простая однострочная шапка) и "новая", в которой есть только шапка и измененный порядок столбцов.
Как быстро скопировать (переместить) данные из старой таблицы в соответствующие столбцы новой? Если в новой таблице нет соответствующих столбцов, копирование столбца целиком не требуется.
Изменено: jeka-irbis - 17 дек 2019 21:23:01
Выгрузка данных из Excel в несколько txt-файлов.
 
Доброй ночи!
Есть рабочий макрос, выгружающий данные в txt-файл:
Код
Sub WriteSERVICE(control As IRibbonControl)
    Dim rowsDict As Object, wsUnload As Worksheet
    Dim s As String, ss As String, j As Long, n As Long
     
    Set wsUnload = ActiveSheet
    Set rowsDict = CreateObject("Scripting.Dictionary")
    If wsUnload.Cells(wsUnload.Rows.Count, 7).Value <> "" Then n = wsUnload.Rows.Count Else n = wsUnload.Cells(wsUnload.Rows.Count, 7).End(xlUp).Row
    For j = 5 To n
        If Not rowsDict.Exists(wsUnload.Cells(j, 4).Value) Then
            rowsDict.Add wsUnload.Cells(j, 4).Value, wsUnload.Cells(j, 4).Value
            s = s & "[InstanceData]" & vbCrLf & "SERVICE=" & wsUnload.Cells(j, 4).Value & vbCrLf & vbCrLf
        End If
    Next j
    
    ss = ThisWorkbook.Path & Application.PathSeparator & Cells(3, 2) & "_" & Format(Now, "dd-mm-yy-hh-mm-ss") & "_SERVICE" & ".txt"
    Open ss For Output As #1
    Print #1, s
    Close #1
    MsgBox "Файл сформирован: " & ss, 64, "Excel"
End Sub
Подскажите, как его модифицировать, чтобы появилась возможность разделения выгрузки данных, по отдельным файлам, прописанных с дополнительном столбце пути файла и его имени?
Проверка и формирование уникальных значений ячеек столбца таблицы
 
Добрый день.
Прошу совета или помощи в следующей ситуации.

Имеется сводный перечень "кодов элементов", полученных сборкой разных документов от разных исполнителей. В этом перечне необходимо проверить уникальность использованных "кодов", и, если попадаются неуникальные, сформировать новые значения, которые потом вернутся исполнителю для корректировки (как таблица соответствия старых и новых значений). Код формируется по определенным правилам и использовать "рандомные" функции или GUID-подобные вещи не представляется возможным.

1) Имеется код элемента. Если он до этого не встречался, то остается старое значение.
2) Если в ячейках выше код встречался, то текущему элементу необходимо присвоить окончание, допустим, "-01".
3) Если коды ниже повторяются неоднократно, то каждому последующему присваивать "-02", "-03" и т.д.

Можно ли выполнить требование по уникальности в пределах двух столбцов ("исходный код" - "новый код"), без макросов и вспомогательных столбцов для преобразований и фильтрации? В макросах не разбираюсь, а со вспомогательными столбцами не очень удобно.

Пример того, что должно получаться - во вложении. Для удобства "подсвечены" исходные дубликаты.
Сводные таблицы. Посчитать, сколько файлов за какими исполнителями закреплено
 
Доброй ночи!
Есть исходная таблица, где прописываются "исполнители", "Файл", с которыми они работают, и "количество страниц" для файлов.

Создаю по ней сводную таблицу - в столбцах "Исполнитель", в строках "Файл". Почему-то при выборе значения "Количество по полю Файл" выводится количество страниц. Т.е. в рабочих ячейках должны появляться только "1" (если файлу назначен исполнитель), а в "Общем итоге" должно считаться, сколько файлов у какого исполнителя - в чем мой косяк?
Или играет роль порядок столбцов в исходной таблице? Порядок - "Файл", "количество страниц", "Исполнитель"
Проблема функций "Операции с текстом"
 
Доброе утро!
Столкнулся со следующей проблемой.
Есть файл, созданный в Excel 2019 и в котором применены новые функции, в т.ч. и ОБЪЕДИНИТЬ.
На другой машине установлен Excel 2013, в котором функции 2019-ой версии становятся "неопознанными" : "_xlfn.TEXTJOIN".
Есть необходимость работы с данным файлом в обеих версиях Excel (условно говоря - дома и на работе). На обеих версиях Excel используется PLEX 2019.1.
Ячейки с "новыми" формулами в Excel 2013 не трогаются.

При попытке использования в Excel 2013 функций PLEX 2019.1 из группы "Операции с текстом" (в частности, удаление лишних пробелов) появляется следующее окно:

При этом ячейки, к которым должны применяться функции "Операции с текстом", не содержат формул.
Если нажать "Отмена", то действие выбранной функции может произойти (например, добавление символов в начало/конец строки) или не произойти (не происходит удаление лишних пробелов).

Возможно, подобное окно появляется при попытке выполнения и в других операциях PLEX, не проверял.
В чем все-таки проблема и лечится ли это?
Изменено: jeka-irbis - 27 ноя 2019 08:39:27
Аналог функции "ОБЪЕДИНИТЬ" для старых версий
 
Добрый день!
Вопрос - есть ли аналог функции ОБЪЕДИНИТЬ? На работе установлен Excel 2013, в нем этой функции еще не было.
И если нет - как можно создать пользовательскую функцию аналогичного действия?

/СЦЕПИТЬ - не подойдет, так как не все сцепляемые ячейки заполнены/
Генерация GUID в Excel
 
Добрый день!
Столкнулся с необходимостью генерации GUID. Пока генерю так:
Код
=СЦЕПИТЬ(ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;4294967295);8);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;65535);4);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;65535);4);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;65535);4);"-";ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;4294967295);8);ДЕС.В.ШЕСТН(СЛУЧМЕЖДУ(0;65535);4))
А есть ли еще способы генерации GUID в Excel? Интересны другие варианты.
Округление результата по условию
 
Добрый вечер!
Суть вопроса - возможно ли разное округление результата вычислений, в зависимости от значения целой части результата БЕЗ оператора ЕСЛИ и промежуточных ячеек?
Например, если оно менее 1, то до 2 знаков после запятой; если больше 1 и меньше 10 - до 1 знака, если больше 10 и меньше 100 - до целых значений, если больше 100 - до десятков).
Изменено: jeka-irbis - 4 ноя 2019 22:00:27
Сцепить в текстовую строку данные, пропуская пустые ячейки
 
Добрый день!
Суть проблемы в следующем.
Необходимо сформировать строку по данным ячеек, с использованием команды СЦЕПИТЬ (или "&").
Но в разных строках не все ячейки могут иметь значения, соответственно, необходимо предусматривать варианты сцепки для исключения лишних пробелов (и исключения знаков, например, запятых, если ячейки сцепляются через запятые, а одна из ячеек оказывается пустой).

Использование функции ЕСЛИ для каждого варианта заполненных и пустых ячеек не совсем удобно, т.к. формула становится длинной и нечитаемой.
Есть ли какой-то способ уйти от этого? Что-то вроде ВПР, или еще что...
Способ со вспомогательными столбцами тоже рассматривается.
Изменение размера диапазона в PLEX 2019.1
 
Добрый день!
Некорректно работает функция "Изменить размеры".
Необходимо строку преобразовать в столбец. Все настраиваю:



В результате, начиная с ячейки А2, дублируется строка А1:BJ1.Я что-то делаю не так или есть какой-то косяк в макросе?(
Изменено: jeka-irbis - 3 ноя 2019 11:48:08
Обновление оглавления, функционал
 
Возможно ли реализовать в будущих версиях PLEX функцию обновления оглавления?
Проблема в следующем - есть книга с увеличивающимся количеством вкладок. Кнопкой "Оглавление" на одноименной вкладке создается список вкладок книги, являющихся первым столбцов таблицы сбора статистики со всех вкладок.
По мере добавления вкладок возникает необходимость их добавления в "Оглавление" и учета с них нужной статистики. Без очистки данных сейчас это сделать невозможно.

Если есть способы обновления "Оглавления" без очистки вкладки - поделитесь секретом, пожалуйста.
Функция "TextMid", Не работает
 
Добрый вечер!
Пытаюсь "расчленить" строки типа "AA-BB-СС-DD-EE" по "-".
Для крайних членов использую "TextLeft" и "TextRight".
C вытаскиванием внутренних - проблема.Функция "TextMid" из данной строки не извлекает "BB", "CC", "DD".
В справке написано, что должен работать синтаксис "TextMid(Txt; Delim1; Delim2; N1; N2; IgnoreCase), однако в новой ячейке при этом пусто...
Можно, конечно, использовать комбинации "TextLeft" и "TextRight", но хотелось бы сразу "вычленять" нужное, без промежуточных ячеек.

Кстати, в оффлайн-спраке, на л.87, - опечатка. Вместо =TextLeft(Txt; Delim1; Delim2; N1; N2; IgnoreCase) должно быть =TextMid(Txt; Delim1; Delim2; N1; N2; IgnoreCase).
Функция "Очистка книги"
 
Добрый день!
Функция "Очистить" книгу" не удаляет пустые строки со всех вкладок? Или я как-то не так ею пользуюсь?

Делаю очистку исходного файла, после этого объединяю вкладки - в объединенной вкладке вижу пустые строки.
Plex 2018.4
Доработка макроса связи Excel и AutoCAD
 
Доброго времени суток!
Есть "Макрос для связи данных Excel и Autocad", ссылка на скачивание https://drive.google.com/file/d/1o3HVCvf-Cfv6copDCnhK1KatTAa3Ygzf/view?usp=sharing

Требуется доработка одной из его функций, "Select on screen" - чтобы по выбору блока (блоков) в одном из чертежей папки в Excel выгружались данные одноименных блоков из всех чертежей папки, в которой лежит открытый чертеж.


Текущая работа функции: в запущенном файле "Макроса..." нажимается кнопка "Select on screen", в открытом файле AutoCAD выбирается один/несколько блок(ов), в Excel автоматом выгружаются данные по выбранным блокам.

В "Макросе..." есть особенность - перед началом работы нужно в Preference (VB) выбрать библиотеку установленной версии AutoCAD.
Если будут предложения по оптимизации кода "Макроса..." - возможно, договоримся о доп.работе по оптимизации.

О готовности взяться или за дополнительными сведениями прошу писать в личку.
-----------------------------------------------------------------------------------------------------------------------------
На стадии обсуждения деталей
Изменено: jeka-irbis - 23 фев 2019 23:53:33
Связанные списки. Установка всех значений ячеек по одному из выбранных
 
Доброго времени суток)
Подскажите, пожалуйста, как осуществить заполнение данных для нескольких ячеек:

- на "листе 1" есть умная таблица 1 с заголовками;
- на "листе 2" - другая умная таблица 2 (с бОльшим количеством столбцов);
- некоторые столбцы таблицы 2 формируются из значений определенного столбца таблицы 1, соответственно для всех таких столбцов таблицы 2 созданы индивидуальные выпадающие списки.
Возможно ли сделать следующее, чтобы при выборе одного значения в любом из списков значения остальных списков устанавливались бы автоматом?
Изменено: jeka-irbis - 18 фев 2019 22:06:34
Копирование файлов с переименованием
 
Доброго времени суток!
В Excel сформирован список команд, которые копируются в BAT-файл, который затем запускается.
Есть предположение, что можно напрямую запустить команды через макрос.
Последовательность действий - выделить диапазон команд и нажать кнопку с макросом.
Но так как знаний по VBA практически нет (максимум - поправить готовый несложный макрос), просьба помочь с самим макросом.
Заранее спасибо!
Функция Text..., не работает?
 
 Функция Text... не работает c уровнями вложения?
Например,
S3=TextRight(TextLeft(AB3;" (");", ") выдает #ЗНАЧ!,

хотя вычислениеT3=TextLeft(AB3;" (")
S3=TextRight(T3;", ")
все нормально рассчитывает...
Надстройка 2018.4, Excel 2010 x86.
Страницы: 1 2 След.
Наверх