Программирование. Макросы VBA Excel.

[ExcelVBA]

Здравствуйте форумчане.

Решил завести на форуме СтроимДом страничку, на которой смогу предлагать услуги связанные с программированием, например, написанием макросов в MS Excel.

Что такое Excel - знают все. Те, кто пользуется им как калькулятором - возможно не знают, что он может больше. Помогу написать продвинутые формулы

Те, кто проводят с ним достаточно времени и понимают, что многие операции можно автоматизировать, но не знают как... тоже могут обращаться.

Профи, которые с ним на ты... милости прошу. Поднимем уровень ИТ на форуме СтроимДом вместе.

 

Итак, собственно, что я предлагаю:

- Напишу любой сложности макросы в Excel.

- Автоматизирую многие процессы.

- Научу программированию.

- Просто помогу советом.

 

Как это работает и сколько это стоит:

- На простые вопросы отвечу тут.

Короткие ответы на короткие вопросы - бесплатно

 

- Несложные, простые макросы, формулы напишу и отправлю почтой.

Задачи, которые я оцениваю как несложные, в основном получается закончить в течении 1-2 дней. Цена порядка 200-250 грв.

Деньги по факту получения результата можно перевести на карточку.

 

- Сложные и большие - оценю объективно свои возможности.

По договоренности.

[msdv]

Буду первым.

Как автоматом убрать ошибки(#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО! и тд....) в формулах во всем документе.

Хотелось бы доп. кнопку при нажатии меняющую все ошибки на 0.

[v.ard]

Здравствуйте!

 

А можно в личку Ваш email и телефон?

 

Спасибо.

[ExcelVBA]

Буду первым.

Как автоматом убрать ошибки(#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО! и тд....) в формулах во всем документе.

Хотелось бы доп. кнопку при нажатии меняющую все ошибки на 0.

 

Попробуйте так:

Нажимаете F5, затем внизу кнопка Special...

Выбираете Формулы, опция Ошибки.

Затем нажитмаете клавишу удалить или делаете замену выделенных ячеек на 0.

[msdv]

Попробуйте так:

Нажимаете F5, затем внизу кнопка Special...

Выбираете Формулы, опция Ошибки.

Затем нажитмаете клавишу удалить или делаете замену выделенных ячеек на 0.

 

Спасибо!!!!!

[ramshteine]

Подпишусь ка я на темку))

[ExcelVBA]

Многие используют формулы. А многие знают, что можно писать свои формулы?

 

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

Ответ: можно.

Интересует кого такая формула, в которую передается диапазон суммирования и цвет?

[ramshteine]

Конечно интересно ))

[ExcelVBA]

Конечно интересно ))

 

Ну собственно весь вопрос:

Public Function SumByColor(rRange As range, rColor As range)

Dim a As range

Dim col As Double

col = rColor.Interior.Color

 

For Each a In rRange

If col = a.Interior.Color Then

SumByColor = SumByColor + a.Value

End If

Next

End Function

[ramshteine]

Что можете посоветовать при таком трабле: Таблица дублируется как будто области раскрепляются. Помогает только перезапуск данного файла. Фал весит 32 гига. Более 60 тысяч строк.

Надеюсь на фото видно.

[ExcelVBA]

Что можете посоветовать при таком трабле: Таблица дублируется как будто области раскрепляются. Помогает только перезапуск данного файла. Фал весит 32 гига. Более 60 тысяч строк.

Надеюсь на фото видно.

[ATTACH]413785[/ATTACH]

 

Сам факт того, что файл весит 32 гига - это уже трабл сам по себе.

Думаю проблема именно в этом...

 

 

Сделайте следующий эксперимент:

1. Создайте копию файла. Выберите файл в проводнике ctrl+c, ctrl+v.

2. На каждом листе: выберите весь лист (нажмите на квадратик пересечения строк и столбцов), Правка -> Очистить форматы.

3. Сохраните файл.

 

Стал занимать меньше места?

 

Попробуйте еще так...

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

Пробуйте сохранить и смотрите как размер.

[ramshteine]

Сам факт того, что файл весит 32 гига - это уже трабл сам по себе.

Думаю проблема именно в этом...

 

 

Сделайте следующий эксперимент:

1. Создайте копию файла. Выберите файл в проводнике ctrl+c, ctrl+v.

2. На каждом листе: выберите весь лист (нажмите на квадратик пересечения строк и столбцов), Правка -> Очистить форматы.

3. Сохраните файл.

 

Стал занимать меньше места?

 

Попробуйте еще так...

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

Пробуйте сохранить и смотрите как размер.

 

Сорри, ошибся. З2 метра, а не гига.

 

Раньше он весил 70 метров. Периодечески его чищу, этот создан заново месяц назад, с удалением столбцов и строк и некоторого цветового заполнения. Из формул только сумма, в одном столбике, больше вообще никаких нет.

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

Видно проблема все-таки не решаемая((

[slkl]

Сорри, ошибся. З2 метра, а не гига.

 

Все решаемо, надеюсь. У Микрософта глюки иногда выскакивают на голом месте. Скажите, какой формат файла, в смысле расширение?

И попробуйте сделать как Вам сказали выше - сделать новый файл и туда копировать потихоньку. Я бы копировал поэтапно, с проверкой после каждого копирования

[ramshteine]

Все решаемо, надеюсь. У Микрософта глюки иногда выскакивают на голом месте. Скажите, какой формат файла, в смысле расширение?

И попробуйте сделать как Вам сказали выше - сделать новый файл и туда копировать потихоньку. Я бы копировал поэтапно, с проверкой после каждого копирования

 

Спасибо, но такие операции я делаю регулярно. Не помогает. Если это глюки, то почему-то только у меня, даже у меня в отделе такого ни у кого нет. Разрешение xlsx, офис 2010. В 2003 такого не было, но ограничение строк и столбцов таки вынудило меня перейти на более новые версии.

[slkl]

...Если это глюки, то почему-то только у меня, даже у меня в отделе такого ни у кого нет. .....

Он у Вас в общем доступе?

[ramshteine]

Он у Вас в общем доступе?

 

Нет, но у других работают его измененные клоны (попроще)

[slkl]

Нет, но у других работают его измененные клоны (попроще)

 

Попробуйте чужой клон на своей машине. Если да - проблема в Вашем Екселе, нет - проблема в Вашем файле.

Также мона попробовать наоборот - свой файл на чужой машине

[ExcelVBA]

Наверняка многие сталкивались с необходимостью консолидации многих однотипных файлов\таблиц в одну пригодную для дальнейшего анализа.

 

Я попытаюсь сгруппировать типичные ситуации которые мне приходилось решать:

1. Данные находятся в разных файлах, которые одинаковы по своей структуре и представляют собой табличные данные.

Типичный пример - консолидация прайс листов.

2. Данные находятся в разных файлах, которые одинаковы по структуре, но данные для консолидации находятся в произвольных ячейках.

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

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

 

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

[kkost]

Добрый день!

А возможно ли преобразовать таблицу с датами (по вертикали - перечень объектов, по горизонтали - перечень событий, тело таблицы - даты) в календарь?

[ExcelVBA]

Добрый день!

А возможно ли преобразовать таблицу с датами (по вертикали - перечень объектов, по горизонтали - перечень событий, тело таблицы - даты) в календарь?

 

Добрый день,

 

насколько я понял уже есть таблица, которая на пересечении содержит даты. Что Вы понимаете под преобразовать в календарь? Если можно приложите картинку.

[kkost]

Вы правильно поняли.

В приложенном файле исходник и примерный результат.

Я вроде уже справился. Исходник надо в другом виде делать, а потом сводную таблицу.

Может Вы лучший способ знаете.

examl.xls

[WhisperDSI]

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

Я смог найти только выборку ячеек по цвету:

 

'---------------------------------------------------------------------------------------

' Procedure : CountByInteriorColor

' Author : The_Prist(Щербаков Дмитрий)

' www.excel-vba.ru

' Purpose : Функция подсчета ячеек на основе цвета заливки.

' Аргументы:

' rRange - диапазон с ячейками для подсчета.

' rColorCell - ячейка-образец с цветом заливки.

' bSumHide - ИСТИНА или 1 учитывает скрытые ячейки.

' ЛОЖЬ, 0 или опущен(по умолчанию) - скрытые ячейки не подсчитываются.

'---------------------------------------------------------------------------------------

Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False)

Dim lColor As Long, rCell As Range, lCnt As Long, vVal

lColor = rColorCell.Interior.Color

For Each rCell In rRange

If rCell.Interior.Color = lColor Then

If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then

If bSumHide Then lCnt = lCnt + 1

Else

lCnt = lCnt + 1

End If

End If

Next rCell

CountByInteriorColor = lCnt

End Function

 

Не понял как добавить ограничение для поиска "яблоко"...

[Offroad]

Робоча локалка, облікові записи користувачів із звичайними правами (не адміни), на всіх машинах треба надстройка VBA "сума прописом". Раніше на умовно головній машині зберігав цю надстройку Excel (xla), потім руцями кожному користувачу додавав її в Excel. Недоліки – довго і рутинно, надстройка має лежати в розшареній папці, треба завжди увімкнений той перший ПК (не принципово - він і так має працювати) і запит про увімкнення макросу при вході в документ нового користувача (а не того хто його останній раз редагував/зберігав).

Тут мені підказали, що код з цієї надстройки можна скопіювати прямо в модуль в сам документ - із недоліків незначно збільшиться обсяг кожного файлу з надстройкою, але відкриється на будь-якій машині і користувачем… Вставляю, але працює рівно до закриття документу. Перевідкриваю - із списку функцій "Определенные пользователем" він зникає і в документі більше не працює. Сам модуль на місці (ALT + F11), код відкривається… Що я роблю не так?

З.І. Пробував різні надстройки, працюють аналогічно. Excel16