Файл: ms excel. Работа с функциями и формулами.doc

1. 
   Общий формат (установлен по умолчанию).
   
2. 
   Числовой формат – форматирование числа с десятичными разрядами. Например, если введем в ячейку «7», то отобразится «7,00».
   
3. 
   Денежный формат – форматирование денежных сумм с указанием наименования денежных единиц и фиксированным числом знаков после разделителя. Например, если введем в ячейку «10 000», то отобразится «10 000,00 р.».
   
4. 
   Финансовый формат – форматирование аналогично денежному формату, предназначенное в основном для чтения длинных финансовых списков, так как дополнительно выравниваются символы валют и десятичные разряды в столбцах.
   
5. 
   Формат даты – для отображения различных вариантов форматирования дат (короткий, длинный). Дата и время – это тоже числа, обозначающие порядковый номер, начиная с 01.01.1900 г. Например, 12 января 1900 года имеет номер 12, а 12 января 2016 года – 42 381 (т.е. 42 381-й день, начиная с 1.1.1900).
   
6. 
   Формат времени – форматирование числа в вид ЧЧ/MM/СС и подпись AM¹ или PM². Например, «11:15:00 AM».
   
7. 
   Процентный формат – форматирование, при котором значение в ячейке умножается на 100 и визуально добавляется знак %. Например, если введем в ячейку «0,55», то в ней отобразится «55,00 %».
   
8. 
   Дробный формат – форматирование числа в виде дроби. Применяется в случае необходимости отобразить данные в виде числа с косой чертой, например, «1/5». Если этот формат не установлен, то в ячейке отображается соответствующая дата (01.май). Вначале устанавливается дробный формат, а затем только вводятся данные.
   
9. 
   Экспоненциальный формат – форматирование числа в экспоненциальную запись. По умолчанию будет использоваться экспоненциальный формат для ячейки, если в нее записано очень большое целое число или, наоборот, маленькое. Например, при вводе числа «700 000 000 000» в ячейке отображается «7Е+11» или число «0,00000000007», следовательно, в ячейке – «7Е-11».
   
10. 
    Текстовый формат – форматирование исходных и производных данных как текст. Заметим, что в MS Excel используется до 15 значимых разрядов в числе, остальные заменяются нулями. Например, число 7 27 7577 987 123 789 в ячейке будет записано: 7 27 7577 987 123 780, т.е. все разряды после 15-го будут заменены нулями. При обработке числовых данных в экономических расчетах редко можно столкнуться с такими большими числами, но 16-значный номер банковской карты нужно записывать только в текстовом формате, чтобы не потерять последнюю цифру.
    
11. 
    Другие числовые форматы – параметры форматирования ячейки устанавливаются пользователем.
    

1. 
   В ячейку А1 введите число –1, в ячейку В1 – 2.
   
2. 
   Выделите ячейки А1 и В1.
   
3. 
   Потяните маркер заполнения вправо до ячейки J1.
   
4. 
   В ячейку А2 введите название месяца, например, «январь».
   
5. 
   Выделите данную ячейку.
   
6. 
   Потяните маркер заполнения вправо до ячейки L2. Какой месяц оказался в ячейке Н2?
   
7. 
   В ячейку А3 наберите число 2, а в ячейку А4 наберите число 4.
   
8. 
   Выделите эти две ячейки.
   
9. 
   Потяните маркер заполнения вниз до ячейки А10. В результате должен получится столбец, содержащий четные числа от 2 до 16.
   

1. 
   В столбец А введите несколько чисел – 7, 11, 9, 15, 17.
   
2. 
   В ячейку В1 – 18.
   
3. 
   Необходимо каждое число столбца А умножить на значение в ячейке В1. В ячейку С1 введите формулу =А1*В1.
   
4. 
   Потяните маркер заполнения ячейки С1. В итоге получим результат – в заполненных ячейках будут нули, т.к. изменилась адресация.
   
5. 
   Для того, чтобы получить верный результат, необходимо в формуле =А1*В1 адрес В1 сделать абсолютным, т.е. добавить знак $. Следовательно, ячейка будет зафиксирована и при копировании адрес не будет изменяться.
   
6. 
   Далее в формуле =А1*В1 установите курсор на адрес В1. Нажмите клавишу F4. Формула примет вид =А1*$В$1.
   
7. 
   Потяните маркер заполнения ячейки С1. Сравните ваши результаты с рисунками:
   

(а)	(б)

1. 
   В набранном тексте выделите индекс (символ 1 в Строке формул или в ячейке А4).
   
2. 
   Вызовите диалоговое окно Формат ячеек (вкладка Главная – блок Шрифт, рисунок 9).
   
3. 
   В группе переключателей Видоизменение активизируйте нажатием мыши переключатель подстрочный:
   

1. 
   На Ленте вкладка Разметка страницы – блок Параметры страницы, нажав на который открывается диалоговое окно для установки параметров:
   

2. 
   Вкладка Страница, можно установить размер бумаги (обычно по умолчанию установлен стандартный размер А4) и ориентацию листа (обычно по умолчанию установлена Книжная, рисунок 11).
   
3. 
   Поля можно задать в этом же диалоговом окне на вкладке Поля.
   
4. 
   Также в документе можно установить колонтитулы. Например, в этой области расположить данные – ФИО, номер группы, номер лабораторной работы. В таком виде документ выглядит более квалифицированным и информативным.
   
5. 
   Необходимо следить за тем, чтобы таблица не выходила за рамки установленных размеров полей страницы. Для этого следует выполнить действия Файл – Параметры. Откроется диалоговое окно Параметры Excel. Выбрать блок Дополнительно, а затем в группе переключателей Параметры отображения листа поставить флажок Показывать разбиение на страницы (рисунок 12).
   

1. 
   Переименовать лист – Вклад. Создать таблицу:
   
   
2. 
   Установить формат ячейки В2 – Процентный. Для строки 3 «Срок» воспользоваться Автозаполнением.
   
3. 
   В
   ячейку В4 ввести формулу: основной вклад + основной вклад * проценты * срок.
   

4. 
   С
   копировать эту формулу в соседние ячейки, используя маркер заполнения. В ячейках получились нули вместо ожидаемого возрастающего ряда значений. Выбрать ячейку D4 и проверить ссылки в формуле.
   
5. 
   Отредактировать формулу. Перед номером столбца В в ссылках на ячейки В1 и В2 ставится знак $, чтобы при копировании формулы в соседние ячейки ссылка не изменялась (F4).
   

6. 
   Растянуть исправленную формулу за маркер заполнения вправо. Содержимое ячеек заменится новой информацией. В результате получится следующая таблица:
   

1. 
   Создать следующую таблицу на новом листе – Основной вклад.
   
2. 
   В ячейку В2 ввести сумму вклада 100.
   
3. 
   В ячейку Е5 ввести формулу для подсчета суммы вклада N через n лет при условии, что на основной вклад насчитываются проценты р, а основной вклад N₀. Формула имеет вид: основной вклад*(1+проценты*срок) или N = No*(1+p*n)
   

4. 
   Использовать абсолютные и относительные ссылки таким образом, чтобы созданную формулу можно было копировать как по горизонтали, так и по вертикали. В результате – следующая таблица:
   

1. 
   Создать таблицу на новом листе – Индекс массы тела. В строку 1 ввести данные из условия задачи по весу, в столбец А – по росту. Рассчитать по формуле коэффициент для каждого значения веса и роста (маркер заполнения).
   
2. 
   Выделить диапазон ячеек, в которых находятся значения индекса массы тела.
   
3. 
   Г
   лавная – Стили – Условное форматирование – Правило выделения ячеек – Больше. В диалоговом окне указать значении индекса, при превышении которого масса тела считается избыточной.
   
4. 
   Главная – Стили – Условное форматирование – Правило выделения ячеек – Меньше. Указать границу – 18,5, выбрать Зеленая заливка и темно-зеленый цвет.
   

Работа с матрицами

• 
  Количество столбцов аргумента массив 1 должно быть таким же, как количество строк аргумента массив 2, и оба массива должны содержать только числа.
  
• 
  Массив 1 и массив 2 могут быть заданы как интервалы, массивы констант или ссылки.
  
• 
  Если хотя бы одна ячейка в аргументах пуста или содержит текст или если число столбцов в аргументе массив 1 отличается от числа строк в аргументе массив 2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!
  

1. 
   Имя листа – Пример. Умножение матриц.
   
2. 
   Ввести матрицы А и В в ячейки таблицы согласно рисунку.
   
3. 
   Выделить диапазон ячеек для результата умножения.
   
4. 
   Найти формулу умножения матриц (категория математические) – МУМНОЖ и ввести значения аргументов функции. Нажать комбинацию клавиш CTRL+SHIFT+ENTER.
   

5. 
   Применить параметры форматирования согласно рисунку.
   

• 
  Массив может быть задан как интервал ячеек, или как массив констант, или как имя.
  
• 
  Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!
  
• 
  МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.
  
• 
  Определитель матрицы – это число, вычисляемое на основе значений элементов массива.
  
• 
  Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.
  

1. 
   Имя листа – Пример. Определитель матрицы.
   
2. 
   Ввести матрицу А в ячейки таблицы согласно рисунку. Выделить ячейку Е3 для результата.
   
3. 
   Найти функцию определителя (категория математические) – МОПРЕД и ввести значение для аргумента функции.
   

4. 
   Применить параметры форматирования согласно рисунку.
   

• 
  Массив может быть задан как диапазон ячеек, как массив констант, или как имя диапазона или массива.
  
• 
  Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!
  
• 
  МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов.
  
• 
  Формулы, которые возвращают массивы, должны быть введены как формулы массива.
  
• 
  Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными.
  
• 
  Произведение матрицы на ее обратную – это единичная матрица, т.е. квадратный массив, у которого диагональные элементы равны единице, а все остальные элементы равны нулю.
  

• 
  МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к небольшим численным ошибкам округления.
  
• 
  Некоторые квадратные матрицы не могут быть обращены, в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО! Определитель такой матрицы равен нулю.
  

1. 
   Имя листа – Пример. Обратная матрица.
   
2. 
   Ввести матрицу А в ячейки таблицы согласно рисунку.
   
3. 
   Выделить диапазон для размещения обратной матрицы.
   
4. 
   Найти функцию вычисления обратной матрицы (категория математические) – МОБР и ввести значение аргумента функции. Нажать комбинацию клавиш CTRL+SHIFT+ENTER.
   

5. 
   Применить параметры форматирования согласно рисунку.
   

(1)

1. 
   Имя листа – Пример. Правило Крамера.
   
2. 
   В интервале ячеек A1:U9 оформить условие. Систему уравнений ввести с помощью мастера формул.
   
3. 
   Интервал ячеек I11:L14 – матрица А. Вычислить определитель матрицы А в ячейке М16, используя функцию МОПРЕД.
   
4. 
   Интервал ячеек В20-Т23 – матрицы А_j. Используя функцию МОПРЕД, вычислить определители А₁, А₂, А₃, А₄ соответственно в ячейках E25, J25, O25, T25.
   
5. 
   В ячейках Е30, J30, O30, T30 вычислить Х₁, Х₂, Х₃, Х₄ по формуле 1.
   
6. 
   Отформатировать таблицу согласно рисунку.
   

Варианты	Задание	Варианты	Задание
1.		2.
3.		4.
5.		6.
7.		8.
9.		10.
11.		12.

1. 
   Назовите элементы электронной таблицы.
   
2. 
   При решении каких задач целесообразно использовать редакторы электронных таблиц?
   
3. 
   В каких случаях применяется абсолютный адрес?
   
4. 
   Перечислите способы обрамления данных в таблице.
   
5. 
   В каких случаях удобно использовать команду Условное форматирование?
   
6. 
   Какие функции MS Excel предназначены для работы с матрицами?
   
7. 
   Для чего предназначена комбинация клавиш CTRL+SHIFT+ENTER?
   
8. 
   Каким образом в MS Excel можно вызвать редактор формул?