Как удалить пользовательскую функцию в эксель

Обновлено: 08.01.2025

Использование макросов VBA в Excel может значительно сэкономить время. Вы можете автоматизировать множество повторяющихся задач и создавать новые функции и возможности в Excel с помощью простых кодов макросов VBA.

Но в некоторых случаях вам может потребоваться удалить все макросы из книги Excel (или удалить только определенные макросы).

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

В этом руководстве я покажу вам несколько действительно простых способов удаления макросов из книги в Microsoft Excel .

Удалите все макросы, сохранив файл в формате XLSX

По умолчанию у вас не может быть кода макроса VBA в формате файла XLSX. Если вы это сделаете, он будет автоматически удален при сохранении файла Excel.

В Excel вы можете использовать макросы только в форматах .XLSM, .XLSB и более старых форматах .XLS. Когда вы сохраняете книгу в любом другом формате, макросы сразу теряются.

Предположим, у вас есть файл с именем Example.xlsm (с макросами), ниже приведены шаги по удалению всех макросов из этого файла:

Это оно! Теперь ваш файл не содержит макросов.

Этот метод удобен тем, что удаляет все макросы из текущей книги Excel за один раз. Однако, если вы хотите удалить некоторые макросы и удалить некоторые, этот метод не подойдет вам (см. Тот, который использует диалоговое окно «Макрос» для этого).

Еще одна хорошая особенность этого метода заключается в том, что у вас все еще есть копия исходного файла со всеми макросами (на случай, если она вам понадобится в будущем).

Удалить определенные макросы из диалогового окна «Макрос»

Хотя предыдущий метод удалял все макросы. это позволяет вам выбрать те, которые вы хотите удалить.

И если вы хотите удалить все макросы, вы также можете это сделать.

Предположим, у вас есть файл с именем Example.xlsm, в котором есть несколько макросов.

Ниже приведены шаги по удалению макроса из этой книги:

Если вы хотите удалить несколько (или все) макросов, повторите шаги 4 и 5.

Кроме того, вы также можете щелкнуть вкладку «Представления», щелкнуть раскрывающийся список «Макросы» и затем выбрать опцию «Просмотр макросов». Это также откроет диалоговое окно Macros.

Хотя этот метод отлично работает, он позволяет удалять только макросы, которые хранятся в модуле редактора Visual Basic. Если у вас есть макросы событий (в определенных листах или ThisWorkbook) или макросы в личной книге макросов, их нельзя удалить с помощью этого метода.

Удалите модуль с макросом

Этот метод дает вам максимальный контроль, поскольку вы можете получить доступ ко всем макросам (будь то в модуле, объектах или личной книге макросов).

Ниже приведены шаги по удалению макроса из редактора Visual Basic:

Если у вас есть модуль с кодом, который вы хотите удалить, вы можете щелкнуть правой кнопкой мыши объект модуля, а затем выбрать опцию «Удалить модуль».

Итак, это три способа удаления макросов из книги Microsoft Excel.

Надстройки расширяют диапазон команд и возможностей Microsoft Excel. По умолчанию они доступны не сразу, поэтому сначала их необходимо установить и (в некоторых случаях) активировать.

Ищете сведения о Office на основе HTML, CSS и JS? Если да, см. статью Office надстройки для Excel.

Некоторые надстройки, такие как "Пакет анализа" и "Поиск решения", встроены в Excel. Другие доступны в Центре загрузки, и их необходимо предварительно скачать и установить. Кроме того, некоторые надстройки создаются сторонними организациями, например поставщиками программных решений или программистами. Это могут быть надстройки модели COM, надстройки Visual Basic для приложений (VBA) и надстройки DLL. Они также требуют установки.

Большинство надстроек можно разделить на три типа, описанных ниже.

Надстройки Excel. Обычно к этому типу относят файлы надстроек Excel (XLAM), Excel 97–2003 (XLA) и надстроек DLL (XLL), а также надстройки автоматизации. Некоторые надстройки Excel, такие как "Пакет анализа" и "Поиск решения", становятся доступны после установки Microsoft Office или Excel. Для использования этих надстроек в большинстве случаев нужно лишь активировать их.

Пользовательские надстройки. Многие разработчики и поставщики решений создают пользовательские надстройки модели COM, надстройки автоматизации, VBA и XLL. Они требуют установки.

После установки надстройки или ее активации соответствующие команды становятся доступны в одном из указанных ниже мест.

Вкладка Данные. После установки и активации надстроек "Пакет анализа" и "Поиск решения" в группе Анализ становятся доступны команды Анализ данных и Поиск решения.

Вкладка Формулы. После установки и активации инструментов для евро в группе Решения становятся доступны команды Пересчет в евро и Формат евро.

Вкладка Надстройки. Другие надстройки можно добавить на вкладку Надстройки. Эта вкладка добавляется на ленту после установки и активации первой надстройки, которая должна отображаться на ней. Если вы не видите вкладку Надстройки, зайдите и перезапустите Excel.

Другие надстройки, такие как вкладка Начало работы, доступны в Excel в других местах, например в виде вкладки на ленте, или через макросы или пользовательские меню.

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

Активация надстройки Excel

На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

Откроется диалоговое окно Надстройки.

В поле Доступные надстройки установите флажок той надстройки, которую необходимо активировать, а затем нажмите кнопку ОК .

Если в окне Доступные надстройки не удается найти надстройку, которую требуется активировать, возможно, ее требуется установить.

Установка надстройки Excel

Чтобы установить надстройку, которая обычно устанавливается вместе с Excel, например "Пакет анализа" или "Поиск решения", запустите программу установки Excel или Microsoft Office и выберите вариант Изменить, а затем выберите требуемую надстройку. После перезапуска Excel она должна появиться в поле Доступные надстройки.

Некоторые надстройки Excel хранятся на компьютере. Чтобы установить или активировать их, нажмите кнопку Обзор (в диалоговом окне Надстройки), найдите надстройку, а затем нажмите кнопку ОК.

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

Другие надстройки, отсутствующие на компьютере, можно скачать и установить с помощью веб-браузера со страницы Загрузки или с других сайтов в Интернете или на сервере организации. Чтобы скачать их, следуйте инструкциям по установке.

Отключение надстройки Excel

На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В поле Доступные надстройки снимите флажок той надстройки, которую нужно деактивировать, а затем нажмите кнопку ОК .

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

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

Удаление надстройки Excel

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

Откройте вкладку Файл и выберите пункт Выход.

Выберите в панели управления пункт Программы и компоненты (для Windows 7 и Windows Vista) или Установка и удаление программ (для Windows XP).

Выполните одно из указанных ниже действий.

Если приложение Excel было установлено в составе пакета Microsoft Office, в списке установленных программ выберите пункт Microsoft Office, а затем нажмите кнопку Заменить.

Если приложение Excel было установлено отдельно, в списке установленных программ выберите название программы, а затем нажмите кнопку Заменить.

Если вы установили надстройку из Центра загрузки, выберите имя программы в списке установленных и нажмите кнопку Удалить.

Следуйте инструкциям программы установки.

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

Добавление надстройки COM

На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

В списке Управление нажмите Надстройки COM, а затем — Перейти.

Откроется диалоговое окно Надстройки COM.

В поле Доступные надстройки установите флажок для той надстройки, которую необходимо установить, и нажмите кнопку ОК.

Совет Если нужная надстройка отсутствует в поле Список надстроек, нажмите кнопку Добавить, а затем найдите надстройку.

Удаление надстройки COM

На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

В списке Управление нажмите Надстройки COM, а затем — Перейти.

Откроется диалоговое окно Надстройки COM.

В поле Список надстроек снимите флажок той надстройки, которую нужно удалить, а затем нажмите кнопку ОК .

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

Чтобы удалить надстройку COM из списка доступных надстроек и с компьютера, щелкните ее имя в поле Список надстроек, а затем нажмите кнопку Удалить.

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

На вкладке Файл выберите команду Параметры, а затем — категорию Надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

Откроется диалоговое окно Надстройки.

Чтобы установить надстройку автоматизации, в поле Доступные серверы автоматизации выберите пункт Автоматизация и щелкните нужную надстройку.

Совет Если нужной надстройки нет в списке, нажмите кнопку Обзор , найдите нужную надстройку, а затем нажмите кнопку ОК .

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

Большинство надстроек можно разделить на три типа, описанных ниже.

Вкладка Данные. После установки и активации надстроек "Надстройка анализа" и "Решение" команды "Анализ данных" и "Найти решение" доступны на вкладке "Данные" на ленте.

Вкладка "Главная". Другие надстройки можно добавить на вкладку Главная.

Активация предварительно установленной Excel надстройки

В меню Инструменты выберите Надстройки.

В поле Доступные надстройки выберите для надстройки, которая вы хотите активировать, и нажмите кнопку ОК.

Установка надстройки Excel

Некоторые Excel надстройки находятся на вашем компьютере, и их можно установить или активировать, нажав кнопку Обзор (в диалоговом окне Надстройки выше), чтобы найти надстройку, и нажав кнопку ОК.

Другие надстройки, недоступные на компьютере, можно скачать и установить с помощью веб-браузера из скачивания или с других сайтов в Интернете, с сервера в организации или с помощью встроенной службы Store в Excel. Чтобы скачать их, следуйте инструкциям по установке.

Установка надстройок из Магазина

На ленте щелкните вкладку Вставка.

Для поиска надстройокиспользуйте поле "Категории" или "Поиск".

Примечание: Некоторые надстройки необходимо приобрести.

Удаление надстройки Excel

На вкладке Вставка нажмите кнопку Мои надстройки.

В Office надстройкищелкните меню 3 точки рядом с надстройки.

Нажмите Удалить.

Ознакомьтесь с уведомлением о других устройствах и нажмите кнопку Удалить.

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в 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). Программные модули бывают нескольких типов для разных ситуаций:

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

Давайте разберем приведенный выше в качестве примера макрос 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) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

При работе с текстовыми данными в Excel вам может потребоваться удалить текст до или после определенного символа или текстовой строки.

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

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

В этом уроке я покажу вам, как удалить текст до или после определенного символа в Excel (на разных примерах).

Итак, давайте начнем с нескольких простых примеров.

Удаление текста после символа с помощью функции поиска и замены

Если вы хотите быстро удалить весь текст после определенной текстовой строки (или перед текстовой строкой), вы можете сделать это с помощью «Найти и заменить» и подстановочных знаков.

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

Ниже приведены шаги для этого:

Вышеупомянутые шаги позволят найти запятую в наборе данных и удалить весь текст после запятой (включая запятую).

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

Как это работает?

Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.

Это связано с тем, что знак звездочки (*) считается соответствием всей текстовой строке, следующей за запятой.

Поэтому, когда вы нажимаете кнопку «Заменить все», она заменяет запятую и весь последующий текст.

Примечание . Этот метод работает хорошо, если в каждой ячейке есть только одна запятая (как в нашем примере). Если у вас несколько запятых, этот метод всегда найдет первую запятую, а затем удалит все после нее. Таким образом, вы не можете использовать этот метод, если хотите заменить весь текст после второй запятой и оставить первый как есть.

Если вы хотите удалить все символы перед запятой, измените запись в поле поиска, поставив перед запятой знак звездочки (* вместо *)

Удалить текст с помощью формул

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

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

Ниже приведена формула для этого:

В приведенной выше формуле используется функция FIND (НАЙТИ), чтобы найти положение запятой в ячейке.

Этот номер позиции затем используется функцией LEFT для извлечения всех символов перед запятой. Поскольку мне не нужна запятая как часть результата, я вычел 1 из результирующего значения формулы поиска.

Это был простой сценарий.

Возьмем немного сложный.

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

Вот формула, которая сделает это:

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

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

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

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

Все идет нормально!

Но что, если в наборе данных непоследовательное количество запятых.

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

Ниже приведена формула, которая сделает это

В приведенной выше формуле функция LEN используется для определения общей длины текста в ячейке, а также длины текста без запятой.

Когда я вычитаю эти два значения, получается общее количество запятых в ячейке.

Таким образом, это дало бы мне 3 для ячейки A2 и 2 для ячейки A4.

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

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

Кроме того, поскольку результат связан с исходными данными, при изменении исходных данных результат будет автоматически обновляться.

Удаление текста с помощью заливки Flash

Он работает, выявляя закономерности, когда вы вручную вводите данные, а затем экстраполируете их, чтобы получить данные для всего столбца.

Поэтому, если вы хотите удалить текст до или после определенного символа, вам просто нужно показать flash fairy, как будет выглядеть результат (введя его вручную пару раз), и flash fill автоматически распознает узор и даст вам результаты.

Позвольте показать вам это на примере.

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

Вот шаги, чтобы сделать это с помощью Flash Fill:

Вышеупомянутые шаги дадут вам результат, как показано ниже:

Вы также можете использовать сочетание клавиш Control + E для Flash Fill после выбора ячеек в столбце результатов (столбец B в нашем примере)

Так что не забудьте еще раз проверить результаты Flash Fill.

И точно так же, как мы удалили весь текст после определенного символа с помощью флэш-заливки, вы можете использовать те же шаги, чтобы удалить текст перед определенным символом. просто покажите вручную в соседнем столбце flash fill, как результат должен выглядеть как мой Интернет, и он сделает все остальное.

Удалить текст с помощью VBA (пользовательская функция)

Вся концепция удаления текста до или после определенного символа зависит от нахождения позиции этого символа.

Как показано выше, найти последнее вхождение этого символа хорошим означает использовать смесь формул.

Если это то, что вам нужно делать довольно часто, вы можете упростить этот процесс, создав настраиваемую функцию с помощью VBA (называемую функциями, определяемыми пользователем).

После создания вы можете повторно использовать эту функцию снова и снова. Это также намного проще и проще в использовании (поскольку большая часть тяжелой работы выполняется кодом VBA в серверной части).

Ниже кода VBA, который можно использовать для создания настраиваемой функции в Excel:

For i = rLen To 1 Step -1

Вам необходимо поместить код VBA в обычный модуль редактора VB или в личную книгу макросов. Когда он у вас есть, вы можете использовать его как любую другую обычную функцию рабочего листа в книге.

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

Ниже приведена формула, которая сделает это:

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

Как видите, это намного короче и проще в использовании по сравнению с формулой с длинным текстом, которую мы использовали в предыдущем разделе.

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

Вот некоторые из писем, которые вы можете использовать для быстрого удаления текста до или после определенного символа в Excel.

Если это простая одноразовая вещь, вы можете сделать это с помощью функции поиска и замены. что, если это немного сложнее, тогда вам нужно использовать комбинацию встроенных формул Excel или даже создать свою собственную формулу с помощью VBA.

Запятая обычно используется при работе как с текстом, так и с числами в Excel. В тексте запятая может использоваться как разделитель (например, разделитель между именем и фамилией или адресом) или как часть обычной грамматики.

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

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

Удалить запятую из чисел

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

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

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

Ниже приведены шаги для этого:
- Выберите набор данных (тот, что с цифрами)
- Щелкните вкладку Главная на ленте
- В группе номеров нажмите кнопку запуска диалогового окна (значок маленькой наклонной стрелки в правом нижнем углу группы)
- Убедитесь, что в диалоговом окне «Формат ячеек» выбрана вкладка «Число».
- В списке «Категория» выберите «Номер» (если он еще не выбран).
- Снимите флажок «Использовать разделитель 1000 (,)» и укажите, сколько десятичных цифр вы хотите.
- Нажмите ОК.
Вышеупомянутые шаги позволят удалить запятую из всех выбранных чисел.

Совет: Вы также можете использовать сочетание клавиш Control + 1, чтобы открыть диалоговое окно форматирования ячеек (после выбора ячеек). Удерживая клавишу Control, нажмите клавишу 1.

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

Использование функции ЧИСЛО ЗНАЧЕНИЕ ( NumberValue )

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

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

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

Ниже приведена формула, которая преобразует этот текст в числа.

Скопируйте эту формулу для всех ячеек в столбце, и у вас будут числа.

Есть вероятность, что вы все еще можете увидеть запятую в результате формулы, но тогда это будет из-за форматирования чисел. Вы можете использовать предыдущий метод, чтобы удалить эти запятые из чисел.

Удалить запятую из текстовой строки

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

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

Позвольте мне показать вам несколько способов сделать это, когда у вас есть текстовые данные.

Использование поиска и замены

С помощью функции «Найти и заменить» вы можете легко найти все запятые в ячейке и заменить их чем-то другим (пустым или другим символом).

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

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

Ниже приведены шаги по удалению запятой из этого набора данных имен:

Вышеупомянутые шаги удалят все запятые из всех выбранных ячеек.

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

Кроме того, это изменит исходный набор данных. Если вы хотите сохранить исходные данные нетронутыми, сначала создайте резервную копию или скопируйте данные на другой лист / диапазон, а затем выполните указанные выше действия.

Использование формулы ЗАМЕНА ( SUBSTITUTE )

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

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

Ниже приведена формула, которая сделает это:

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

С помощью функции SUBSTITUTE вы можете указать количество экземпляров, которые вы хотите заменить. Это можно сделать с помощью четвертого аргумента, который является необязательным аргументом. Если вы не укажете этот необязательный аргумент, функция заменит все экземпляры указанного символа / строки.

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

Ниже приведена формула, которая сделает это:

Если указать номер экземпляра как 1, формула должна заменить только первый экземпляр запятой, а все остальные экземпляры оставить как есть.

Но что, если вы не хотите удалять первую запятую, а вместо этого замените вторую запятую тире.

Вы можете сделать это, используя следующую формулу:

Приведенная выше формула выполняет замену только второго экземпляра запятой и заменяет его тире.

Итак, это несколько простых способов удалить запятую из чисел и текстовых строк в Excel .

Читайте также: