Лабораторная работа №7. Текстовые функции в MS Excel

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

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

· СЦЕПИТЬ (текст1; текст2;...) -служит для объединения несколько текстовых строк в одну. Здесь текст1, текст2, ... - это элементы текста, объединяемые в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки на одну ячейку. Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор конкатенации «&»;

· СОВПАД(текст1; текст2) -сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ в противном случае. Функция учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для проверки вхождения некоторого текста в документ;

· НАЙТИ(искомый_текст; просматриваемый_текст; нач_позиция) -находит вхождение искомого_текстав просматриваемый_текст и возвращает номер позиции первого вхождения. Здесь нач_позиция - это позиция, с которой будет начат поиск;

Важно!

Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1. Функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.

· ДЛСТР(текст) -возвращает количество знаков в текстовой строке. Здесь текст - текст, длину которого нужно определить, пробелы также учитываются;

· ПСТР(текст; начальная_позиция; число_знаков) -возвращает указанное число знаков из текстовой строки, начиная с указанной позиции. Здесь текст - текстовая строка, содержащая извлекаемые знаки, начальная_позиция - позиция первого знака, извлекаемого из текста, число_знаков указывает, сколько знаков требуется вернуть;

Важно!

Если начальная_позиция больше, чем длина текста, то функция ПСТР возвращает строку "" (пустой текст).

Если начальная_позиция меньше, чем длина текста, но начальная_позиция + количество_знаков больше длины текста, то функция ПСТР возвращает все знаки вплоть до конца текста.

· ЗАМЕНИТЬ(старый_текст; нач_поз; число_знаков; новый_текст) -замещает указанную часть знаков текстовой строки другой строкой текста. Здесь старый_текст - текст, в котором нужно заменить некоторые знаки; нач_поз - позиция в старом тексте, начиная с которой происходит замена; число_знаков равно числу знаков в старом тексте, которые заменяются; новый_текст - текст, на который заменяются знаки старого текста;

· ПОИСК(искомый_текст; текст_для_поиска; нач_позиция) -возвращает позицию первого вхождения текста_для_поиска в искомый_текст, начиная с нач_позиция. Функция ПОИСК используется для поиска вхождения знака или строки текста в другую строку текста, с тем чтобы применить функции ПСТР или ЗАМЕНИТЬ для изменения текста;

Важно!

4В тексте для поиска можно использовать маски ввода: знак вопроса (?) и звездочка (*). Знак вопроса соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти один из этих знаков, то следует поставить перед ними знак тильда (~).

4ФункцияПОИСК не различает строчные и заглавные буквы.

4Если искомый_текст не найден, то возвращается значение ошибки #ЗНАЧ!

· ПОДСТАВИТЬ(текст; стар_текст; нов_текст; номер_вхождения) -подставляет нов_текст вместо стар_текст в текстовой строке, начиная с позиции номер_вхождения.

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

ЗАДАНИЕ 1. Нахождение длины текстовой строки

Определить длину текстовой строки и установить, превышает ли она 10 символов.

1. Запустите программу Microsoft Excel.

2. Переименуйте Лист1 рабочей книги в Длина строки.

3. В ячейку А1 введите произвольный текст, например: Мама мыла раму

4. В ячейку В1 запишите формулу =ДЛСТР(А1). В результате вычислений должна получиться длина введенного текста (14).

5. Определим, превышает ли длина текста 10 символов. Для этого в ячейку А2 введем формулу =СЦЕПИТЬ("Длина текста_";А1;ЕСЛИ(B1<=10;"_не превышает_";"_превышает_");"10 символов").

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

ЗАДАНИЕ 2. Подсчет количества символов, входящих в текстовую строку

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

Частота =

1. Откройте чистый рабочий лист. Переименуйте его в Частота.

2. В ячейку А1 введите текст Текстовая строка.

3. В ячейку А2 введите произвольную текстовую строку.

4. В ячейку В1 введите текст Длина строки.

5. В ячейку В2 введите формулу =ДЛСТР(A2). Появится количество символов веденной текстовой строки.

6. Далее будем рассчитывать количество вхождений символа "а" в текст. В ячейку С1 введите текст Позиции вхождения буквы «а» в текстовую строку.

7. В ячейку С2 введите формулу =НАЙТИ("а";A2). В результате в ячейке появится значение позиции первого вхождения буквы «а» в текстовую строку.

8. Для определения следующего вхождения буквы «а» в текст поиск нужно начинать не с начала текстовой строки, а с позиции, следующей за позицией первого вхождения буквы «а» в текст. Поэтому в ячейку С3 введем формулу =НАЙТИ("а";$A$2;C2+1).

9. Далее выполните копирование формулы из ячейки С3 в ячейки С4, С5 и т.д. до появления сообщения об ошибке #ЗНАЧ!

10. Подсчитаем количество вхождений буквы «а» в текст. Для этого в ячейку D1 введите текст Количество вхождений буквы «а» в текст, а в ячейку D2 формулу =СЧЕТ(диапазон), где в качестве диапазона укажите ячейки с рассчитанными позициями вхождения буквы «а» от С2 до сообщения #ЗНАЧ!

11. В ячейку Е1 введите текст Частота вхождения «а».

12. В ячейке Е2 подсчитайте частоту по формуле.

ЗАДАНИЕ 3. Замена символов в тексте

Заменить в данной текстовой строке все символы «а» на символы «я».

1. Откройте чистый рабочий лист. Переименуйте его в Замена.

2. В ячейку А1 введите текст Текстовая строка.

3. В ячейку А2 введите произвольную текстовую строку.

4. В ячейку В1 введите текст Длина строки.

5. В ячейку В2 введите формулу =ДЛСТР(A2).

6. Определим позиции вхождения символа «а» в текст. В ячейку С1 введите текст Позиции вхождения буквы «а» в текстовую строку.

7. В ячейку С2 введите формулу =НАЙТИ("а";A2). В результате в ячейке появится значение позиции первого вхождения буквы «а» в текстовую строку.

8. В ячейку С3 введем формулу =НАЙТИ("а";$A$2;C2+1).

9. Далее выполните копирование формулы из ячейки С3 в ячейки С4, С5 и т.д. до появления сообщения об ошибке #ЗНАЧ!

10. В ячейку D1 введите текст Замена по позициям.

11. В ячейку D2 введите формулу =ЗАМЕНИТЬ(A2;C2;1;"я"). В ячейке появится текстовая строка, в которой первая встречающаяся буква «а» будет заменена буквой «я».

12. В ячейку D3 введите формулу =ЗАМЕНИТЬ(D2;C3;1;"я").

13. Выполните копирование формулы в диапазоне позиций вхождения буквы «а».

ЗАДАНИЕ 4. Поиск символов в тексте

Установить, входит ли текстовая подстрока "нн" в заданную текстовую строку.

1. Откройте чистый рабочий лист. Переименуйте его в Поиск.

2. В ячейку А1 введите текст Текстовая строка.

3. В ячейку А2 введите произвольную текстовую строку.

4. В ячейку В1 введите текст Проверка.

5. В ячейку В2 введите формулу
=ЕСЛИ(НАЙТИ("нн";A2)<>"#ЗНАЧ!";"Входит";"Не входит").

ЗАДАНИЕ 5. Слова-перевертыши

Словами-перевертышами называются слова, которые читаются одинаково слева направо и справа налево, например КАЗАК. Проверьте, является ли введенное слово перевертышем.

1. Откройте чистый рабочий лист. Переименуйте его в Перевертыши.

2. В ячейку А1 введите текст Слово.

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

4. В ячейку В1 введите текст Длина слова.

5. В ячейку В2 введите формулу =ДЛСТР(А2).

6. В ячейку С1 введите текст Номер позиции.

7. В ячейку С2 введите формулу=В2.

8. В ячейку С3 введите формулу=ЕСЛИ(С2>1;С2-1;"конец ввода").

9. Выполните копирование формулы в ячейки С4, С5 и т.д. до появления диагностического сообщения "конец ввода".

10. В ячейку D1 введите текст Перевернутое слово.

11. В ячейку D2 введите формулу =ПСТР(A2;B2;1). При этом в ячейке должен появиться последний символ введенного слова.

12. Далее начинаем к последнему символу присоединять предыдущие путем их вырезки и сцепления. Для этого в ячейку D3 введите формулу =СЦЕПИТЬ(D2; ПСТР($A$2; С3; 1)).

13. Выполните копирование формулы в ячейки D4, D5 и т.д. до тех пор, пока перевернутое слово не будет сформировано.

14. В ячейку Е1 введите текст Проверка.

15. В ячейку Е2 введите формулу
= ЕСЛИ(перевернутое_слово=А2;"Перевертыш"; "Не перевертыш"). Здесь вместо перевернутое_слово вводится адрес ячейки, где оно записано.

Лабораторная работа №8. Таблица MS Excel как простейшая база данных

Цель работы: познакомиться с возможностями использования электронных таблиц в качестве простейших однотабличных баз данных (БД), а также с основными операциями над базами данных.

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

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

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

Важно!

Для работы используется файл учебного назначения Учебная база данных.xls, который находится в директории D:\Student. Загрузите файл и скопируйте содержимое Листа1 в новую рабочую книгу, которую сохраните в своей папке под именем Кадры.xls. Лист с базой данных назовите Кадры.