Работа с текстовыми функциями и функциями просмотра

И ссылок в Microsoft Excel 2007

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

Общие сведения

Для преобразования регистра текста используются три функции: ПРОПИСН, ПРОПНАЧ, СТРОЧ. Функция ПРОПИСН преобразует все буквы в прописные, функция ПРОПНАЧ преобразует в прописные только первую букву каждого слова, а функция СТРОЧ преобразует все буквы в строчные.

Синтаксис всех функций одинаков: ПРОПИСН(А), ПРОПНАЧ(А), СТРОЧ(А), где A – ячейка с преобразуемым текстом.

Для объединения текста из разных ячеек используют функцию СЦЕПИТЬ. Синтаксис функции: СЦЕПИТЬ(А), где A – список от 1 до 30 элементов, текст которых требуется объединить. Элемент может быть ячейкой, текстом или числом. Ссылки на пустые ячейки игнорируются. Нельзя использовать ссылки на диапазоны смежных ячеек.

В некоторых случаях необходимо вычислить количество символов в ячейке. Для этого можно использовать функцию ДЛСТР. Синтаксис функции: ДЛСТР(А),

где A – ячейка, в которой требуется вычислить количество символов.

Функции просмотра и ссылок используют для просмотра массивов данных и выбора из них необходимых значений. Для поиска значения в крайнем левом столбце таблицы и соответствующего ему значения в той же строке из указанного столбца таблицы используют функцию ВПР. Синтаксис функции: ВПР(А;В;С;D),

где A – искомое значение.

В– таблица, в которой производится поиск. Может быть задана диапазоном ячеек или именем диапазона;

C – номер столбца таблицы, в котором должно быть найдено соответствующее значение;

D – логическое значение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или отсутствует, то находится приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Для поиска значения в верхней строке таблицы и соответствующего ему значения в том же столбце из указанной строки таблицы используют функцию ГПР. Синтаксис функции: ГПР(А;В;С;D),

где A – искомое значение.

В– таблица, в которой производится поиск. Может быть задана диапазоном ячеек или именем диапазона;

C – номер строки таблицы, в которой должно быть найдено соответствующее значение;

D – логическое значение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или отсутствует, то находится приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Задание

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

Технология работы

Упражнение 1

1. Запустите Microsoft Excel 2007.

2. Создайте новый файл.

3. Перейдите к листу Лист 1, введите данные из табл.29.

Таблица 29

4. Вставьте пустой столбец слева от столбца Товар. В новом столбце с использованием функций исправьте регистр текста в ячейках столбца Товар.

5. Перейдите к листу Лист 2. Введите данные из табл. 30.

Таблица 30

6. В ячейке D2 с использованием функций соедините текст из ячеек А1, А2, В1 и В2 так, чтобы получилась фраза: Фирму Фиалка – курирует менеджер Ревазов. Скопируйте формулу на ячейкиD2:D6.

7. Перейдите к листу Лист 3. Введите данные из табл. 31.

Таблица 31

8. В ячейке D2 с использованием функций создайте такую формулу, чтобы оплата определялась как произведение количества на цену, но при покупке более 20 единиц товара цена уменьшалась на 15%. Скопируйте формулу на ячейки D2:D22.

9. Сохраните файл под именем Lesson_07.

10.Закройте Microsoft Excel 2007.

Упражнение 3

Запустите Microsoft Excel 2007.

Откройте новый файл.

Задание 1

1. Перейдите к листу Лист 1. Введите данные из табл. 32.

Таблица 32

2. Для ячеек столбца Стоимость, руб. (В2:В6) установите денежный формат (р.).

3. Для ячеек столбца Скидка (С2:С6) увеличьте разрядность до четырех знаков после запятой.

4. Для ячеек столбца Скидка, % (D2:D6) установите процентный формат.

5. Для ячеек столбца Дата размещения (E2:E6) установите формат даты в последовательности: месяц (слово), год (четырьмя цифрами), без указания числа (Июль 2010).

6. Для ячеек столбца Дата исполнения (F2:F6) установите формат даты в последовательности: число (цифрами), месяц (цифрами) без указания года (14.3).

7. Для ячеек столбца Стоимость, $ (G2:G6) установите денежный формат ($).

8. Для ячеек столбца Затраты (H2:H9) установить общий формат данных.

9. Для ячеек столбца Количество (I2:I6) установите формат с разделителями разрядов.

10.Для ячеек столбца Код заказа (J2:J6) установите текстовый формат.

Задание 2

1. Перейдите к листу Лист 2. Введите данные из табл.33.

Таблица 33

2. Для ячеек G2:G26 установите числовой формат, отображающие положительные числа шрифтом синего цвета, а отрицательные – шрифтом красного цвета.

3. Для ячеек F2:F26 установите числовой формат, отображающий числа более 50 шрифтом синего цвета, а менее 50 – шрифтом красного цвета.

4. Для ячеек С2:С26 установите числовой формат, отображающий даты в виде дня недели полным словом.

5. Для ячеек В2:В26 установите числовой формат, отображающий после числа текст "штук".

6. Для ячеек А2:А26 установите числовой формат, отображающий перед текстом текст "Печенье".

7. Сохраните файл под именем Lesson_08.

Закройте Microsoft Excel 2007.

Контрольные вопросы

1. Какие функции используются для преобразования регистра текста?

2. С помощью каких функций можно выполнить объединение текста из разных ячеек?

3. Как вычислить количество символов в ячейке?

4. Какой расчет выполняется по функции ВПР?

5. По какой функции выполняется поиск значения в верхней строке таблицы?

Лабораторная работа №5