Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Максим Зеленский
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 След.
VBA проверка на число (по заданному региональному стандарту)
 
#
13.10.2021 19:13:12
Привет всем.
Немного туплю, порыл форум, но никак не найду подходящего решения, или не могу протестировать... а VBA основательно приподзабыл уже :)

Есть три ячейки, в которых может быть написано число с десятичным знаком или без десятичного знака, причем может быть сохранено как текст или как число:
0.5true
0,5false
1true
нужен кусочек кода VBA для проверки содержимого ячейки
Если в ячейке записано целое число, без десятичного знака, то это не ошибка. Неважно, как оно сохранено - как текст или как число
Если в ячейке записано число со знаками после запятой, то если разделитель не точка - это ошибка.
Если в ячейке записано число со знаками после запятой, то если разделитель точка - это не ошибка.

Макрос должен работать в системе с любыми региональными стандартами. Иными словами, хороши только целые числа, и то, что может быть преобразовано в число, но при этом обязательно в нем разделитель - точка.

вот такой код не дает нормального результата, увы :(
Код
Sub tezt()
With Application
       .DecimalSeparator = "."
       .ThousandsSeparator = ","
       .UseSystemSeparators = False
End With

Debug.Print "0.5", IsNumeric("0.5")     ' false, должно быть true
Debug.Print "0,5", IsNumeric("0,5")     ' true, должно быть false
Debug.Print "1", IsNumeric("1")           ' true, всё в порядке

Application.UseSystemSeparators = True
End Sub


Идеи?
F1 творит чудеса
 
Перейти    
Функции Power Query, которые ведут себя неоднозначно, и вообще возомнили о себе
 
#
27.07.2020 14:24:44
Навеяно неоднократными вопросами об изменении порядка строк после выполнения функции, например этой
Цитата
Михаил Л написал:
Впору в курилке создать тему про функции PQ, которые не ведут себя однозначно при разных количествах строк.

хм... Насколько мне известно, все функции ведут себя однозначно, но порядок строк таблицы может меняться, если применена какая-то функция групповой обработки строк.

точно не гарантирует сохранение порядка строк:
  1. глобальная группировка
  2. удаление дубликатов
  3. слияние/join/merge (практически любой вариант слияния)
Скорее всего, не гарантирует порядок строк
  1. удаление дубликатов списка (List.Distinct) (но не проверял, х.з., не помню, почему мне так кажется, но кажется логичным).
  2. разворачивание структурного столбца в новые строки:
    • Table.ExpandRecordColumn (столбец записей),
    • Table.ExpandListColumn (столбец списков),
    • Table.ExpandTableColumn столбец таблиц)
Локальная группировка, по идее, должна сохранять порядок строк, но не сильно удивлюсь, если при правильно проведенной локальной группировке порядок вывода результата будет отличаться от начального.

Иногда с этим можно бороться, фиксируя/пересчитывая таблицу в памяти перед операцией (например, добавлением индекса).
Может, еще что-то вспомню
F1 творит чудеса
 
Перейти    
Подстановка кода из другой таблицы по неполному условию, только Power Query или DAX
 
#
17.07.2020 14:56:22
Давненько ничего не спрашивал, но тут что-то завис и не могу подобраться к решению

Есть таблица Данные, в ней много разных столбцов, в том числе столбцы Продукт, Тип, Канал. Есть таблица Справочник, в ней столбцы Продукт, Тип, Канал и Код.

Задача:
подтащить Код из Справочник в Данные, не используя в коде PQ или DAX возможные значения столбцов Продукт, Тип, Канал, так как справочник может более-менее меняться, и придется править код.
Решаться будет в Power BI, поэтому только Power Query или DAX. В реальной задаче столбцов с условиями до 10 в обоих таблицах.

Подвох:
В Данных столбцы проверки заполнены всегда, пустых значений в них нет.
В Справочнике не в каждой строке заполнены значения в столбцах Продукт, Тип, Канал. Например, может быть не заполнен столбец Тип, что означает "любой Тип". Или столбец Канал, что означает "любой Канал". Может быть не заполнено несколько столбцов. В конце концов, встречаются условия только по одному столбцу.
Логика подстановки должна быть такой, что сначала проверяются все полные соответствия, затем частично неполные (например, только отсутствующий тип), затем все остальные. В справочнике данные могут быть расставлены по приоритету, если это необходимо.
На картинке нагляднее. Там вместо пустых значений стоят *, но это не принципиально.


В общем, чтой-та я подвис. Способов перепробовал уже довольно много, ни один не стоит показа, так как не привел к результату.

ЗЫ Заполнить справочник всеми необходимыми сочетаниями - крайний вариант, очевидный и оставлен как самый хреновый. Но дополнить справочник какой-то информацией можно.
Изменено: Максим Зеленский - 17.07.2020 14:58:38
F1 творит чудеса
 
Перейти    
Когда появилась эта фича?, просто любопытно
 
#
18.06.2020 13:11:36
При создании умной таблицы из диапазона, в котором первая строка содержит (как в моем случае) дату или формулу, выскакивает такое сообщение:


давно эта фича появилась? я что-то не уследил, обычно всегда галочку убираю в таких случаях
F1 творит чудеса
 
Перейти    
Поиск формул ячейки, в которых встречаются константы
 
#
14.02.2020 13:42:36
Коллеги, день добрый.
Столкнулся с задачей описания методики расчёта разнообразных показателей. На входе у меня куча Excel-ек, в которых в разнообразном виде эти расчеты представлены.
Как положено, есть ячейки ввода, где только константа какая-то забита, есть ячейки с правильными формулами, а есть "подправленные" формулы, в которых используются неописанные константы.

Подскажите, пожалуйста, какой есть более-менее быстрый способ найти все ячейки, в которых есть формула, но при этом в формуле используются константы, например:
=СУММ(A:A)/3,141529
=A1*5/$B$2

Существует ли такой, или только глазками? Пока ничего сам не нашел.
F1 творит чудеса
 
Перейти    
Задачка
 
#
14.02.2020 13:34:37
Если кто-то скучает, поставьте числа вместо многоточий:
Цитата
В этом предложении цифра 0 встречается ... раз, цифра 1 - ... раз, цифра 2 - ... раз, цифра 3 - ... раз, цифра 4 - ... раз, цифра 5 - ... раз, цифра 6 - ... раз, цифра 7 - ... раз, цифра 8 - ... раз, цифра 9 - ... раз
Есть как минимум два решения :)
Я решения нашел, алгоритм не придумал кроме как перебор. Но он должен же быть! :) Вдруг кто его опишет? :)
F1 творит чудеса
 
Перейти    
[Работа] Создание выгрузки в CSV из данных листа Excel по хитрой схеме
 
#
27.07.2018 14:14:34
Всем привет.

Есть рабочая задача, за которую мне сейчас некогда взяться самому, поэтому переадресую сюда.

Есть лист в файле Excel достаточно простого формата (обычная неумная таблица), содержит помесячный прогноз и его корректировку в строках по сочетанию Код - Склад, плюс некие метаданные этой корректировки.
Также есть текстовый файл, содержащий часть исходной информации.
Из этого листа необходимо собрать информацию и по хитрой схеме создать текстовый файл определенного формата для загрузки в стороннюю программу (формально это CSV, но со строками нефиксированной длины, некоторые "ячейки" которого содержат JSON). Однако формат довольно детально описан.

Исходные файлы всегда разные, поэтому решение должно быть на VBA или на VBA+PowerQuery, работать с данными, находящимися в другом (возможно, открытом) файле.
Желательно создание небольших пользовательских форм.

Предварительное ТЗ и описание формата CSV вышлю по запросу - пишите в личку.

Подводные камни и минусы:
- придется изучить суть происходящего, некоторые аспекты мне самому пока не ясны до конца. При моем живом участии, конечно.
- формат CSV дурной, просто выгрузить табличку с листа не получится, придется формировать чуть ли не построчно.

Плюсы:
- Заказчик - я сам :)
- при необходимости формат входного Excel можно будет менять - по согласованию

Срок не "прям завтра", но разумно короткий.

Пишите, обсудим.
F1 творит чудеса
 
Перейти    
JavaScript UDF, Azure Machine Learning, Power BI Custom Visuals и интеграция с Microsoft Flow, в скором будущем - в Excel :)
 
#
08.05.2018 11:00:45
JavaScript UDF - уже работают в Office Insiders, а Azure ML, Flow и Power BI Custom Visuals - на подходе:
https://dev.office.com/blogs/azure-machine-learning-javascript-custom-functions-and-power-bi-custom-visuals-further-expand-developers-capabilities-with-excel
F1 творит чудеса
 
Перейти    
[Power Query] Получаем числовые форматы ячеек из файла Excel при помощи Power Query, есть такая функция
 
#
01.09.2017 13:36:23
В продолжение вот этой темы.

Как известно, Power Query берет из листа Excel только значения, все форматы (кроме формата даты) идут мимо.
То есть, например, если у вас значение ячейки A1 = 1000, при этом при помощи форматирования вы задали денежный формат с отображением знака $, а в соседней ячейке A2 у вас тоже 1000, только с отображением знака €, то для Power Query это два абсолютно одинаковых числа.

Стандартных средств для того, чтобы достать примененный формат ячейки при помощи PQ нет.
Если вы можете добраться до файла при помощи VBA или проставить нужные метки формата руками - вам повезло. Однако если такая история случается с большим количеством файлов (выгрузки из учетных систем, например), то придется уже повозиться. А если файлы хранятся где-то на веб-сервере, то их нужно сначала скачать, затем... ну вы поняли.

В общем, вот вторая готовая функция из проекта "Excel Formats to Power Query" - Excel.GetNumberFormats

Текущая версия 1.1.1 работает с одним (указанным явно по имени) листом и одним (первым или заданным по номеру как аргумент) столбцом, и в качестве результата возвращает данные с нужного листа с добавлением перед ними специального столбца, содержащего форматы ячеек.

То есть, из вот такого листа:


который при стандартной загрузке становится вот таким:


получаем вот такой вывод:

Далее уже полученные форматы можно парсить так, как душа пожелает.

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

Как пользоваться:

  1. Копируем код
  2. Создаем пустой запрос в PQ
  3. В редакторе заменяем код на скопированный -> получаем функцию.
  4. Скармливаем функции четыре (минимум два) параметра:
  5. Полный путь к файлу (включая имя файла) xlsx или xlsm (например, "C:\PQ\test.xlsm")
  6. Имя листа (например, "Лист2"), который хотим получить.
  7. необязательно Номер столбца (например, 1 или 5 - в соответствии с нотацией R1C1, то есть столбец А = 1, а столбец E = 5). По умолчанию 1, т.е. первый столбец, он же А.
  8. необязательно Параметр true/false - хотим ли, чтобы столбец с форматами был добавлен первым столбцом к данным, полученным с листа. По умолчанию true.
  9. Радуемся.
Последний параметр определяет, что мы получаем в дополнение к стандартному выводу функции Excel.Workbook:
  1. false: таблица из двух столбцов: rowIndex (0-based номер строки) и NumberFormat (собственно форматы ячеек)
  2. true/null/опущен: таблица, содержащая исходные данные из столбца Data, к которой первым столбцом (ColumnN.NumberFormat) прицеплены форматы.
Если не хотим копировать целиком код и пр. - можно использовать всегда свежую версию функции при помощи следующей строки:
Код
Expression.Evaluate(Text.FromBinary(Web.Contents("https://github.com/hohlick/Power-Query-Excel-Formats/blob/master/NumberFormats/Excel.GetNumberFormats.pq")), #shared)
В планах: добавление возможности импорта форматов сразу из нескольких листов (т.е. для каждого из заданных листов) и из нескольких столбцов (списком или ссылкой "как в Excel").
F1 творит чудеса
 
Перейти    
[Power Query] Добываем иерархию (вложенность) строк из файла Excel, есть такая функция
 
#
14.08.2017 16:58:06
Делюсь.

Есть такая проблема, что Power Query не берет форматы из ячеек листа, только сами значения.
Зачастую на это можно наплевать, но часто информация на листе организована в структуру - иерархию строк, которые "плюсиками" сворачиваются/разворачиваются.
Бывает, что разобрать эту структуру можно только получив/обозначив уровень такой иерархии где-то на листе, так как других признаков нет. А добыть номер уровня можно только при помощи VBA, или проставить руками.

В общем, говоря короче, наваял функцию для PQ, которая подтягивает к информации на листе еще и уровень иерархии строк, если таковая присутствует.
Пользуйтесь.

https://github.com/hohlick/Power-Query-Excel-Formats/blob/master/RowsOutline/ExcelWorksheetsRowOutlines.pq
Описание на буржуинском: https://github.com/hohlick/Power-Query-Excel-Formats/tree/master/RowsOutline

Как пользоваться:
  1. Копируем код
  2. Создаем пустой запрос в PQ
  3. В редакторе заменяем код на скопированный -> получаем функцию.
Скармливаем функции три параметра:
  1. Полный путь к файлу (включая имя файла) xlsx или xlsm (например, "C:\PQ\test.xlsm")
  2. Имя листа (например, "Лист2"), который хотим получить (необязательно), или список имен листов типа {"Лист1", "Лист5"}. Если ничего не укажем - будет результат по всем листам.
  3. Параметр true/false (необязательно) - хотим ли, чтобы уровень иерархии строк был добавлен первым столбцом к данным, полученным с листа. По умолчанию true.
Радуемся.

Если последний параметр true/null/опущен - получаем к стандартному выводу функции Excel.Workbook дополнительно два столбца:
1. Таблица из двух столбцов: RowIndex (0-based номер строки) и outlineLevel (уровень иерархии)
2. Таблица, содержащая исходные данные из столбца Data, к которой первым столбцом (outlineLevel) прицеплен уровень иерархии.

Если последний параметр false, то получаем только первый столбец из упомянутых выше, и что с ним делать - придумываем сами.

В следующих планах - уровень отступа в ячейках. Но это уже сильно сложнее (не по извлечению, а по притягиванию к таблицам PQ).
Изменено: Максим Зеленский - 19.01.2019 02:02:29 (update)
F1 творит чудеса
 
Перейти    
операции объединения, разницы и т.п. над массивами, получаемыми формулами
 
#
05.04.2017 19:55:07
Всем Ку.

Две формулы возвращают массивы. Предположим, СТРОКА(A1:A5) и СТРОКА(A4:A7) (на самом деле могут быть другие, например, ЧАСТОТА или МОДА.НСК)
то есть имеем {1;2;3;4;5} и {4;5;6;7}

Как из них получить формулами же:
1) пересечение {4;5}
2) объединение {1;2;3;4;5;6;7}
3) сложение (или как оно там называется) {1;2;3;4;5;4;5;6;7}
4) только различающиеся {1;2;3;6;7}
5) отличие левого от правого {1;2;3} или наоборот {6;7}
??
наверное тут надо подключать МУМНОЖи разные и т.п. но я совсем потерялся и голову поломал.
п.5 самый актуальный
Изменено: Максим Зеленский - 05.04.2017 20:02:42 (напутал с названиями)
F1 творит чудеса
 
Перейти    
Массив значений ячеек из непоследовательного массива номеров строк, только формулами, без UDF
 
#
20.07.2016 18:16:00
В ячейках А1:A3 записаны строки:
Иванов
Петров
Сидоров

результат функции дает массив номеров строк {1:3}
Как с его помощью формулами получить массив значений из А1 и А3 = {"Иванов":"Сидоров"} (или {"Иванов";"Сидоров"}?? что-то поломался и туплю, через ИНДЕКС не получается.
м.б. есть другое решение, как получить массив только нужных значений без #Н/Д, ЛОЖЬ и пр.?
F1 творит чудеса
 
Перейти    
Вышли MZ-Tools 8.0 для VBA (x64)
 
#
19.10.2015 20:27:17
Собственно, вот новость, вот MZ-Tools
F1 творит чудеса
 
Перейти    
объединение столбцов с повторяющимися названиями в PowerQuery
 
#
14.10.2015 22:10:33
Коллеги, кто работает с PowerQuery, подскажите, как создать плоскую таблицу в PowerQuery, если в таблице на листе Excel названия столбцов повторяются.
Главная засада: VBA не используем, названия столбцов на листе Excel не меняем.

Поясню на примере. На листе Excel есть "плохая таблица", первый столбец, например, "Клиент", а дальше идут группы столбцов с повторяющимися названиями, типа "Дата", "Количество". На листе их менять нельзя. Таких групп может быть очень много (десятки), или мало (5-6), или через месяц количество столбцов может измениться, или в другом исходном файле оно будет отличаться.

Из этой таблицы нужно получить  сведенную таблицу, в которой будут только три столбца: "Клиент", "Дата", "Количество". То есть, все данные из второй группы столбцов "Дата-Количество должны дописаться (вместе с именем клиента) в конец таблицы, затем третьей и т.п.

Соответственно, если затаскивать таблицу в PowerQuery, указывая, что первая строка содержит заголовки, хитрый PQ создает из диапазона умную таблицу и у нее получаются заголовки типа: Дата Количество Дата2 Количество3 Дата4 Количество5. Соответственно, UnpivotOtherColumns тут не помогает - в столбце будут просто перечислены разные названия столбцов.

Ок, говорим PQ, что таблица не содержит заголовки. Это чудо инженерной мысли всё равно создаст нам таблицу, добавив над ней строку с именами столбцов типа Столбец1, Столбец2, ... Столбец7 и так далее. В первой строке таблицы у нас есть настоящие заголовки. Не знаю, стало ли легче... Вроде не намного.

Как PowerQuery заставить сделать такую таблицу, не определяя заранее количество групп столбцов?

Есть ручное решение: создаем N (по количеству групп) выборок столбцов ({1,2,3},{1,4,5},{1,6,7} и так далее), и затем делаем им всем Append.
Но это решение требует постоянного количества групп.
Изменено: Максим Зеленский - 14.10.2015 22:11:41
F1 творит чудеса
 
Перейти    
Убрать линию маркера при помощи VBA, не получается
 
#
09.10.2015 14:00:24
День добрый.
Бьюсь уже который час, никак не пойму, в чем причина.
Делаю следующее: строю диаграмму (простой линейный график с маркерами).
По умолчанию Excel делает очень жирную линию. Я хочу сделать ее тонкой, изменить цвет, изменить тип и размер маркера, и убрать линию обводки вокруг маркера.
Записал рекордером действия, вот что получилось.
Код
Sub Макрос9()
    Range("A3").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Range("Лист1!$A$2:$E$3")
    ActiveChart.PlotBy = xlRows
    ActiveChart.SeriesCollection(1).Select
    Selection.MarkerStyle = 8
    Selection.MarkerSize = 5
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 1
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    Selection.Delete
End Sub

Ок, рисую кнопку, запускаю макрос, бац - линий на диаграмме нет, только маркеры. Логично, если последним действием убираем видимость линий.
Но у маркера нет свойства Line, которое можно было бы менять. Есть только BackgroundColor и ForegroundColor.

И как так? :(
Какое свойство менять у маркера, чтобы линий обводки у него не было?
Можно ли поменять это свойство сразу по всему ряду, или бегать по всем точкам для этого?
F1 творит чудеса
 
Перейти    
Производительность итоговых функций SQL через ADODB.Recordset, или лучше циклами?
 
#
30.09.2015 16:33:54
День добрый всем.
Вопрос скорее теоретический, поэтому пока без примера.

Недавно решал задачку на множественное приложение фильтров к набору данных. Excel 2003
По итогам работы нужно было получить суммы по отфильтрованным записям, при этом количество примененных фильтров - несколько тысяч (перебор различных границ интервалов). То есть, сами отфильтрованные записи не нужны, нужно получить лишь несколько чисел (суммы) для каждого из вариантов фильтров.

Для реализации выбрал следующую схему:
Данные на листе, делаем ADODB.Connection
Код
Set oBase = CreateObject("ADODB.Connection")
oBase.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
            ";Extended Properties='Excel 8.0;HDR=YES';"
            
Set oRS = CreateObject("ADODB.Recordset")

Запускаем циклы перебора параметров фильтров
В цикле делаем Recordset.Open cо сборной строкой constr типа:
Код
"Select COUNT([Поле 1]),SUM([Поле 2]),SUM([Поле 3]),SUM([Поле 4]) FROM [База$] Where HField & " >= " & t1 & " AND " & HField & " < " & t2 AND " & SField & " >= " & m1 & " AND " & SField & "< " & m2

где t1, t2, m1, m2 - это параметры фильтрации
Код
oRS.Open constr, oBase
... ' выгружаю результат в итоговый массив
oRS.Close
Next m2, m1, t2, t1 ' берем другие параметры фильтрации

То есть, на каждом шагу цикла открываем Recordset с итоговыми функциями в строке запроса, выгружаем результат, закрываем Recordset
По идее, скорость работы макроса должна зависеть больше от количества циклов, но при увеличении количества записей в исходной базе данных без изменения количества прилагаемых фильтров, скорость уменьшилась практически кратно.
Похоже, на каждом этапе получение итоговых функций требует время, более-менее кратное количеству записей в исходном массиве.

Так как ADODB я только овладеваю, то пара вопросов:
1) Как можно ускорить работу макроса - у меня большие сомнения относительно целесообразности Open/Close для Recordset столько раз. Но строка запроса каждый раз отличается. Может быть, есть какие-то приемы для работы с ADODB, чтобы не заниматься "хлопанием дверями"?
2) не будет ли быстрее в данном случае гонять массив, проверяя условия на каждой записи? В принципе, оправдано ли применение ADODB для такой задачи?

Спасибки
F1 творит чудеса
 
Перейти    
Сравнение двух динамических диапазонов, бинарные операции над строками/столбцами матрицы
 
#
06.08.2015 11:39:24
День добрый.
Немного многословно ниже, но тем не менее :)

В процессе решения одной задачки здесь на форуме столкнулся с тем, что необходимо сравнить на совпадение (или несовпадение) два диапазона, размерностью 1 столбец каждый, но с переменным количеством строк.
Предположим, первый диапазон "Даты" содержит некие даты, для которых проводится анализ, второй "Праздники" - список праздничных дней (хотя на самом деле это могут быть и не даты, а числа или текст).
В итоге я хочу получить формулу, результат которой - массив 0 и 1 (или ЛОЖЬ/ИСТИНА), для тех дат из первого диапазона, которые не попадают на праздничные дни. Предположим, эту формулу я потом засуну в СУММПРОИЗВ для подсчета по еще двум-трем критериям для того же исходного диапазона дат, либо использую ее результат для других целей.

Если мы будем сравнивать Даты<>ТРАНСП(Праздники), то получим такой массив с числом строк как в "Дата" и числом столбцов = числу строк в "Праздники" (например, 3).
Четвертый столбец - результат бинарного умножения предыдущих столбцов И(А1,А2,А3):
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ЛОЖЬИСТИНАИСТИНАЛОЖЬ
ИСТИНАЛОЖЬИСТИНАЛОЖЬ
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАЛОЖЬЛОЖЬ
ИСТИНАИСТИНАЛОЖЬЛОЖЬ
ИСТИНАИСТИНАЛОЖЬЛОЖЬ
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
ИСТИНАИСТИНАИСТИНАИСТИНА
Но как бинарно перемножить столбцы массива построчно? Напрямую никак не получается у меня схлопнуть массив до одного столбца бинарно.

В принципе, понятно, что можно использовать БДСУММ (если речь идет о суммировании, например). Но далеко не всегда нужна сумма, неудобное задание условий для отбора, массив нужно использовать не для суммирования, а для поиска номеров строк и т.п.

В итоге у меня родилась какая-то не очень красивая формула:
=(МУМНОЖ(1*(Даты<>ТРАНСП(Праздники));СТРОКА(Праздники)^0)=СЧЁТЗ(Праздники)

Можно ли сделать проще?
Изменено: Максим Зеленский - 06.08.2015 11:43:24
F1 творит чудеса
 
Перейти    
Извлечь первую группу цифр из строки (формулой), не менее 2 символов подряд
 
#
08.07.2015 11:55:42
Добрый день всем.

Есть строки, содержащие буквы, цифры, пробелы и символы типа "-", "/", ",", "."
Мне необходимо извлечь из строки первую группу цифр длиной не менее 2 символов. Позиция группы не фиксирована, перед ней могут встречаться одиночные цифры. Последовательность может начинаться с 0, такую тоже считаем. Например, "07" - нас интересует.

Например,
"RAS-5M34UAV-E наружный блок мультисистемы, 10,0 кВт, инвертор" - результат д.б. 34
"RAS-18N3KV-E внутренний (настенный) блок"  - результат д.б. 18
"MMY-MAP1204HT8P-E наружный блок VRF системы, 33,5 кВт, инвертор" - результат д.б. 1204

Как извлечь первую/последнюю цифру - знаю. Как извлечь все цифры из строки - знаю. Как извлечь цифровую последовательность заданной длины - тоже нашел.

Но вот как извлечь первую цифровую последовательность длиной не менее N символов?

Понятно, что быстрее всего регулярками, чуть медленнее, но тоже быстро - обычным макросом, но нужно именно формулами, и желательно одной формулой.

Подкиньте идею, плз. Мои жалкие попытки и образцы текста в файле. Пока максимум, чего удалось достичь - массив позиций цифр в строке.
Изменено: Максим Зеленский - 08.07.2015 11:56:34
F1 творит чудеса
 
Перейти    
Внесение изменений в таблицу файла *.mdb из Excel, правка таблицы Access без самого Access
 
#
29.06.2015 16:35:33
Коллеги, день добрый.

Помогите, плз, советом.
Есть программка, созданная, похоже, на MS Access. Нужная мне информация содержится в файле MDB. Но самого Access на компе нет, и в ближайшее время не предвидится.

В этой БД кучка таблиц, нужную мне я вытаскиваю в Excel через простое подключение к внешним данным.
Дальше я правлю в этой таблице нужные мне строки (только правлю содержимое, не убиваю и не добавляю строки). Суть правок - перевод интерфейса и пр. (программа многоязычная) с китайского или английского (или китайского варианта английского) на русский. Ну в общем не суть, просто вношу изменения в таблицу.

А вот дальше мне нужно засунуть измененную таблицу (достаточно только измененные строки) обратно в MDB, чтобы всё заверте...
Как это сделать за один раз? На крайний случай, как это сделать по Worksheet_Change?

По форуму порылся, нашел только это. Но так как сам в ADO и SQL полный нуб, то поправить под себя макросы не выходит.

БД расположена тут: E:\Product.mdb и запаролена (предположим, пароль Pass1)
Интересующая меня таблица называется _TRANSLATE, имеет поля ID, INDEX , English , Chinese , Russian, и т.д. языки (еще 13 шт.). Скорее всего, ID - ключевое поле.
После импорта табличка становится таблицей Excel с именем "Таблица_Product"

Пытался адаптировать вот такой макрос:

но ругается на немонопольный доступ на команде CN.Open
Что не так, куды рыть?
F1 творит чудеса
 
Перейти    
Сочетания с повторами - подсчет количества и вывод вариантов, все сочетания до 4 элементов из 8 с повторами
 
#
17.06.2015 12:28:35
Коллеги, день добрый.
Что-то затупил, не хватает знаний по математике и больше всего реально не хватает времени.

Есть некая система, собирающаяся из модулей. Модули бывают 8 типоразмеров. В систему могут быть объединены до 4 модулей (т.е. она может состоять из 1, 2, 3, 4 модулей, но не более 4 модулей). Система может состоять из любых (не обязательно разных) модулей.
Например: AA, AAB, G, CDEF, ABCDD, BBBB, DEEF, и так далее
Необходимо получить в табличку все сочетания различных типоразмеров с учетом указанных ограничений (количество модулей для каждой системы). Могу проставить руками, но на таком количестве строк боюсь промазать, а вопрос срочно-важный.

В общем, нид хелп :)
F1 творит чудеса
 
Перейти    
Сохранение вложений из нескольких писем (Outlook), batch attachments saving
 
#
06.04.2015 13:42:23
День добрый. Использую вот такой код для сохранения вложений из нескольких выделенных писем сразу (нужные рассылки и всё такое).
Скрытый текст

Из-за того, что Application.FileDialog не хочет работать в Outlook, приходится вызывать диалоговое окно Word (он быстрее грузится у меня, чем Excel :) )
Но диалоговое окно выбора папки сохранения появляется у меня в фоновом режиме, т.е. "за окном" Outlook.
Как его заставить появляться впереди? что-то туплю. Причем желательно не в режиме system.modal, т.к. иногда нужно проверить, правильно ли выбран каталог, при помощи проводника.
F1 творит чудеса
 
Перейти    
Размер поля WMF-рисунка при вставке скопированного Shape, как отрегулировать
 
#
21.01.2015 15:15:57
День добрый всем.
Есть простая достаточно задачка у меня. Берем текст из ячейки, создаем из него WordArt, затем преобразовываем WordArt в рисунок через специальную вставку.
Вот примерный код, подчищенный макрописец:
Код
Sub MakeWater()
Range("B3").Select
    With ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, Range("B3").Text, _
        "+mn-lt", 54, msoTrue, msoFalse, Range("B3").Left, Range("B3").Top)
        With .TextFrame2.TextRange.Font
            .Size = 36
            .Shadow.Blur = 0.25
            .Shadow.OffsetX = 1
            .Shadow.OffsetY = 0.2
            .Line.Weight = 0.1
        End With
        .Top = Range("B3").Top
        .Left = Range("B3").Left
        .Copy
    End With
    ActiveSheet.PasteSpecial Format:="Метафайл Windows (EMF)", Link:=False, _
        DisplayAsIcon:=False

End Sub


При таком подходе все работает как задумано, кроме одного - размер (Ш*В) вставляемой картинки значительно больше, чем размер скопированного объекта, и чем больше шрифт, тем больше расхождение размеров. Такое ощущение, что для рисунка добавляются пропорциональные поля.

Соответственно, два вопроса:
1) Есть ли другой способ преобразовать WordArt (ну или любую другую надпись) в картинк? Вроде есть, но припомнить не могу.
2) Как можно повоздействовать на вот эти вот поля? Только обрезкой?
F1 творит чудеса
 
Перейти    
"проскальзывание" кода на форме во время выполнения, срабатывают не все строки кода, при ручном проходе всё ок
 
#
09.12.2014 17:33:36
Добрый день, коллеги.
Столкнулся с такой странной, вроде бы, проблемой.
По мотивам вот этой темы Как отследить изменения Чекбокса ? набросал простенькую форму
На форме - SpinButton, два Checkbox'а и Label.

Суть модельки - показать, как можно сохранять и отображать историю чекбоксов при переходе от одного значения спина к другому.
Чекбоксы привязаны к ячейкам через ControlSource. При изменении SpinButton происходит замена ControlSource у чекбокса (номер строки задается спином), и проверяется - если ячейка ControlSource пустая, то чекбокс устанавливается False.
При инициализации формы чекбоксы устанавливаются в False, спин = 1

Запускаем форму. Первая строка отрабатывается нормально.
Щелкаем спин, переходим к другой строке. Первый чекбокс отрабатывает нормально, второй становится в положение triplestate - затененная галочка. Повторяем - картина та же, первый чекбокс и его ControlSource исправно принимает значение False на пустых ячейках, второй - по-прежнему игнорирует попытки вразумить его и ControlSource
Идем по спину в обратном направлении - всё отрабатывает замечательно, второй чекбокс срабатывает как надо - пустой заполняется False, если ранее был установлен - так же отлавливает.

Выгружаем форму, запускаем ее в ручном пошаговом режиме - все работает нормально.
Такое ощущение, что код "проскальзывает", по какой-то причине не успевая отработать изменения двух чекбоксов сразу.
Менял порядок строк в коде, давало обратные эффекты (пролетал мимо первый вместо второго), но стабильной работы добиться не удалось. Устраивал проверку не только на "", но и на Null, и на Empty - ничего.

Что это может быть за причина и как с этим бороться? Встречался с подобными проблемами и в других формах.

код тут
F1 творит чудеса
 
Перейти    
Смещение относительно объединенной ячейки [VBA], туплю
 
#
29.11.2014 01:43:47
На листе N блоков нужной информации. Начало блока - объединенная ячейка, на 10 столбцов. Нахожу ее через Find (по ключевому слову). Адрес ее, предположим, $A$5
Дальше мне нужно из следующей строки взять несколько значений (в ней ячейки не объединены). Например, из третьей ячейки. Делаю так:
Код
x = rr.Offset(1, 2).Value
Получаю значение 12-й ячейки следующей строки, вместо 3-й.
Ок, делаю так:
Код
x = rr.Cells (1).Offset(1, 2).Value
'Или
x = rr.MergeArea.Cells(1).Offset(1, 2).Value
в обоих случаях результат тот же. Вылечилось только после Unmerge.
Почему? Мне не тяжело, но хорошо, что файлы были не защищены от изменений, а их несколько сотен штук в цикле.
Как в таком случае правильно бороться? Что-то не соображу.

Прошу извинить, что без файла - пишу с телефона.
F1 творит чудеса
 
Перейти    
Массив битов десятичного числа [VBA]
 
#
25.11.2014 22:45:43
Доброе время суток, коллеги.
Есть у меня необходимость получить десятичное число в виде массива битов заданной размерности D, например, для D=4
Код
A(i) = Array(0,0,1,0) ' двойка 
A(i) = Array(0,1,0,1) ' пятерка
Точнее, задача стоит создать массив A(1 To X), содержащий массивы битов для чисел в интервале от 1 до X.
При этом правильно ли я понимаю, что X - это число, соответствующее 2^D - 1?

В связи с этим вопрос - как можно это экономно сделать в VBA,  определив заранее размер массива по верхней границе диапазона?
Все, что нашел пока - алгоритмы  для С++, Java и прочего, которые я перевести не могу.
Воспользовался алгоритмом из Википедии (вычисление остатков от деления на 2), но он какой-то громоздкий.

Есть какие-то более правильные решения?
F1 творит чудеса
 
Перейти    
Повторяющиеся имена в разных областях видимости, VBA, как правильно обратиться к имени по имени
 
#
06.11.2014 16:50:35
Добрый день, коллеги.
Сижу в отладке, симулирую разные ошибки, столкнулся с таким потенциально неприятным моментом.
Есть файл, в нем определены, предположим, два, по случайности, одинаковых, имени.
Первое имеет область видимости лист "Тест1", и называется оно Name1
Второе имеет область видимости Книга, и называется оно тоже Name1

С помощью макроса, который я тут сократил до минимума, делаю следующее:
Код
Sub test_names()
Dim nms As Names, nmeArr, i&
Set nms = ThisWorkbook.Names: If nms.Count = 0 Then Exit Sub
ReDim nmeArr(1 To nms.Count)
For i = 1 To nms.Count
    Debug.Print "Имя: ", nms(i).Name
    nmeArr(i) = nms(i).Name
    Debug.Print "Вызывается: ", nmeArr(i), "Возвращается: ", nms(nmeArr(i)).Name
Next
End Sub

макрос возвращает на первом проходе:
Код
Имя:          Тест1!Name1
Вызывается:   Тест1!Name1   Возвращается:               Тест1!Name1
всё вроде бы правильно.
На втором имени всё хуже:
Код
Имя:          Name1
Вызывается:   Name1         Возвращается:               Тест1!Name1
На практике в массив имена сохраняются из одной книги, плюс делается еще ряд манипуляций, а потом идет обращение из массива к таким же именам в другой книге.
Если все имена различаются - то и проблем нет, какая разница, какая у него область. А если, вдруг, по нелепой случайности имена будут вот так коряво определены - тогда лезет вот такая ошибка.

Собственно вопросы в следующем:
1) как правильно обратиться к имени по имени, имея ввиду разные области видимости? Через Parent?
2) как правильно сохранить такие имена в массиве, с тем, чтобы потом к ним правильно обратиться?
F1 творит чудеса
 
Перейти    
PoverPivot или сводная на основе нескольких листов, структура листов может отличаться
 
#
21.10.2014 17:54:23
Суть такая: есть книга Excel, в ней помесячные листы предположим, их всегда 12.
На листах таблицы, начинаются с A1, заголовки полей в первом столбце.
Основная проблема в том, что некоторые поля на некоторых листах могут отсутствовать
Те поля, которые в итоге нужно анализировать - есть всегда, но могут быть расположены в разном порядке.
Записей в таблицах может быть >100.000, т.е. суммарное количество строк со всех листов может быть больше лимита листа.
Приложил примерный файлик - в нем зеленые ярлычки листов - таблицы с отсутствующими полями, а оранжевые - таблицы с переставленными полями.
Необходимые поля отмечены желтым.

Я решил эту задачу макросом, который бегает по листам и нужным полям и вытягивает нужную информацию, но это медленное и жутко негибкое решение, так как задачи анализа могут модифицироваться.
По ряду причин Access не подходит, необходимо решение в Excel.

Стал смотреть в сторону PowerPivot - раньше с ней не работал, но вроде бы она похожие задачи может решать. Пока еще мало что в нем понял, увы.
Пока наиболее приемлемое решение, которое нашел - это выгрузка в csv, объединение в один большой csv-файл и затем загрузка его в PowerPivot как источника данных. Но тут опять же нужно иметь идентичную структуру, удалять заголовки полей во всех таблицах кроме первой и прочие танцы с бубнами.
Может ли PowerPivot взять в качестве источника данных таблицы с похожей структурой, и если "да" - как его заставить это сделать? Либо единственный способ - это приводить структуру в единый вид?
Изменено: Максим Зеленский - 22.10.2014 14:06:20
F1 творит чудеса
 
Перейти    
Что быстрее - WorksheetFinction.Match или Find или цикл?, к вопросу об использовании функций листа в VBA
 
#
17.10.2014 19:33:53
Недавно в одной из тем боковичком вылезло обсуждение использования функций листа в коде VBA.
Соответственно возник вопрос - я всегда считал, что для стандартных процедур встроенные функции быстрее, чем их симуляция в VBA.

Провел несколько тестов.
Сформировал случайным образом 5000 чисел и 5000 текстовых строк (на повторы не проверял)
Набросал простенький макрос поиска номера вхождения в диапазон 4-мя способами:
1. Поиск через WorksheetFunction.Match
2. Поиск через Range.Find(...).Row
3. Поиск через перебор ячеек в диапазоне (результаты тут не привожу, они минимум в 15-20 раз хуже)
4. Поиск через перебор массива.
Использовал Timer() - не самое точное вычисление, но особенно глубоко залезать не хотелось.
Таймер сбрасывался после каждой процедуры поиска.
Для теста считал суммарное время поиска, среднее и максимальное (для одного прохода) - по каждому методу.

Итого в тесте 5000 раз ищем некое случайное число (в диапазоне от 1 до 10000) или текст (аналогичное случайное число плюс абракадабра) среди неотсортированного массива из 5000 таким же образом созданных случайных чисел или текстов. Число/текст для поиска каждый раз формируется рандомом, диапазон для поиска постоянный.

Тест 1. Ищем число среди чисел. Для функций Range.Find и Match используем в качестве аргумента диапазона переменную типа Range
Тест 2. Ищем число среди чисел. Используем в виде аргумента Range напрямую, без переменной
Тест 3. Перебор ячеек через индекс строки.
Тест 4. Ищем текст

Итого (в секундах):
Поиск числа 1Поиск числа 2Поиск текстаСреднее
Sum:
Match0,6289060,7343751,2851560,882813
For Loop Array2,4296882,1796883,6406252,750000
Range.Find8,9765639,0546887,2109388,414063
Average:
Match0,000130,0001470,0002570,000139
For Loop Array0,000490,0004360,0007280,000404
Range.Find0,0017950,0018110,0014420,001682
Maximum:
Match0,0039060,0468750,0039060,018229
For Loop Array0,0468750,0585940,0703130,058594
Range.Find0,0742190,0546880,0625000,063802
В итоге при поиске чисел Match быстрее, чем Find в 12 раз - если в качестве аргумента задавать непосредственно Range, примерно в 14 раз - если в качестве аргумента задавать переменную типа Range.
По сравнению с перебором массива Match при поиске числа быстрее соответственно в 4 или в 3 раза.
При поиске текста Match в 2,8 раз быстрее перебора массива и в 5,6 раз быстрее, чем Find

Я думаю, на сортированном массиве при нечетком поиске результат был бы еще круче.
Может быть, тест не самый чистый, но, как говорится, комментарии излишни. Как резюме - не всякий метод работает быстрее при реализации в VBA, особенно в том, что касается поиска. Я думаю, что какой-нибудь хитрый метод при поиске в массиве (что бы это могло быть?) может сработать быстрее перебора, но на несортированном рандоме - вряд ли удастся добыть устойчиво лучший вариант.
Изменено: Максим Зеленский - 17.10.2014 23:33:10 (мелкая ошибка)
F1 творит чудеса
 
Перейти    
метка [SOLVED] в теме сообщения
 
#
09.10.2014 18:22:33
День добрый всем.
Подскажите, можно ли прикрутить к форуму такую штуку, как пометку названия темы типа [SOLVED] или [РЕШЕНО], если ТС сатисфакнут? Как сделано, например, на MrExcel или ExcelForum.com
Мне кажется, это удобная штука - позволяет быстро найти проблемы, еще требующие решения. Советчикам легче не рыть по всем обновлениям :)
F1 творит чудеса
 
Перейти    
Может ли UDF изменить значение другой ячейки?, чистой воды хулиганство
 
#
30.09.2014 18:18:46
Собственно, делюсь найденным.
Прошу прощения, если баян.
Насколько мне известно, путем нехитрых манипуляций можно сделать и формулу-самоубийцу.

Пользование и применение - на свой страх и риск.
F1 творит чудеса
 
Перейти    
Страницы: 1 2 След.
Наверх