- •Общие сведения о табличном процессоре Excel
- •Рабочая книга Excel и ее элементы
- •Понятие объекта, свойства, методы и события
- •Элементы окна приложения Excel
- •Окно документа
- •Настройка Excel
- •Настройка интерфейса Excel
- •Настройка параметров Excel
- •Создание новой рабочей книги
- •Открытие существующей рабочей книги
- •Сохранение рабочей книги
- •Удаление файла рабочей книги
- •Операции над рабочими листами
- •Связывание рабочих листов таблицы
- •Прямое связывание
- •Операции с элементами рабочего листа
- •Ввод данных и формул в ячейки рабочего листа
- •Выражения и операции Excel
- •Типы данных Excel
- •Ввод данных
- •Ввод формул
- •Организация ссылок
- •Ввод специальных данных
- •Автоматическое вычисление сумм
- •Ввод в ячейки таблицы текущих значений даты и времени
- •Режимы вычислений
- •Выбор ручного или автоматического режима вычислений
- •Вычисления с точностью как на экране
- •Сортировка данных таблицы
- •Корректировка табличных документов
- •Перемещение по табличному документу
- •Выделение фрагментов таблицы
- •Редактирование данных
- •Копирование данных и формул
- •Автозаполнение
- •Оформление табличного документа
- •Изменение ширины столбцов
- •Операции над длинным текстом ячейки
- •Изменение высоты строки
- •Выбор шрифта
- •Ввод затенений и цветов
- •Изменение вида выравнивания по горизонтали
- •Изменение расположения текста относительно вертикали
- •Изменение ориентации вывода текста
- •Подготовка табличного документа к печати
- •Предварительный просмотр таблицы перед печатью
- •Печать
- •Работа с диаграммами
- •Создание диаграмм
- •Объекты диаграммы
- •Форматирование объектов
- •Изменение типа диаграммы
- •Добавление к диаграмме новых данных
- •Справочная система MS Excel
- •Использование встроенных функций Excel
- •Суммирование ячеек, удовлетворяющих определенному критерию
- •Подсчет количества значений в диапазоне
- •Подсчет количества пустых ячеек в диапазоне
- •Расчет среднего значения
- •Определение максимального значения
- •Определение минимального значения
- •Генерация случайного числа
- •Функции прогнозирования
- •Определение ранга числа
- •Определение процентной нормы числа
- •Функции для работы с матрицами
- •Функции даты и времени
- •Функция текущей даты
- •Функция текущей даты и времени
- •Функция определения дня недели
- •Функция определения номера месяца
- •Функция определения количества дней между двумя датами
- •Функция проверки условия
- •Использование функции И / ИЛИ
- •Использование функции И
- •Использование функции ИЛИ
- •Функция поиска данных в некотором диапазоне
- •Оценка ежемесячных выплат
- •Работа с именами ячеек
- •Создание имен
- •Замена адресов ячеек их именами
- •Вставка имен в формулы
- •Использование примечаний
- •Подбор параметра
- •Таблицы автоматической подстановки данных
- •Поиск решения
- •Консолидация данных
- •Консолидация по расположению
- •Консолидация по категориям
- •Удаление результатов ошибочной консолидации
- •Одновременная работа с несколькими табличными документами
- •Обмен данными через буфер
- •Динамический обмен данными DDE
- •Связь и внедрение объектов OLE (Active-X)
- •Общие принципы работы с большими таблицами
- •Фиксация на экране титулов
- •Создание нескольких окон для одного табличного документа
- •Разбиение документа на страницы вручную
- •Печать повторяющихся заголовков
- •Работа со списками (базами данных)
- •Создание списка (базы данных)
- •Поиск записей в списке
- •Редактирование записей с помощью формы
- •Удаление записей с помощью формы
- •Добавление записей
- •Подведение промежуточных итогов в списке
- •Фильтрация списка
- •Выбор элементов списка с помощью автофильтра
- •Функции для работы со списками
- •Сводные таблицы
- •Импорт данных в список Excel из внешних источников
1.15.1.Использование функции И
ЕСЛИ (И (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2)
Вычисление выражения 1 выполняется только при истинности всех указанных условий; в противном случае вычисляется выражение 2.
Пример III.18. Для каждой ячейки из диапазона А11:А15 примера 13 проверить условие:
если значение ячейки {Ai} больше 900 и одновременно меньше 1500, то умножить его на 100, в противном случае оставить значение ячейки {Ai} неизменным.
Результат должен быть получен в ячейках С11:С15. В ячейку С11 ввести:
=ЕСЛИ ( И (А11>900; А11<1500);А11*100;А11) .
Сначала вызывается функция ЕСЛИ, затем из списка встроенных функций в левой части строки формул вызывается функция И для ввода логического условия. После завершения ввода условий в соответствующие поля окна И, следует щелкнуть указателем мыши в конце строки формулы и продолжить ввод значений в окне ЕСЛИ.
Из ячейки C11 формулу скопировать вниз в С12:С15.
1.15.2.Использование функции ИЛИ
ЕСЛИ (ИЛИ (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2).
В случае истинности одного из условий (условия 1 или условия 2) расчет текущей величины выполняется по выражению 1; в противном случае выполняется расчет по выражению 2.
Пример III.19. Для каждой ячейки из диапазона А11:А15 примера 13 проверить:
если значение ячейки {Ai} больше или меньше 1000, то умножить его на 10, в противном случае (если равно 1000) оставить значение ячейки {Ai} неизменным.
Результат должен быть получен в ячейках D11:D15. В ячейку D11 вводим:
=ЕСЛИ ( ИЛИ (А11>1000;А11<1000);А11*10;А11) .
Сначала вызывается функция ЕСЛИ, затем их списка встроенных функций в левой части строки формул вызывается функция ИЛИ для ввода логического условия. После завершения ввода условий в соответствующие поля окна ИЛИ, следует щелкнуть указателем мыши в конце строки формулы и продолжить ввод значений в окне ЕСЛИ.
Из ячейки D11 формулу скопировать в D12:D15.
1.16. Функция поиска данных в некотором диапазоне
ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ; ДИАПАЗОН) - группа функций ссылки и
массивы (1-ый вариант - векторный просмотр; 2-ой вариант – массив).
Диапазон представляет блок, состоящий из двух колонок. Поиск ведется по искомому значению в первой колонке диапазона, а в текущую ячейку выбирается соответствующее значение из второй колонки диапазона. Если точное соответствие не обнаружено, то выбирается то наибольшее значение в диапазоне, которое меньше или равно искомому.
Пример III.20. По номеру месяца определить его название. Для этого создается отдельная таблица, где номеру месяца ставится в соответствие его название.
Используя автозаполнение:
-в ячейки с J1:J12 ввести цифры от 1 до 12;
-в ячейки К1:К12 ввести названия месяцев с января по декабрь. В ячейку Н9 ввести любое число от 1 до 12.
В ячейку I9 вставить функцию ПРОСМОТР, выбрав первый способ задания аргументов (по отдельности: вектор просмотра и вектор результата):
47
=ПРОСМОТР(Н9;$J$1:$J$12;$K$1:$K$12) - знак $ устанавливает абсолютные ссылки на адреса областей расположения номеров и названий месяца. (Для фиксации адреса нажмите F4 в конце адреса).
Вячейке I9 появится название соответствующего месяца.
Вячейку Н10 введите новое число от 1 до 12. Скопируйте в ячейку I10 формулу из ячейки I9.
Пример III.21. По номеру месяца от текущей даты определить название месяца. Результат должен быть получен в ячейке I11.
Вячейку I11 вставить функцию ПРОСМОТР, выбрав второй способ задания аргументов (сразу весь массив). В качестве искомого значения укажите номер текущего месяца, воспользовавшись вложенными функциями:
=ПРОСМОТР(МЕСЯЦ(СЕГОДНЯ());$J$1:$K$12).
Вячейке I11 получим название месяца текущей даты.
1.17.Оценка ежемесячных выплат
ППЛАТ(НОРМА;КПЕР;НЗ;БС;ТИП) – группа финансовых функций.
•Норма – норма прибыли за период займа;
•Кпер – общее число периодов выплат годовой ренты;
•Нз – текущая стоимость: общая сумма всех будущих платежей с настоящего момента;
•Бс – будущая стоимость или баланс наличности, которую нужно достичь после последующей выплаты;
•Тип – логическое значение (0 или 1), обозначающее, должна ли
производиться выплата в конце периода (0) или в начале периода (1). Функция ППЛАТ может быть использована для анализа всевозможных ссуд.
Необходимым условием является непротиворечивость аргументов функции.
Пример III.22. Предположим, что нужно воспользоваться 9-ти процентной 15-летней ссудой. Объем ссуды составляет 150000000 рублей. С помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.
1.Перейдите на новый лист.
2.Введите таблицу, начиная с ячейки А1:
1 |
А |
В |
|
Процентная ставка |
9% |
|
|
2 |
Период |
15 |
|
3 |
Удельная ставка |
|
|
4 |
Число выплат |
|
|
5 |
Объем ссуды |
-150000000 |
|
6 |
|
|
|
7 |
Ежемесячная выплата |
|
|
|
|
|
|
3.В ячейки В3 и В4 введите соответствующие формулы:
Процентная ставка (норма) – годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее годовое значение делится на 12 (=В1/12).
Срок действия ссуды – 15 лет, поэтому с учетом 12 платежей в год общее количество месячных выплат (кпер) составит =12 *В2.
4.Для ячейки В7 пошаговыми действиями Мастера функций выполните настройку функции ППЛАТ. После этого в поле Значение диалогового окна Мастера функций вы
48