Excel создать макрос который выводит фамилию

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

Очень часто приходится иметь дело с преобразованием Фамилий Имен и Отчеств в Фамилию и инициалы. Использую следующую формулу:

ЛЕВСИМВ(A2;НАЙТИ(СИМВОЛ(32);A2))&ЕСЛИ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;СИМВОЛ(32);»»))=1;ПСТР(A2;НАЙТИ(СИМВОЛ(32);A2)+1;1);ПСТР(A2;НАЙТИ(СИМВОЛ(32);A2)+1;1)&».»&ПСТР(A2;НАЙТИ(СИМВОЛ(32);A2;НАЙТИ(СИМВОЛ(32);A2)+1)+1;1))&».»

Теперь вопрос:

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

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

P.S.: Недавно кажется нашел подходящий для этого дела макрос с функцией filtr_FIO, но в ячейках где нет Отчества, он почему-то не работает. Файл с макросом прилагаю, может получится его использовать для решения вышеуказанной задачи.

Помогите, пожалуйста…

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

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

1. Вручную через «точку-с-запятой» в поле «Источник».



2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.



3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

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

1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».



2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:



3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.



2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.



4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub
 

5. Сохраняем, установив тип файла «с поддержкой макросов».



6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

1. Создадим первый выпадающий список, куда войдут названия диапазонов.



2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.



3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.



Выбор нескольких значений из выпадающего списка Excel

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

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(0, 1)) = 0 Then
            Target.Offset(0, 1) = Target
        Else
            Target.End(xlToRight).Offset(0, 1) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
        Else
            Target.End(xlDown).Offset(1, 0) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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


Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("C2:C5")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
        If Len(oldval) <> 0 And oldval <> newVal Then
            Target = Target & "," & newVal
        Else
            Target = newVal
        End If
        If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub


Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).



2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.



3. Жмем «Свойства» – открывается перечень настроек.



4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

Скачать пример выпадающего списка

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

Превращаем полное ФИО в Фамилию + Инициалы.

Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в

B1

) и отсутствуют лишние пробелы, то превратить полное ФИО в Фамилию + инициалы можно с помощью этой формулы (см.

файл примера

):

=ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1)&».»&ПСТР(B1;НАЙТИ(» «;B1;НАЙТИ(» «;B1;1)+1)+1;1)&».»

Формула определяет позицию начала Имени и Отчества по пробелам (код символа пробел =32). Очевидно, что порядок следования фамилии и имени с отчеством важен для работы формулы.

Формула

НАЙТИ(» «;B1;1)

в выражении

Иванов Иван Иванович

. Соответственно, выражение

ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1)

выводит фамилию и первый инициал (первую букву имени).

Гарантировать, что в ячейке отсутствуют лишние пробелы можно предварительно применив формулу

СЖПРОБЕЛЫ()

.

Чтобы превратить

Иванов Иван Иванович

(в ячейке

B2

) в

И.И. Иванов

(инициалы перед фамилией) используйте формулу =

ПСТР(B2;НАЙТИ(СИМВОЛ(32);B2;1)+1;1)&».»&ПСТР(B2;ПОИСК(СИМВОЛ(32);B2;НАЙТИ(СИМВОЛ(32);B2;1)+1)+1;1)&». «&ЛЕВСИМВ(B2;НАЙТИ(СИМВОЛ(32);B2;1)-1)

Совет

:

Если бы фамилия, имя и отчество хранились в отдельных ячейках

, то формула была бы гораздо проще

=ЛЕВСИМВ(B1;1)&».»

То же относится и к другим строковым значениям:

Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др.

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

При
составлении различных отчетов и сводок
в качестве названий строк часто

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

экран
столбец с названиями месяцев в ячейках
A1:A12. Для этого выполните

следующие
действия:

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

·
Выполните команду Сервис | Запись
макроса | Начать запись. Появляется

окно
диалога “Запись макроса”.

·
Введите в поле ввода Имя макроса
название создаваемого макроса Месяц_абс.

В
поле ввода Описание введите текст:
Вводит названия месяцев. Нажмите кнопку

Параметры.
Появится окно диалога “Запись макроса”.

·
Введите клавишу быстрого вызова
макроса. Для этого перейдите в поле
ввода

Ctrl,
переключитесь на латинский регистр,
удалите находящуюся там букву и

введите
букву m. После этого нажмите кнопку OK.
Начиная с этого момента,

осуществляется
запись макроса.

·
Выполните последовательность действий,
которую будет выполнять макрос:

установите
курсор в ячейку A1; введите слово январь;
поместите указатель мыши в

правый
нижний угол ячейки A1, при этом указатель
мыши изменит вид на черный

крестик;
нажмите кнопку мыши и, удерживая ее
нажатой, продолжите выделение до

ячейки
A12.

·
Нажмите на кнопку Остановить
макрос.

Если
вы не выполните условие 1 шага, то есть
курсор будет находиться в ячейке

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

нажатием
клавиш Ctrl+m поместите курсор в ячейку
A1.

Макрос
записан и теперь нажатие клавиш Ctrl+m
вызовет появление в ячейках

A1:A12
названий месяцев. При записи макроса
мы не включали режим записи с

относительными
ссылками, поэтому названия месяцев
будут появляться только в

ячейках
A1:A12. Для устранения этого неудобства
предназначен режим записи с

относительными
ссылками.

Пример 2. Создание макроса с относительными ссылками

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

книги.

Перед
созданием макроса включите режим записи
с относительными ссылками. Для

этого
выполните следующие действия:

·
Выполните команду Сервис | Запись
макроса | С относительными ссылками.

·
Введите в поле ввода Имя макроса
название создаваемого макроса —

Заголовок.
В поле ввода Описание
введите текст: Вводит названия

компьютеров.

·
Нажмите кнопку Параметры.
Появится окно диалога “Запись макроса”.

·
Введите наименование добавляемого
пункта меню: Типы ЭВМ. Нажмите кнопку
OK.

·
Выполните последовательность действий,
которую будет выполнять макрос:

введите
заголовок таблицы: Тип компьютера;
нажмите клавишу Enter; введите тип 1

компьютера
CP_35_SR и нажмите клавишу Tab. Аналогично
введите следующие типы

компьютеров
CP_40_SR, CP_55_LS, CP_55_SX. Выделите четыре ячейки
с введенными

типами
компьютеров, выберите команду Формат
| Столбец, а затем команду Подгон

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

·
Нажмите на кнопку Остановить
макрос.

После
окончания записи макроса в меню команды
Сервис
появится пункт с

названием
Типы ЭВМ, которое мы ввели на 4 шаге.

Для
выполнения макроса “Заголовок” нужно
нажать кнопку мыши на пункте с

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

заголовок
следующего вида: Тип компьютера
CP_35_SR CP_40_SR

CP_55_LS
CP_55_SX

Этот
макрос отличается от макроса, созданного
в примере №1 тем, что заголовок

таблицы
можно поместить в любом месте текущей
рабочей книги.

Самостоятельно:

1. Создайте макрос в Excel, который
вычисляет сумму ячеек A1, A2 и выводит
результат в ячейку A3 шрифтом Times New Roman,
курсивом, 12 размером.

2.  Создайте макрос в Excel, который
выводит на желтом фоне синими буквами
в текущую ячейку имя, а справа от нее
фамилию шрифтом Arial, полужирным, 12
размером.

3.    Создайте макрос в Excel, который
выводит красными буквами в ячейку A3
слово “Частное:”, справа
от нее выводит частное ячеек A1 и A2 шрифтом
Times New Roman, курсивом, 12 размера.

4. Запишите макрос, присваивающий
имена выделенным диапазонам

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

6. Создать макрос с именем «Цвет»,
включающий в себя следующие операции:

— выделение диапазона ячеек;

— закрашивание его цветном;

7.  Создайте макрос, который бы
убирал с рабочего листа:

— буквы столбцов и номера строк,
линии сетки, полосы прокрутки, ярлычки
листов, строку формул, строку состояния;

— все  панели инструментов;

8.  Создайте макрос, который бы
восстанавливал стандартный вид окна 
Microsoft Excel.

РЕДАКТИРОВАНИЕ МАКРОСА

Нажмите Alt+F11.
Вы попадете в редактор кода

ИЛИ:

1. На вкладке Разработчик в группе
   Код щелкните Макросы.

2. В поле Имя макроса щелкните макрос,
   который нужно отредактировать.

3. Нажмите кнопку Изменить.

Появится окно кода в редакторе Microsoft
Visual Basic.

Любой макрос начинается со
слова Sub
за которым идет имя макроса и заканчивается
словами End
Sub.

Объяснение кода макроса:

ActiveCell.FormulaR1C1 = «Отв. Исп»
— В активную ячейку записать …..

Range(«C7»).Select
— Ячейку «C7»
выделить.

И т.д.

Обратите внимание, что часто все действия
производятся с объектом Selection,
т.е. каким-то выделенным фрагментом.

Задание

1. В коллективном хозяйстве имеется 8
птицеводческих бригад. Каждая бригада
выращивает различное количество гусей.
В каждой бригаде расходовалось некоторое
количество кормов (тонн) и, соответственно,
увеличивался вес гусей.

Запишите макрос, который

— по введенным исходным данным, выполняет
расчеты и выводит на экран: · исходные
данные в виде таблицы, где указаны
фамилия и инициалы бригадиров, количество
гусей в каждой бригаде, расход комбикормов
в течение каждого месяца в каждой бригаде
и привес всех гусей в течение каждого
месяца в каждой бригаде;

— строит диаграмму по итоговым данным.

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

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

Использовать в макросе вызов встроенной
функции ЕСЛИ

3. Научиться записывать макросы в
Word

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #

  07.11.2018269.82 Кб22А.doc

• #
• #
• #
• #