Здравствуйте!
При удалении таблице DataBodyRange и создании новой строки, таблица откуда-то восстанавливает формулу столбца. Найти бы где она хранит эти формулы. Прикладная VBA-задача, например, может быть такой - выяснить соответствует ли формула в строке таблицы, общей формуле для всего столбца (при этом у всех ячеек столбца формула может быть не верна (при создании новой строки она была бы иная), а значит сверять формулы строк относительно друг-друга тупик. Хочется прямолинейно решить задачу, а не, например, через костыль, где я создаю дополнительную строку, смотрю ее формулы, а затем сношу)

Здравствуйте!
Как задать массив как литерал прямо в ячейке чтобы потом это значение ячейки можно было скормить какой-либо формуле, принимающей массив?
Я хочу где-то задавать массив как литерал (допустим, строковым типом, так как тип значения A1 это "строка"). Нужен механизм конвертации на уровне формул. Нужен какой-то способ сказать "Excel, тут в A1 не строка, а массив", пример. Решить нужно на уровне формул, доступных в Excel-2019 или ниже. Спасибо!

Здравствуйте!
Мне нужно типо ВПР по двум искомым значениям, но не сам результат, а номер позиции, удовлетворяющий критериям (не важно первый он или последний).
Также в примере какой-то косяк в работе ПОИСКПОЗ, когда умная таблица из одной строки.

Я знаю, что формула =ПРОСМОТР(1;1/(B18=B24:B40);D24:D40) работает как впр и ее не нужно вводить как формулу массива, но мне нужен номер по массиву. Есть ли более удачный вариант формулы, чем моя ПОИСКПОЗ, желательно работающий как обычная формула (не массива), как это демонстрирует ПРОСМОТР?

Здравствуйте! Возникает такая проблема, смотрите вложенный файл. Я не могу контролировать какие имена задают файлам пользователи поэтому совет "просто переименуй" не работает.




------------------------------------------------------------------------------------------------------
(Добавлено спустя)
Мне нужно импортировать группу листов из другого файла и сохранить их формульные связи друг на друга. Кроме того, я не могу удалять старый лист и заменять его новым, т.к. это руинит формульные связи других листов к такому листу. Приходится действовать через Cells.Copy и Cells.PasteSpecial чтобы формулы ссылающиеся на лист выжили после того как он изменит данные. Я бы с радостью изменил код на что-то другое, раз тут есть проблемы с подменой связей, но озвученное выше должно соблюдаться. Надеюсь, не слишком субурно описал

Приветствую!

Скачайте пример, зайти в единственный модуль макроса VBA, переименуйте функцию ‘GetText_’ убрав нижнее подчеркивание в конце имени функции. Закройте файл сохранив изменения и при повторном открытии у вас возникнет «Разрушительный сбой», если работа макросов не подавлена.
Файл-пример можете создать сами, если думаете, что дело в специфичных особенностях примера, новый файл будет работать с тем же эффектом в виде разрушительного сбоя, для этого в новом файле сначала создайте таблицу, в единственной строке которой укажите формулу ‘=GetText()’, затем вставьте модуль vba и код функции GetText, закройте файл сохранив его как .xlsm

Тестировалось на  пакетах MS Office 2016 и 2019, 64-бит, на разных машинах.

Теперь вопрос. Разумеется, обнаружил этот способ не от хорошей жизни. Часто ломается файл, где в умной таблице есть формулы с UDF, не зная как решить проблему, я упрощал и упрощал пример ошибки до минимально работающего набора, который ее провоцирует и он перед вами. Я не могу отказаться от умной таблицы и от UDF и от файла .xlsm,  но вместе эти три сущности, кажется, не уживаются, что делать, сталкивался ли кто?

Привет! Поделитесь, у кого есть, кодом Fn VBA для 64 битного MS Excel, которая принимает коллекцию как аргумент и возвращает все ее ключи (именно ключи, не значения).

Привет! У двух умных таблиц разное поведение в части форматирования итогов при удалении макросом DataBodyRange.
Одна не сбрасывает формат итогов, другая сбрасывает. Внешне таблицы выглядят абсолютно одинаково и я хотел бы понять в чем дело и как сделать чтобы вторая таблица прекратила переустанавливать формат итогов.
При ручном удалении разницы между таблицами не возникает, не ясно почему разница возникает только при удалении макросом.

Смотрите Лист1 и Лист2 в файле-примере.

Здравствуйте! Формула "СУММПРОИЗВ" не будет работать в принципе если в диапазоне суммирования есть текст? Я ввожу условия, которые должны были отсеять текст, но все равно не работает. И второй вопрос в рамках отправленного примера: как сделать чтобы умная таблица не переводила даты в заголовках таблицы в строки (хочу избавиться в моей формуле от ДАТАЗНАЧ, но хочу оставить умную таблицу)

Дополнено спустя время: желательно чтобы числа введеные как текст подсчитывались

Привет!
Меня интересует, увеличится ли скорость работы кода VBA, если он сначала опросит все ячейки одного листа и только потом перейдет на второй лист.
Допустим, нам нужно считать значение 10000 ячеек в массив, сами ячейки разбросаны в пределах одной книги (каждое значение нужно читать по индивидуально назначенному адресу ячейки, оптимизация по общему диапазону невозможна).

Будет ли зависимость скорости от последовательности адресации листов?
Будет ли зависимость скорости от синтаксиса адресации листов?

Второй вопрос можно сузить для краткости - какой синтаксис оптимален для описанной выше задачи, есть ли разница для цикла при задании итерации:


Вариант 1
итерация 1
ThisWorkbook.Sheets("Лист1").Range("A3")
итерация 2
ThisWorkbook.Sheets("Лист2").Range("A2")
итерация 3
ThisWorkbook.Sheets("Лист1").Range("A4")

Вариант 2
итерация 1
ThisWorkbook.Sheets("Лист1").Range("A3")
итерация 2
ThisWorkbook.Sheets("Лист1").Range("A4")
итерация 3
ThisWorkbook.Sheets("Лист2").Range("A2")

Вариант 3
несколько циклов (один в другом)
ThisWorkbook.Sheets("Лист1").Select
'код обращения к адресам нужных ячеек без указания листа
ThisWorkbook.Sheets("Лист2").Select
'код обращения к адресам нужных ячеек без указания листа


P.s. Еще вопрос про скорость который меня волнует, если можно)
Это нормальная практика объединять с разделителем несколько значений в String разной длины и засовывать в массив, делая String последним уровнем вложения в структуре данных? Извлекается от от туда только один раз, поиск мне не нужен. Переживаю за скорость. Или лучше заменить на коллекцию?)

Добрый день!
Как заставить макрос дождаться завершения работы других макросов? Нужен код.

Добрый день!
Помогите решить задачу.
Есть ряд цифр из которых нужно выбрать одно значение исходя из условий:
1. Выбранное значение должно быть одно и с максимально высоким приоритетом (1- это максимум).
2. Если приоритет равен, а значений несколько- то выбирается максимальная цифра из ряда с одинаковым приоритетом.
3. Значение 0 или пусто обрабатывать одинаков - искать другие значения.
4. Найденное значение должно быть более 0, ноль возможен только если весь ряд нулевой (в принципе нет значений выше 0)
5. Приоритет не задан - игнорировать (значения без заданного приоритета не брать)

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

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

Журнал восстановления:

Скрытый текст

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName&g... ошибки в файле "C:\Users\Polzovatel\Desktop\88 прочие 0,3 коррект.xlsm"</summary><additionalInfo><info>Файл поврежден настолько серьезно, что восстановить его не удалось. Была предпринята попытка сохранить формулы и значения, но некоторые данные могут быть потеряны или повреждены.</info></additionalInfo></recoveryLog>

Во вложении я дам ссылку на архив в котором будут два файла - рабочий исходник и реплика с него, которая дает ошибку при попытке ее сохранить в каком бы то ни было формате (и даже просто при нажатии на сохранить):
https://disk.yandex.ru/d/2cQoUrjMYXOrXg

Всем привет!
Выкладываю код одной из функций, которая работает в 64-х разрядной версии эксель, но выдает  "разрушительный сбой" на 32-х разрядной версии эксель.
Я гуглил о проблеме и так понял это связанно с "option explicit", который прописан в модуле "Эта книга".
Не нашел как адаптироваться если функций в книге несколько.
В общем мне нужно чтобы все мои функции книги работали на обоих версиях экселя
Откликнувшимся большое спасибо!

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

Идея не моя, вдохновителем улучшений является сайт https://www.xlwings.org.

Во вложении имеется файл Excel из которого возможен удобный запуск скриптов языка программирования "Python" (точно такой же как запуск макросов VBA).
На сайте https://www.xlwings.org/ автономная работа файла Excel с кодом Python предлагается только по платной подписке до версии "PRO", а бесплатные решения, предлагаемые на сайте не так удобны, так как требуют чтобы скрипт с расширением ".py" размещался отдельным файлом в той же папке что и файл Excel и был назван тем же именем что и файл Excel.
Удобная возможность запуска языка программирования "Python" из файла Excel базируется на трех решениях:

1. Портативная сборка интерпретатора языка Python
https://sourceforge.net/projects/winpython/files/

По ссылке предлагается несколько вариантов: с предустановленными библиотеками и без. Нет разницы в том что вы выберете, чтобы вес портативной сборки был меньше я беру голую сборку (голую сборку определить можно по весу скачиваемого файла - около 25мб). Разархивируйте скаченную папку в любое место на компьютере или в сети (на работе).

Шаг 1. Установить библиотеку "xlwings" для Python в "WinPython Command Prompt.exe":
Шаг 2. Скачайте файл "My_project.xlsm" и открыв его пропишите путь к файлу "python.exe" в ячейке b1 на листе "xlwings.conf".
У меня этот путь выглядит так:
C:\Winpython64-3.10.1\python-3.10.1.amd64\python.exe

По совершению указанных действий папка с Python (и библиотекой "xlwings") станет портативной, ничего не слетит, если вы поставите другие библиотеки и затем решите переместить эту папку в другое место на своем компьютере или поделитесь папкой с коллегой. При перемещении сборки на другой компьютер она сможет работать с файлом My_project.xlsm, требуется только задать правильно путь в ячейке b1 на листе "xlwings.conf" (шаг 2).

2. Файл Excel с модулем "xlwings" для удобной работы с библиотекой "xlwings" для Python

https://www.xlwings.org/

Чтобы во всем этом разобраться, рекомендую ознакомится с документацией, но если у вас есть время на чтение понятной литературы, то рекомендую бесплатно cкачать и прочесть "Python for Excel" от автора этой библиотеки (Felix Zumstein). Книга на английском языке. Ориентирована на людей мало знающих программирование, все хорошо объясняется с азов. Гугл хром отлично переводит и сайт и книгу.
Некоторые подробности, которые будут полезны по мере погружения в изучение библиотеки "xlwings":
надстройка Excel "xlwings" для работы файла не используется, у меня  она даже не установлена.
Исходным файлом является файл "Standalone", который после установки библиотеки "xlwings" (о том как установить библиотеку я написал в 1 пункте) вы сможете найти тут:
"Ваша папка с Python"\App\Python\Lib\site-packages\xlwings\quickstart_standalone.xlsm"
Из исходного файла я взял лист "xlwings.conf" и модуль VBA "xlwings", этот модуль вы можете обнаружить через штатный редактор VBA. Подробности о Standalone-версии, по-моему, есть только в упомянутой книге "Python for Excel". А на сайте информации, к сожалению, не много, - смотрите Alternative: Standalone VBA module.
Отличие моего файла от исходного файла "Standalone" в том, что для его работы не требуется размещать скрипт Python в той же папке и под тем же именем что и файл Excel. На сайте https://www.xlwings.org/ опция со спрятанным кодом Python внутри книги Excel предлагается только по платной подписке до версии "PRO", и эта платная возможность реализована там каким-то другим способом (не тем, которому я даю описание).

3. Хранилище файлов из листа Excel.

https://www.cyberforum.ru/blogs/829006/blog4964.html

Позволяет хранить скрипты с кодом Python прямо в файле Excel.
Если бы не нашлось этого решения, пришлось бы хранить файлы с расширениями ".py" отдельно от файла Excel в одной папке, либо где-то отдельно и прописывать к ним пути в ячейке "B3" листа "xlwings.conf", что не так прикольно. К счастью, оказалось, что код с cyberforum.ru прекрасно скопировался через штатный редактор VBA прямо в лист "xlwings.conf" Standalone-версии, которая упоминается в пункте 2. Адаптации кода не потребовалось (я отредактировал только необязательный "test"). Если потребуется, то все описываемые действия из пункта 3 легко повторить, но вам ничего не нужно делать, при скачивании мной прилагаемого файла:
чтобы увидеть файл скрипта "Python", хранящийся в Excel просто запустите макрос "START_MENU"

Запуск:

А. Скачайте файл Excel ниже.
Б. Скачайте портативную чистую версию Python отсюда:
https://sourceforge.net/projects/winpython/files/
В. Проделайте шаги 1-2 из пункта 1.
Г. В файле Excel нажмите кнопку теста на работоспособность.

Если вы видите, что меняется текст в ячейке "B14" после нажатия кнопки теста, то принимайте поздравления - ваша сборка полностью готов к плаванию в океане под названием "Python"!

Важно: Для просмотра скриптов Python (файлы с расширениями ".py"), их выгрузки для редактирования и загрузки новых редакций, пользуйтесь макросом "START_MENU". Описание макроса и его функционала найдете по ссылке в 3 пункте.

Оставлю это здесь:
1) pywin32 устанавливается автоматически на первом шаге и может так случиться, что придется откатить его до предшествующей версии с помощью "pip install pywin32==302" где "302" это номер версии до которой нужно откатиться (у вас вероятно будет не 302-я версия, смотрите через команду "pip list").
2) Если в пункте №1 вы скачали облегченную портативную сборку и хотите чтобы в ней заработал вложенный файл "WinPython Control Panel.exe", который скачался вместе со сборкой Python, выполните в "WinPython Command Prompt.exe":

"pip install pyside6" возможный вариант замены "pip install PyQt5" в коде выше, но трудно сказать что предпочтительнее. Вариант с PyQt5 мне нравится тем, что вес его файлов меньше.

Требования: любой Windows 8+, 2 ГБ оперативной памяти
Тестировалось 25.12.2021 на сборке:
Python x64   v.3.10.1               https://sourceforge.net/projects/winpython/files/WinPython_3.10/3.10.1.0/
pywin32        v.303
xlwings         v.0.25.3

Добрый день!
Написал код по ходу выполнения которого, чтобы пользователю не было скучно, запускается заставка (обычный скринсейвер).
Заставка автоматом отключается от любого действия пользователя . В том числе движения мышкой в любую сторону.
Такая вот беда - эта заставка в 20% случаев отключается сразу после запуска - из-за инерционного движения мышкой после нажатия кнопки на которую привязан макрос. Т.е. макрос уже работает, а пользователь в это время все еще двигает мышку в области кнопки по инерции.
Мне нужно как-то в коде учесть это.

Пока придумал только пауза 1 секунду выдерживать между нажатием кнопки с макросом и запуском скринсейвера
Application.Wait Time:=Now + TimeValue("0:00:01")

Но мне не нравится это мое решение, секунды мало, а на большую паузу я не согласен - так как запуск заставки через 2-3 секунды от нажатия кнопки смотрится так, словно компьютер еле запустил ее (ощущение что код тормозит).

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

Здравствуйте!
Нужно чтобы в формуле

Выражение "<>Переменный текст 1" заменить ссылкой на ячейку, в которой текст может меняться и при этом формула не "ломалась".

Откликнувшимся спасибо!

Добрый день!
Протянул формулу СУММ по столбцу где данные с количеством знаков после запятой не более чем 2, формула выдала результат "558191214,999993"
Все перепроверил такого не должно было быть, исходные данные не содержат разрядов больше чем сотые доли.
Глюк? Пример, к сожалению не удается ужать в 100кб - суммируется много данных.

пример
Пример

Добрый день!
Есть длинный фрагмент текста типа CE.U1.RPR.0120.10UJA.0.AR.LC0017-ST02
Нужно чтобы формула находила фрагмент "10UJA" конкретно в этом примере.
Но не все так просто.
Возможны варианты, где искомое будет изменяться (не будет только "10UJA").
Искомое значение это текст вида "xxUyy", где:
-xx - число от 10 до 29 (например 10UJA, 15UJA, 29UJA);
-U - символ "U" в английской раскладке (10UJA);
-yy - любые две буквы английского алфавита  (например 10URS, 15UJA, 29UVF)
Искомому фрагменту всегда предшествует символ "точка".

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

Добрый день!
При нажатой кнопке "Отобразить данные" все выглядит красиво:

Скрытый текст

Но при нажатии "Скрыть данные (печатная форма)" элементы управления формой некрасиво кучкуются на надписях шапки таблицы:

Скрытый текст

файл качнуть можно тут

Здравствуйте форумчане!
Задумался о построении БД в своем отделе.
В отделе 7 человек, из этих 7-и сотрудников знания эксель только у двоих отличные.
По этой причине ни о каком уходе из файлов эксель в другие СУБД не может быть и речи.
Из 7-и человек работать в этом смогут только двое да и не работать учиться)))
Остальные сотрудники кроме пользовательского экселя ничему не обучаемые.
Это я к тому что прежде чем предлагать всякие MySQL, PostgreSQL и Power BI вы представляли реалии кто будет с этим работать.
Естественно, делать так чтобы в отделе работало только два человека в мои планы не входит.
По этой причине необходимо создать БД отдела максимально дружественную к пользователям Excel.
Уход в Access тоже не подойдет по тем же соображениям и плюс много сложной отчетности, которая должна быть построена на БД.
Осложняющим фактором является то, что нет устроявшейся формы отчетности, приходится то так сделать, то эдак.
По этой причине строить отчетность в Access нет никакого желания.

Имея такие вводные пришла идея выкручиваться следующим образом.
1. В отдельных xlsx файлах вести файлы-справочники. Один файл - один справочник. Например, в одном файле перечень договоров по подрядчикам, во втором файле реквизиты субподрядчиков, в третьем файле данные о ходе сдачи документации заказчику, в четвертом файле единичные расценки по видам работ, в пятом классификатор, присваеваемый виду работ и т.д.
В общем будет как-бы множество столбцов, но не в одной раздутой книге Эксель, а в некоем множестве книг. Эдакий конструктор, из которого 2-е сотрудников будут строить сложную отчетность по общему идентификатору (разную, в т.ч. по новым формам)
2. Всей файлы-справочники (вместо того чтобы использовать ВПР) связать через Access, но при этом не загружать их в сам Access, а только создать связанные таблицы.
4. На основании запросов power query к Access 2-е сотрудников смогут строить сложную отчетность для отдела (отчетность будет опят же строится в файлах эксель). При этом файлы отчетности будут не такие тормознутые как было ранее (не будет множества ВПР-формул), а самое главное в них легче наводить порядок если изменилось что-то в справочнике (не придется переделывать все отчеты). Кроме того наводить порядок смогут низкоквалифицированные сотрудники отдела - они смогут разобраться в файле-справочнике.

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

В общем получается схема:
База эксель - Access для построения связей между справочниками в Excel - Запрос power query к Access - отчетность в Excel

Отдельно хочу пояснить почему не рассматриваю power pivot .
Строить модель данных в power pivot не подходит так как 1. Мне не нужна сводная таблица в отчетности. А выводить данных из pivota во что-либо другое, отличающееся от сводной таблицы, как я понял, не выйдет.
2. Проблематично подключиться через power query к модели данных power pivot. Получается система нипель туда закачать данные не вопрос, а обратно танцы с бубном.

Что посоветуете мне? Правильная ли моя идея идти к решению проблемы БД через Access?
Может есть какой-то другой подходящий инструментарий.