Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

31

Microsoft Excel 2013. Уровень 2. Расширенные возможности

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

ПРОПИСН(Текст) – делает все буквы в строке текста прописными.

UPPER(Text)

=ПРОПИСН("Специалист") СПЕЦИАЛИСТ

СТРОЧН(Текст) – делает все буквы в строке текста строчными.

LOWER(Text)

=СТРОЧН("СПЕЦИАЛИСТ") специалист

ПРОПНАЧ(Текст) – делает прописную первую букву в каждом слове текста, преобразуя все другие буквы в строчные.

PROPER(Text)

ПРОПНАЧ("МОСКОВСКИЙ УНИВЕРСИТЕТ") Московский Университет

При необходимости можно из ячейки забрать необходимые символы. В зависимости от расположения, используются функции ЛЕВСИМВ, ПРАВСИМВ и ПСТР.

ЛЕВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с начала строки

текста.

LEFT(Text; Num_chars)

=ЛЕВСИМВ("Специалист";4) Спец

ПРИМЕР: По исходным данным – Фамилия, Имя и Отчество, получить Фамилия И.О.

32

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРАВСИМВ(Текст;Количество_знаков) – выдает указанное количество символов с конца

строки текста.

RIGHT(Text;Num_chars)

=ПРАВСИМВ("Специалист";4) лист

ПСТР(Текст;Начальная_позиция;Количество_знаков) – возвращает заданное число символов из

строки текста, начиная с указанной позиции.

MID(Text;Start_num;Num_chars)

=ПСТР("Специалист";7;3) лис

Для определения позиции нужного символа можно воспользоваться функциями НАЙТИ или ПОИСК.

НАЙТИ(Искомый-текст;Просматриваемый_текст;Нач_позиция) – определяет позицию начала символа (или символов) в указанной текстовой строке с учетом регистра.

FIND(Find_text;Within_text;Start_num)

=НАЙТИ("ц";"Специалист";1) 4

=НАЙТИ("Ц";"Специалист";1) #ЗНАЧ!

ПОИСК(Искомый_текст;Текст_для_поиска;Нач_позиция) – определяет позицию первого вхождения символа или строки текста в указанной текстовой строке без учета регистра.

SEARCH(Find_text;Within_text;Start_num)

=ПОИСК("Ц";"Специалист";1) 4

=ПОИСК("ц";"Специалист";1) 4

ДЛСТР(Текст) – определяет количество символов в текстовой строке.

LEN(Text)

=ДЛСТР("Специалист") 10

ПРИМЕР: Из исходных данных получить символы до # и символы после #.

33

Microsoft Excel 2013. Уровень 2. Расширенные возможности

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

ЗНАЧЕН(Текст) – преобразует текстовый аргумент в число.

VALUE(Text)

ПРИМЕР: Преобразовать результат вычисления текстовой функции в числовой тип данных.

ПРАКТИКУМ:

Открыть файл 01_6 Текстовые функции.

На листах ЗАДАНИЕ1, ЗАДАНИЕ2, ЗАДАНИЕ3, ЗАДАНИЕ4, ЗАДАНИЕ5 решить задачи в соответствии с заданиями.

Сохранить изменения в файле.

Разбиение текста по столбцам

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

1.Выделить столбец с исходными данными.

2.На вкладке Данные [Data], в группе в группе Работа с данными [Data Tools]

выбрать Текст по столбцам [Text to Columns].

3.В диалоговом окне Мастер распределения текста по столбцам – шаг 1 из 3 [Convert Text to Columns Wizard – Step 1 of 3] указать формат исходных данных:

с разделителями [Delimited] - содержимое одного столбца от другого отделено знаком (пробел, табуляция, точка с запятой, запятая и др.).

фиксированной ширины [Fixed width]: выбираем, если в тексте каждый столбец состоит из одинакового количества символов.

Нажать Далее [Next].

34

Microsoft Excel 2013. Уровень 2. Расширенные возможности

4.На следующем шаге окна Мастер распределения текста по столбцам – шаг 2 из 3 [Convert Text to Columns Wizard – Step 2 of 3] в зависимости от формата исходных данных, выбранных на шаге 1 работы мастера, сделать следующие настройки:

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

другой [other].

Фиксированной ширины

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

Нажать Далее [Next].

5.На следующем шаге окна Мастер распределения текста по столбцам – шаг 2 из 3 [Convert Text to Columns Wizard – Step 3 of 3] настроить формат данных для каждого столбца. Для этого выделить столбец в Образце разбора данных [Data preview] и выбрать Формат данных столбца [Column data format]:

общий [General] – автоопределение типа данных.

текстовый [Text] – значения будет текстовыми данными.

дата [Date] – выбрать для дат нужный вариант: ДМГ, ГМД МДГ и т.д., если порядок расположения составляющих даты отличается от используемого.

Подробнее [Advanced] –

установить Разделитель

целой и дробной части

[Decimal Separator], а так же Разделитель разрядов

[Thousands separator],

если они отличаются от ваших региональных стандартов.

35

Microsoft Excel 2013. Уровень 2. Расширенные возможности

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

В поле Поместить в [Destination] указать ячейку для размещения результата разбиения текстовых данных, нажать Готово [Finish].

ПРАКТИКУМ:

Открыть файл 01_6 Текстовые функции.

На листе ЗАДАНИЕ6 решить задачу в соответствии с заданием.

Сохранить изменения в файле и закрыть его.

Мгновенное заполнение

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

1.Ввести в первую ячейку текст-шаблон для заполнения. Выделить ячейку.

2.На вкладке Данные [Data], в группе в группе Работа с данными [Data Tools]

выбрать Мгновенное заполнение [Flash Fill].

ПРАКТИКУМ:

Открыть файл 01_6 Текстовые функции.

На листе ЗАДАНИЕ7 решить задачу в соответствии с заданием.

Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 6.

Функции даты и времени

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