Excel vba формула слишком сложна для присваивания объекту
Прочие форумы => Курилка => Тема начата: IgorStorm от 17.10.2015, 14:03:45
Здравствуйте, господа знатоки.
Задался вопросом эффективного изучения Excel. Главный критерий эффективности – скорость работы в нем и относительная легкость восприятия (не красота, не крутость написания мегаформул, а именно скорость (читай производительноть). Сейчас нахожусь на этапе когда немного знаю основные формулы, умею их несложно комбинировать и знаю немного VBA могу создавать несложные/средние макросы. Нужен совет, на что лучше сделать упор: на дальнее изучение формул и «родных» методов Excel или все же лучше углубиться в VBA и забыть про стандартные средства? Хотел бы услышать ответы, на ряд возникших у меня вопросов. Извиняюсь, если получилось сумбурно - это просто поток мыслей из оперативной памяти мозга, без копания глубже. :-X
И меня вызывают не больше ассоциаций, чем набор иероглифов. Стоит ли тратить время на то, чтобы я достигнуть такого уровня мастерства, при котором их сам могу составлять, либо же лучше решать научится такие задачи средствами VBA? Так получится универсальнее и быстрее? Т.е. лучше ли выучить сотню родных функций Excel с их логикой и потом из них компоновать такие (сколько времени нужно на ее ввод?) либо же лучше создавать свои?
Верно, ли то, что 50% случаев необходимость ввода сложных формул возникает из-за плохой структуры первоначальных вводных данных и то что средствами VBA их лучше сначала структурировать, а затем применить расчеты попроще?
Верно ли то, что в остальных 50% необходимость ввода сложных формул возникает из-за желания пользователей получать результат в нелогичном виде (не как проще, а как красивее), и что лучше средствами VBA из первоначальных данных сделать готовый отчет с типовой структурой, чем неочевидной логикой формул пытаться вставить данные в заранее разработанную форму типового отчета?
Верно, ли то, что средствами VBA всегда удобнее и быстрее проводить массивные расчеты, применив выгрузку данных с листа в программный массив и там проводя вычисления, чем пытаться решить ту же задачу формулами листа?
Помимо этого я понимаю, что VBA может намного больше, чем стандартные функции Excel и если в нем достичь определенного мастерства, то можно будет решать задачи гораздо эффективнее этих стандартных функций (может даже самому писать их аналоги-велосипеды). Я прав
Также я понимаю, что когда у меня будет достаточно разработок на VBA,я смогу их применять как части конструктора для решения большинства возникающих задач, т.е. разработка станет очень быстрой. Формулы между тем постоянного нужно приспосабливать под новые условия и с ними подобие "библиотеки кодов" не прокатит? Я прав?
Так как же добиться эффективного профессионализма здесь- просто уметь делать в наше время недостаточно, нужно это делать быстро, а что лучше поможет делать быстро? Подскажите проф. советами.
Начинал с формул, потом увлекся VBA.
Сейчас сложные работы выполняю с использованием и того, и другого. Иногда требование к работе - без использования VBA, иногда применение функций листа может стать ущербным.
По сути своей функции листа - те же микропрограммы со своей логикой работы. В некоторых случаях выгоднее применять формулы, в некоторых - VBA.
Например, финансовое моделирование. Для каждого типа кредита нужно выводить свои величины. Вместо того, чтобы каждый раз пересчитывать, например, аннуитетный коэффициент - формулы на листе. И эти данные для обработки загоняются в массив в готовом виде.
Использование сложных формул. Часто пользователи боятся дополнительных столбцов, "лишних" вычислений, не понимая, что это разгружает вычисления, делает формулы проще для понимания.
Формулы работают быстрее. Но если их много, добавляют веса файлу и тормозов в работе.
О больших массивах. Если нужно найти максимальное значение в огромном массиве или посчитать определенные значения (СЧЕТЕСЛИ) - совсем не обязательно прибегать к VBA. Но если идет анализ такого массива с перебором элементов или перемещением больших блоков данных - да, просим поработать код.
О структуризации данных. Неправильная структура плохо воспринимается и формулами, и VBA. Вначале нужно продумать, а уже потом работать с формулами и VBA. Хотя. немного не так - продумывать структуру нужно параллельно с логикой работы формул/макросов.
Резюме. Писал-написал, а конкретного - ничего. Потому что опыт, который "сын ошибок трудных", подскажет, чем и как работать. И чем дальше, тем качественнее его подсказки.
Я бы не так сказал: уметь - недостаточо, нужно погрузиться в задачу и получать удовольствие от работы над ней :)
Приведенная вами для примера, формула - неплохой шанс довести до исступления человека, который попробует в ней разобраться. Существенно облегчите ему (и себе!) жизнь, если будете пользоваться именованными диапазонами и разобьёте формулу на 2-3 (промежуточные результаты также порекомендовал бы именовать).
Использование многократно встроенных различных функций - это контролеНЕпригодно! Ни о каких модификациях вычислительной схемы мечтать в таких случаях не приходится. Так. задачка-однодневка.
Безусловно, удобнее использовать VBA! Причем обязательно использовать пользовательские формы. Работа будет приносить удовольствие!
(Я вашей "болезнью" с громоздкими формулами переболел лет 25 назад. Однажды, в полном озверении, бросил всё и научился создавать пользовательские формы. Это совсем несложно!)
Ага. Особенно если файл будут использовать много пользователей, которые не знают как ярлыки листов отобразить, не то что макросы разрешить. Плюс в некоторых организациях выполнение макросов вообще запрещено на уровне групповой политики. Как решение для себя - да, оптимально и то не всегда в плане скорости вычислений.
А формы больше нужны для взаимодействия с пользователями и проверки вводимых значений.
неплохой шанс довести до исступления человека, который попробует в ней разобраться. Существенно облегчите ему (и себе!) жизнь, если будете пользоваться именованными диапазонами
Именованные формулы да, могут облегчить читабельность и дальнейшее "обслуживание" формулы если знать все принципы работы с именами. Однако для понимания другого человека формула не станет проще, а даже наоборот - если человек не знаком вообще с именами - для него станет большой загадкой что такое "_часть1", "_часть2" и т.п.
Как итог: ко всему надо подходит исходя из ситуации и конкретных задач. Где-то лучше формулы, где-то VBA, где-то что-то другое.
Такие пользователи НИКОГДА не будут читать содержание формулы. И, тем более, пытаться редактировать формулы.
Лет 50 назад меня учили, что программой, ясной для любого дурака, будут пользоваться только дураки. Кажется, правильно учили.
Присвоение таких малоинформативных имен не остроумно и мало отличимо от ссылок на адреса ячеек или диапазонов.
1. Если пользователь задумался о макросах или VBA, значит он намерен многократно пользоваться вычислительной схемой.
2. При многократном использовании возникает (собственный жизненный опыт) модифицировать вычислительный процесс. Посему разбирательство с вложенными функциями, которые вложены в другие вложенные функции, также вложенные. вызовет оторопь, отвращение и т.п.
3. Я честно признался, что тоже "болел" ранее такими формулами-"матрешками". И пришел к выводу, что не стоит лениться - лучше пользоваться VBA с "недлинными" строками кода - затраченное время с лихвой окупится при (см.п.2)
Хотя, конечно, дело вкуса: мазохизм не запрещен :)
Присвоение таких малоинформативных имен не остроумно и мало отличимо от ссылок на адреса ячеек или диапазонов
ну да. Я же должен был Вам прям реальные имена сейчас сходу придумать. Вы хоть фантазию включайте, когда обсуждение ведете :) Я же написал русским языком - пользователь, который не знает что такое именованный диапазон никогда не догадается, где эти имена искать. И для него еще большей загадкой будет это имя, чем стандартная функция. Ну читать же надо, что пишут, а не в одних своих мыслях вариться.
А такие файлы как правило создает один человек и пользователь при этом вообще ни о чем не думает. Я уже отсюда вижу, что у Вас нет опыта разработки файлов даже внутри небольшой компании для определенного коллектива. А реалии жизни как правило таковы, что сидит куча пользователей и один "спец" создает файл для их в нем работы. Тут уже не до опросов того, о чем там пользователи думают. Тут приходится считаться со многими факторами.
Предположу, что написанное Вами выше - это лишь изложение мыслей одинокого пользователя, которому совершенно нет нужды создавать файлы для других и политика ИТ внутри компании не наложила на Вас ограничений.
Поэтому повторюсь: выбор средств всегда должен исходить из ситуации, а не наоборот. Если делать наоборот - то непременно окажетесь в ситуации, когда все сделали на VBA, а работать в этом кроме Вас никто и не может. Что в итоге? Инициатива имеет инициатора, потому что либо все переделывать, либо одному делать всю работу, которая должна была быть сделана другими с помощью этого файла. И мазохизмом здесь будет как раз не учитывать все реалии и делать "как мне хочется".
Почти всё понял.
Однако, должен, заметить, что вряд ли "одинокий пользователь" смог бы создать пакеты прикладных программ, которые уже много лет (больше 20) используются в Москве, в Шанхае, в Упсале и в Нагое.
Так как привык обсуждать суть дела, а не оппонента, то свое мнение оставлю при себе и полемику прекращаю. Честь имею!
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать(Copy) .
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl + C
- Тут же вставляем обратно сочетанием Ctrl + V
- Жмём Ctrl , чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
(см. строку назначения ниже)
'InsertNewRow "ServiceCrewDay_EmployeeList", "SAP_SCD_InPool", "SAP_SCD_OutPool", "SAP_SCD_SecondaryIn", "SAP_SCD_SecondaryOut", "SAP_SCD_ORD", "SAP_SCD_THF", "SAP_SCD", "SAP_SCD", "SAP_SCD"
Сам макрос работает должным образом, проблема заключается только в назначении именованных диапазонов. Есть лучший способ это сделать?
или есть способ обойти формулу слишком сложным методом? и если это необходимо сделать на всех компьютерах конечных пользователей или только на моем, и настройки сохранятся?
То, что я думал о том, чтобы сделать, это просто взять 2 именованных диапазона, а затем для следующих диапазонов Просто компенсировать их счетчиком строк предыдущего диапазона, поэтому, если Range2 = Sheets().Range("A1:A10") , затем Range3 = Range2.Offset(Range2.Rows.Count,0) , тогда ввод для задания должен быть только Range1 as string, Range2 as string, NumberOfExtraRanges as integer , причина, по которой мне нужно по крайней мере два диапазона связаны с тем, что каждый диапазон после диапазона 1 находится на другой вкладке и, по сути, является версией необработанных данных всех часов информации о выплате и т.д. на первой вкладке, которая будет Range1_EmployeeList
с которым я буду играть, пока жду ответа.
@Nicholas hunter Я думал об этом, но я думаю, что это было бы слишком громоздко, поскольку этот макрос будет использоваться во всех электронных таблицах, которые мы создаем (около 400+), для небольшого фона, мы создаем табели, которые будут автоматически собираться и обрабатывается SAP. Таким образом, для каждой электронной таблицы требуется выходная страница SAP, имеющая одинаковый формат и т. д. Каждый «Бригада», член экипажа и каждый получаемый им платеж (сверхурочные бонусы и т. д.) В каждой электронной таблице должны быть представлены в этом выходном листе SAP. они также хотят иметь возможность добавлять строку в свой список экипажа, когда им не хватает места.
Я устанавливаю значение ячейки в Sheet1 с помощью формулы, которая включает ячейки из Sheet1 и Sheet2. Есть ли способ установить такую формулу из VBA?
Вот что у меня есть:
Я заполняю некоторую информацию от B11 до M11 до строки номер 29, например, заполняя форму по строкам. Иногда это будет только одна строка с информацией, а также могут быть все таблицы с данными.
В столбце G у меня есть раскрывающийся список, и в соответствии со значением из этого раскрывающегося списка заполняется соответствующая ячейка H.
Мне удается установить формулу для этого, но из соображений безопасности я хочу установить ее из VBA.
что ты уже испробовал?
Я попытался сделать «для», которое начинается с i = 11, и установить столбец G со значением i, но я думаю, что сделал это неправильно.
вы можете просто сохранить свою формулу в столбце G и защищать в этом диапазоне, чтобы пользователь не мог ее редактировать.
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно.
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей.
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то.
Ответы 4
Вы можете использовать средство записи макросов: перейдите на вкладку разработчика на ленте, затем запишите макрос, затем щелкните ячейку с формулой, затем нажмите «Остановить запись» на ленте, затем откройте VBA, и Excel уже сгенерирует макрос для вас.
Когда я это делаю, я получаю следующее:
Как я могу это сделать, когда любая из ячеек в столбце G изменяется, начиная с G11?
WorksheetFunction.Vlookup() работает довольно хорошо, если вы правильно используете диапазоны и параметры:
Как я могу использовать этот подход для каждой ячейки G, в которой есть информация? Я буду использовать ячейки от g11 до g29, есть ли способ реализовать цикл For, чтобы проверить, в какой строке есть информация?
Вы можете просто сохранить свою формулу в столбце G и защитить этот диапазон, чтобы пользователь не мог его редактировать.
В качестве альтернативы, используя обработчик событий Worksheet_Change :
Если вам нужно сделать то же самое с несколькими столбцами, это немного сложнее, потому что у вас может быть только один обработчик Worksheet_Change на данном листе. Поэтому вам нужно изменить его, как показано ниже. Это можно было бы немного упростить, чтобы уменьшить некоторую избыточность, но это упражнение для другого вопроса :)
Когда я использовал это решение, оно работает для одного выбора, но выдает ошибку 424 «требуется объект» и отладочную подсветку «Для каждого cl in Intersect (Target, Columns («G»))»
Добавьте еще одну строку над этим: If Intersect(Target, Columns("G")) Is Nothing Then Exit Sub
Где именно? Я могу найти, куда его поставить и заставить его работать. Кроме того, почему вы ссылаетесь на столбец G, поскольку формула должна идти по H, ячейки H - это те, которые изменяются в соответствии со значением в G
Я отредактировал ответ с дополнительной строкой кода.
Результат формулы помещается в столбец H ( cl.Offset(0, 1) ). Это происходит ЕСЛИ И ТОЛЬКО ЕСЛИ изменяется значение в столбце G. Нет необходимости сбрасывать столбец H, когда столбец G остается пустым или статичным/неизмененным, верно?
Спасибо! пока что это решает проблему. Если мне нужно такое же отношение между столбцом G и столбцом J, это будет репликация (cl.Offset (0, 3)). Правильно? . Это работает! Спасибо
По сути, да, но у вас может быть только один обработчик событий Worksheet_Change , поэтому реализация будет немного сложнее. Я смоделирую свой ответ с помощью кода-заполнителя, который поможет вам начать работу в этом направлении.
Читайте также: