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

Страницы: 1 2 След.
[Работа] Создание выгрузки в CSV из данных листа Excel по хитрой схеме
 
Всем привет.

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

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

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

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

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

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

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

Пишите, обсудим.
F1 творит чудеса
JavaScript UDF, Azure Machine Learning, Power BI Custom Visuals и интеграция с Microsoft Flow, в скором будущем - в Excel :)
 
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, есть такая функция
 
В продолжение вот этой темы.

Как известно, 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, есть такая функция
 
Делюсь.

Есть такая проблема, что 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 Янв 2019 02:02:29 (update)
F1 творит чудеса
операции объединения, разницы и т.п. над массивами, получаемыми формулами
 
Всем Ку.

Две формулы возвращают массивы. Предположим, СТРОКА(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 самый актуальный
Изменено: Максим Зеленский - 5 Апр 2017 20:02:42 (напутал с названиями)
F1 творит чудеса
Массив значений ячеек из непоследовательного массива номеров строк, только формулами, без UDF
 
В ячейках А1:A3 записаны строки:
Иванов
Петров
Сидоров

результат функции дает массив номеров строк {1:3}
Как с его помощью формулами получить массив значений из А1 и А3 = {"Иванов":"Сидоров"} (или {"Иванов";"Сидоров"}?? что-то поломался и туплю, через ИНДЕКС не получается.
м.б. есть другое решение, как получить массив только нужных значений без #Н/Д, ЛОЖЬ и пр.?
F1 творит чудеса
Вышли MZ-Tools 8.0 для VBA (x64)
 
Собственно, вот новость, вот MZ-Tools
F1 творит чудеса
объединение столбцов с повторяющимися названиями в PowerQuery
 
Коллеги, кто работает с 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 Окт 2015 22:11:41
F1 творит чудеса
Убрать линию маркера при помощи VBA, не получается
 
День добрый.
Бьюсь уже который час, никак не пойму, в чем причина.
Делаю следующее: строю диаграмму (простой линейный график с маркерами).
По умолчанию 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, или лучше циклами?
 
День добрый всем.
Вопрос скорее теоретический, поэтому пока без примера.

Недавно решал задачку на множественное приложение фильтров к набору данных. 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 творит чудеса
Сравнение двух динамических диапазонов, бинарные операции над строками/столбцами матрицы
 
День добрый.
Немного многословно ниже, но тем не менее :)

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

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

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

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

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

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

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

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

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

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

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

Помогите, плз, советом.
Есть программка, созданная, похоже, на 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 с повторами
 
Коллеги, день добрый.
Что-то затупил, не хватает знаний по математике и больше всего реально не хватает времени.

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

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

Из-за того, что Application.FileDialog не хочет работать в Outlook, приходится вызывать диалоговое окно Word (он быстрее грузится у меня, чем Excel :) )
Но диалоговое окно выбора папки сохранения появляется у меня в фоновом режиме, т.е. "за окном" Outlook.
Как его заставить появляться впереди? что-то туплю. Причем желательно не в режиме system.modal, т.к. иногда нужно проверить, правильно ли выбран каталог, при помощи проводника.
F1 творит чудеса
Размер поля WMF-рисунка при вставке скопированного Shape, как отрегулировать
 
День добрый всем.
Есть простая достаточно задачка у меня. Берем текст из ячейки, создаем из него 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 творит чудеса
"проскальзывание" кода на форме во время выполнения, срабатывают не все строки кода, при ручном проходе всё ок
 
Добрый день, коллеги.
Столкнулся с такой странной, вроде бы, проблемой.
По мотивам вот этой темы Как отследить изменения Чекбокса ? набросал простенькую форму
На форме - SpinButton, два Checkbox'а и Label.

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

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

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

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

код тут
F1 творит чудеса
Смещение относительно объединенной ячейки [VBA], туплю
 
На листе 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]
 
Доброе время суток, коллеги.
Есть у меня необходимость получить десятичное число в виде массива битов заданной размерности 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, как правильно обратиться к имени по имени
 
Добрый день, коллеги.
Сижу в отладке, симулирую разные ошибки, столкнулся с таким потенциально неприятным моментом.
Есть файл, в нем определены, предположим, два, по случайности, одинаковых, имени.
Первое имеет область видимости лист "Тест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 или сводная на основе нескольких листов, структура листов может отличаться
 
Суть такая: есть книга Excel, в ней помесячные листы предположим, их всегда 12.
На листах таблицы, начинаются с A1, заголовки полей в первом столбце.
Основная проблема в том, что некоторые поля на некоторых листах могут отсутствовать
Те поля, которые в итоге нужно анализировать - есть всегда, но могут быть расположены в разном порядке.
Записей в таблицах может быть >100.000, т.е. суммарное количество строк со всех листов может быть больше лимита листа.
Приложил примерный файлик - в нем зеленые ярлычки листов - таблицы с отсутствующими полями, а оранжевые - таблицы с переставленными полями.
Необходимые поля отмечены желтым.

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

Стал смотреть в сторону PowerPivot - раньше с ней не работал, но вроде бы она похожие задачи может решать. Пока еще мало что в нем понял, увы.
Пока наиболее приемлемое решение, которое нашел - это выгрузка в csv, объединение в один большой csv-файл и затем загрузка его в PowerPivot как источника данных. Но тут опять же нужно иметь идентичную структуру, удалять заголовки полей во всех таблицах кроме первой и прочие танцы с бубнами.
Может ли PowerPivot взять в качестве источника данных таблицы с похожей структурой, и если "да" - как его заставить это сделать? Либо единственный способ - это приводить структуру в единый вид?
Изменено: Максим Зеленский - 22 Окт 2014 14:06:20
F1 творит чудеса
Что быстрее - WorksheetFinction.Match или Find или цикл?, к вопросу об использовании функций листа в VBA
 
Недавно в одной из тем боковичком вылезло обсуждение использования функций листа в коде 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 Окт 2014 23:33:10 (мелкая ошибка)
F1 творит чудеса
метка [SOLVED] в теме сообщения
 
День добрый всем.
Подскажите, можно ли прикрутить к форуму такую штуку, как пометку названия темы типа [SOLVED] или [РЕШЕНО], если ТС сатисфакнут? Как сделано, например, на MrExcel или ExcelForum.com
Мне кажется, это удобная штука - позволяет быстро найти проблемы, еще требующие решения. Советчикам легче не рыть по всем обновлениям :)
F1 творит чудеса
Может ли UDF изменить значение другой ячейки?, чистой воды хулиганство
 
Собственно, делюсь найденным.
Прошу прощения, если баян.
Насколько мне известно, путем нехитрых манипуляций можно сделать и формулу-самоубийцу.

Пользование и применение - на свой страх и риск.
F1 творит чудеса
Parallel Sets / Coordinates, приемы визуализации
 
Вот такая красивая вещь.
Интересно, ее можно всё же сделать средствами Excel?
Автор насчет ParallelSets говорит, что не блеснет - я ему верю. Но есть софтина :)
Соотношени выживших на Титанике по полу и классу

Parallel Coordinates, наверное, можно придумать средствами Excel, но пока не понял, как
соотношение характеристик автомобилей
F1 творит чудеса
перезапускаемый счетчик в формуле, он же периодический счётчик формулой
 
Коллеги, просветите, какие бывают варианты счётчиков в формулах.
Например, мне нужно, чтобы при протягивании формула проставляла в строках 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 1, 2 ... и так далее, то есть от 1 до 5 по кругу
Я пытался как-то решить этот вопрос через кратность номера строки. Получалось что-то вроде
=ЕСЛИ(ОСТАТ(СТРОКА();5)=0;5;ОСТАТ(СТРОКА();5))

Когда речь идет только о простом счетчике itself, то это годится. Но иногда счетчик - всего лишь аргумент в какой-то сложной формуле. Может быть, есть какие-то более короткие варианты?
F1 творит чудеса
Подсчет количества одинаковых признаков
 
Доброе время суток.
Не могу решить задачку, совсем завис
Есть список неких данных, назовем их сегментами, каждый из которых в один момент времени обладает 2-мя признаками (всего признаков 6).
Количество записей на 1 сегмент разное. Признаки для 1 сегмента не повторяются.

Как узнать количество сегментов, имеющих два конкретных признака?
Решить нужно формулой, желательно без допстолбцов :(
F1 творит чудеса
Возможно ли добавление виртуального ряда на диаграмму [VBA], данных нет на листе
 
Доброе время суток, друзья
Возник вопрос - возможно ли (и каким способом) добавление в диаграмму ряда, данных которого нет на листе.
Насколько понимаю, добавление ряда производится при помощи метода SeriesCollection.Add, у которого есть обязательный аргумент Source типа Range.
Есть ли какой-нибудь способ добавить на диаграмму ряд, данных которого нет на листе (т.е. совсем), или всё же нужно ссылаться хотя бы на пустой диапазон и затем вручную менять данные в этом ряду?
F1 творит чудеса
Адресами ячеек в Range.SpecialCells, баг или фича?
 
День добрый
Имеется лист, в котором в ячейках A2, A4, A6, A8, A10 находятся формулы.
Имеем также вот такой код:
Код
Sub qq1()
Dim rcell As Range, i&, st$
    On Error Resume Next
    Set rcell = Columns(1).SpecialCells(xlCellTypeFormulas)
    For i = 1 To rcell.Cells.Count
       st = st & rcell(i).Address & vbCrLf
    Next
    MsgBox "Адреса ячеек с формулами:" & vbCrLf & st
End Sub 
Вот такой результат - это баг или фича? Что я сделал не так?

F1 творит чудеса
Странное поведение Dictionary, который думает, что он Collection, Словарь-шизофреник
 
Добрый день, коллеги.
Столкнулся недавно со странной ситуацией.
Примерно такой банальный код, все переменные локальные:
Код
Dim dct1 as Object, sKey$, x&, y&, sItem
Set dct1 = CreateObject("scripting.dictionary"): dct1.CompareMode = vbTextCompare
For x = 1 to 20
   For y = 2 to 30
      sKey = x & "|" & y
      If Cells(x, y) <> 0 Then
         sItem = "...." ' какой-то итем
         If Not dct1.Exists(sKey) Then
            dct1.Add sKey, sItem
         Else
            ' какие-то преобразования sItem
            dct1(sKey) = sItem
         End If
      Else
         If dct1.Exists(sKey) Then dct1.Remove sKey
         ' еще какой-то код
      End If
   Next
Next

При отладке в какой-то момент вдруг выскочила ошибка на код dct1.CompareMode = vbTextCompare - ни в какую не хотело принимать эту строку. Закомментировал её, и обнаружил, что не срабатывает команда dct1.Remove sKey - те записи, что должны удаляться, тем не менее оставались в словаре.
Перезапустил макрос, отследил в Watches, что сразу после создания словаря он уже имеет пустую запись (Item 1 с ключом "", количество записей = 1), что приводило впоследствии к ошибке при выгрузке, и по-прежнему не удаляет записи.
И, так как у меня строка 12 была записана с ошибкой (было dct1.Add sKey, sItem при существующем ключе), то при попадании в данную часть кода вылетала ошибка насчет невозможности добавить элемент в коллекцию.

В общем, словарь, объявленный в локальной переменной, упорно считал себя коллекцией.
Вылечилось перезапуском Excel (объявление словаря стало работать нормально, заработала CompareMode и т.п.), но беспокоит немного, так как этот код не для меня, а для заказчика, и такая неприятная особенность может порушить важные данные.

С чем это может быть связано? Как этого избежать?
F1 творит чудеса
Страницы: 1 2 След.
Наверх