Создание сложных формул в Microsoft Excel 97

 

Табличный процессор Microsoft Excel позволяет создавать сложные таблицы, содержащие сложные формулы по расчету математических, логических и текстовых выражений. Существует две возможности ввода сложных формул в электронных таблицах Microsoft Excel: 1) ручной набор текста формулы; 2) использование возможностей мастера функций.

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

В случае использования мастера функций (кнопка [ f_x ]) следует знать следующие правила: 1) сначала вызывается главная (то есть первая) функция из списка функций в требуемой категории; 2) добавление вложенных функций выполняется после щелчка «мышью» в строке ввода формул; 3) нажимать кнопку [ ОК ] или [ Готово ] следует только после полного написания формулы.

Далее приведены три примера на сложные функции.

Пример №1 (для таблицы 1). В графе 3 следует рассчитать: если графа 1 больше 10 и последний символ в графе 2 равен “а”, то выдать текст “верх”, иначеесли графа 1 меньше 5, то выдать текст “низ”, иначе выдать текст “середина”.

Текст формулы (здесь ячейка С2):

= Если (И (А2>10 ;Правсимв (В2;1)=”а”); ”верх” ;Если (А2<5; ”низ”; ”середина”))

 

Таблица 1

	А	В	С
2		Вода
		Воздух
		Земля

 

 

Пример №2 (для таблицы 2). В графе 2 следует рассчитать: если в тексте графы 1 встречается символ “а” или второй символ равен “р”, то убрать первые два символа из текста графы 1, иначе оставить графу 1 без изменений.

 

Текст формулы (здесь ячейка В2):

= Если (Или (Найти (А2; “а”;1); Пстр (А2;2;1)=”р”); Правсимв (А2;3; Длстр (А2)-2); А2)

 

 

Таблица 2

	А	В
	Компьютер
	Факс
	Принтер

 

Пример №3 (для таблицы 3). В графе 2 следует из фамилии, имени и отчества полностью (графа 1) получить фамилию и инициалы.

Текст формулы (здесь ячейка В2):

= Сцепить (Левсимв (A1; Найти (" ";A1;1)); Пстр (A1; Найти (" ";A1;1)+1;1);".";

 

 

Пстр (A1; Найти (" ";A1; Найти (" ";A1;1)+1)+1;1);".")

 

Таблица 3

	А	В
2	Иванов Иван Петрович
	Алексеев Виктор Николаевич
	Маслов Леонид Кириллович

 

 

Задания к лабораторным работам (Excel)

 

ЗАДАНИЕ 1

 

1. Ввести таблицу со следующей структурой:

 

Таблица 1

 

	А	В	C	D	E	F	G	H
	Название	Дата	Кол-во	Цена, у.е.	Цена, руб.	в т.ч. НДС	Себестоимость	Итого
					D5*27	E5*20/120	Е5-F5	С5*Е5

 

2. В графу «Название» ввести названия газет (например, Коммерсант и т.д.)

3. Заполнить строки таблицы. При вводе формул в пустые ячейки использовать операцию копирования.

4. Сохранить таблицу в файле TAB1 (меню Файл).

5. Переименовать ЛИСТ1 в ТАБЛИЦУ (контекстное меню на ярлыке листа).

6. Ввести:

- в ячейку А2: 27 (текущий размер курса доллара США)

- в ячейку А3: =20/120 (коэффициента пересчета НДС из общей суммы)

6.1. Изменить формулы расчета граф «Цена, руб.» и «В т.ч. НДС», используя в них абсолютные адреса ($А$2 и $А$3).

6.2. Удалить по одному знаку $ в каждой формуле строки 5 и выполнить повторное копирование ячеек. Проследить изменения.

7. Сохранить файл под новым именем TAB2 и задать пароль на открытие файла (меню Файл подрежим Сохранить как…).

8. Добавить графу «Код» перед столбцом «Название».

9. Добавить 5 новых строк в таблицу перед последней строкой.

9.1. Заполнить ячейки в новых строках.

10. Скройте две несмежные (не соседние) строки таблицы.

11. Изменить шрифт в графе «Название» на полужирный.

12. Выключить панель инструментов “Форматирование” тремя способами (контур панели инструментов «Форматирование», контекстное меню, меню Вид).

13. Включить панель “Форматирование” тремя способами (контур панели инструментов «Форматирование», контекстное меню, меню Вид).

14. Установить (через меню Формат) денежный формат в графах:

«Цена, руб.»

«В т.ч. НДС»

«Себестоимость»

«Итого»

15. Шапку таблицы закрасить каким-либо узором (меню Формат).

16. Установить в графе «Кол-во» пользовательский формат, добавив к числу комментарий “ тыс.шт. ” (меню Формат)

17. Подсчитать вертикальный итог в графе «Итого», используя функцию на панели инструментов (пиктограмма S).

18. Сохраните изменения в файле ТАВ2 и закройте файл.

 

ЗАДАНИЕ 2

 

1. Откройте файл ТАВ2 и введите на Лист2 таблицу «Оборотная ведомость по синтетическим счетам». Заполнить ее.

1.1. В графе Исходящий остаток кол-во (сумма) ввести формулу: Входящий остаток кол-во (сумма) плюс Приход кол-во (сумма) минус Расход кол-во (сумма).

 

Таблица 1

 

ОБОРОТНАЯ ВЕДОМОСТЬ
по синтетическим счетам за ______________ месяц _____г.
Синтети-ческий счет,	Наиме- нование	Входящий остаток	Приход	Расход	Исходящий остаток
субсчет		кол-во	сумма	кол-во	сумма	кол-во	сумма	кол-во	сумма
… ввести 10 строк

 

2. Использовать для оформления таблицы стандартное оформление (Автоформат “Бухгалтерский1” или “Цветной1”).

3. Отцентрировать заголовок таблицы относительно ширины таблицы.

4. Скопируйте (двумя способами) формат ячеек, содержащих текст кол-во сумма на все ячейки, содержащие текст кол-во сумма.

5. Переименовать Лист2 в ВЕДОМОСТЬ. Сохраните рабочую книгу под именем MASTER.

6. Ввести на Лист3 таблицу “ Динамика изменения курсов акций российских компаний ”. Заполнить ее.

7. Отработать исправление формул тремя способами.

8. Переименовать Лист3 в Динамика.

9. Создать новую рабочую книгу NEW. Скопировать весь лист Динамика из книги MASTER на Лист1 в книге NEW. Закрыть книгу NEW.

10. Заголовок подчеркнуть жирной линией.

11. Установить в шапке таблицы шрифт 12 пунктов, цвет синий.

12. В графе “Компания” изменить шрифт на курсив.

 

 

Таблица 2