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