Табличный процессор ms Excel
Файл Excel называется рабочей книгой. Рабочая книга состоит из совокупности листов. Каждый лист представляет собой таблицу, разбитую на ячейки. Ранее такие файлы носили название "электронные таблицы" (так как лист в них был всего один). А программа для работы с ними носит название "процессор электронных таблиц" (processor — обработчик). Приложение Ехсеl и является таким обработчиком.
В нижней части рабочего поля имеются ярлычки листов с именами: Лист1, Лист2... Ярлычок активного листа выделен (он белого цвета, имя листа — полужирным шрифтом). Перемещайтесь с одного листа на другой, щелкая по ярлычкам мышью.
Рабочий лист разбит на ячейки (клетки). Ячейки расположены на пересечении строк и столбцов. Слева и сверху расположены адресные полосы. На левой адресной полосе нанесены номера строк: 1.2,.... На верхней адресной полосе — адреса столбцов: А, В,....Z, АА, АВ,... (Иногда говорят о заголовках строк и столбцов.)
Адрес ячейки составлен из имени столбца и номера строки (как в шахматах). Верхняя левая ячейка имеет адрес А1. Ячейка D3 расположена на пересечении столбца D и третьей строки. Сделайте текущей ячейку D3 с помощью мыши (щелкните мышью в ячейке D3). Обратите внимание, что адрес D3 появился в окне, над столбцом А. Это окно называется полем имени. Переместитесь в АС45 с помощью клавиш управления курсором или мыши. Вернитесь в ячейку А1. Переместитесь в АС45 с помощью команды меню ПравкаПерейти. В диалоговом окне (в поле Ссылка) набираете адрес ячейки АС45 (можно набрать ас45).
Способы перемещения по рабочему листу.Перемещение по соседним ячейкам при помощи клавиш:
<Tab> — слева направо, <Shift+Tab> — справа налево,
<Enter> — сверху вниз, <Shift+Enter> — снизу вверх.
Выделение на рабочем листе.При работе с программными продуктами, функционирующими в среде Windows, основное правило гласит: сначала выделить объект, а затем выполнить над выделенным объектом операцию. Поэтому нужно освоить приемы выделения на рабочем листе.
Для выделения с помощью мыши:
столбца — щелкнуть мышью по верхней адресной полосе на букве (сочетании букв)- заголовке столбца;
нескольких столбцов — не отпуская кнопку после щелчка, протащить мышь по адресной полосе столбцов по соответствующим буквам;
строки — щелкнуть мышью по левой адресной полосе на числе - заголовке строки;
нескольких строк — не отпуская кнопку после щелчка, протащить мышь по адресной полосе строк по соответствующим цифрам;
всех ячеек рабочего листа — щелкнуть мышью по кнопке, расположенной на пересечении адресных полос.
Для снятия выделения достаточно щелкнуть мышью по любой невыделенной ячейке рабочего.
Блоки и их выделение. Блок — прямоугольная область смежных ячеек. Адрес блока — адреса левой верхней и правой нижней ячеек блока, разделенные двоеточием. Например, блок А2:С4 —область, для которой адрес верхнего левого угла А2, а правого нижнего угла С4.
Для выделения блока с помощью мыши: щелкнуть мышью на левой верхней ячейке и, не отпуская кнопку, протащить мышь до правой нижней ячейки.
Для выделения блока с помощью клавиатуры: выделить левую верхнюю ячейку, нажать клавишу <Shift> и, удерживая ее, перемещать курсор стрелками до правой нижней ячейки.
Для выделения совокупности блоков: выделить первый блок, нажать клавишу <Ctrl> и, не отпуская ее, выделить последующие блоки, отпустить клавишу <Ctrl>.
Домашняя самостоятельная работа (Основные операции в Excel)
Упражнение 1 Приемы выделения больших блоков
Нужно выделить А1 :С200.
1 -й способ:
щелкнуть мышью на ячейке А1;
воспользоваться правой полосой прокрутки, чтобы сделать видимой строку 200;
прижать клавишу <Shift> и щелкнуть С200.
2-й способ:
щелкнуть мышью на ячейке А1;
выбрать команду на вкладке Главная > Найти и выделить > Перейти или нажать клавиши CTRL+G;
набрать в поле ввода диалогового окна Переход адрес С200;
прижать клавишу <Shift>
щелкнуть ОК.
Выделите В12:ВМ430. Снимите выделение.
Упражнение 2 Содержимое ячеек, ввод и редактирование. В ячейку можно ввести текст, число или формулу (дата и время, как мы позже увидим, частный случай числа). Для ввода данных (текстовой строки или числа) необходимо переместиться в нужную ячейку, набрать данные и нажать клавишу <Enter>.
Введите в ячейку А1 слово числа, но пока не нажимайте клавишу <Enter>. Обратите внимание, что в строке ввода слева от поля ввода появились три кнопки:
косой крестик – щелчок по нему означает отмену ввода и эквивалентен нажатию клавиши <Esc>,
галочка – щелчок по галочке завершает ввод и эквивалентен нажатию клавиши <Enter> или <Tab>,
Для редактирования (изменения) нужно выделить ячейку и перейти в строку ввода, щелкнув по ней мышью или нажав функциональную клавишу <F2> (редактирование). Редактирование завершается так же, как и ввод. Исправьте содержимое А1: замените строчную букву ч прописной Ч. Выделите А1, нажмите <F2>, нажмите <Home> (перемещение в начало строки), нажмите <Del> и нажмите Ч.
В ячейки А2 и АЗ введите числа 24 и 47. Обратите внимание, что текстовая строка Числа выровнена по левому краю ячейки, а числа 24 и 47 — по правому краю. Введите в ячейку А4 формулу =А2+АЗ. Формула начинается со знака равенства. В ячейке отображается результат вычисления по формуле: 71, а в строке ввода (если выделена ячейка А4) — сама формула. (Если вместо результата вычисления 71 в ячейке отображается #ИМЯ?, проверьте, может быть, Вы ввели формулу русскими буквами).
Исправьте формулу на =А2*2+АЗ (содержимое ячейки А2 умножить на 2 и прибавить содержимое ячейки АЗ). Для этого выделите ячейку А4, нажмите <F2>, внесите нужные исправления нажмите <Enter>.
Введите в ячейку А5 формулу =А4-АЗ, но уже другим способом. Наберите знак равенства (=). Щелкните мышью ячейку А4 (в строке ввода появится ее адрес). Введите знак минус. Щелкните мышью ячейку АЗ. Нажмите <Enter>. Такой способ ввода предпочтительнее, чем непосредственный набор адресов. Обратите также внимание на то, что формула в ячейке А5 использует результат вычисления формулы, размещенной в А4.
Введите в ячейку А2 число -14. Обратите внимание, что в ячейках А4 и А5 изменились результаты. У Вас должно получиться следующее (см. рис. 1).
|
|
A | |
|
1 |
Числа | |
|
2 |
|
-14 |
|
3 |
|
47 |
|
4 |
|
19 |
|
5 |
|
-28 |
Рис. 1
Упражнение 3 Перемещения блоков. В результате выполнения упражнения 2 блок А1:А5 заполнен данными и формулами. Так как этот блок окружен пустыми строками и столбцами, он представляет собой текущую область.
Переместим блок А1:А5 в новое положение D3:D7. Так как перемещение проводится в пределах экрана, сделаем так: выделим блок А1:А5, поместим курсор на границу блока (он примет вид стрелки!), нажимая и не отпуская левую кнопку мыши, переместим его в новое положение D3:D7. Изучите, как изменились формулы в ячейках D6 и D7.
Скопируем блок D3:D7 на второй лист текущей рабочей книги в ячейки В2:В6. Выделим блок D3:D7, поместим его в буфер (команда Копировать вкладка Главная), перейдем на следующий рабочий лист, выделим В2, извлечем из буфера (команда Вставить), нажмем клавишу <Esc>. Обратите внимание, что на втором листе мы выделяем лишь одну ячейку — в нее будет помещен левый верхний угол копируемого блока.
Перемещение блока аналогично копированию, с той лишь разницей, что вместо команды Копировать надо использовать команду Вырезать.
Упражнение 4 Операции с рабочими листами.
Перемещение рабочего листа. Поместим рабочий лист Лист1 вслед за листом Лист2. Укажите мышью на ярлык Лист1, нажмите левую кнопку мыши. Появится изображение маленького листка. Перетащите его мышью вдоль корешков вслед за листом Лист2. В результате после листа Лист2 будет расположен Лист1.
Копирование рабочего листа в пределах рабочей книги. На практике часто приходится дублировать рабочие листы, чтобы вносить в них небольшие изменения, а не проектировать "с нуля". Скопируйте Лист3. Для этого поместите курсор мыши на ярлычок листа, щелкните правую кнопку мыши и в контекстном меню выберите Переместить/скопировать. Появится диалоговое окно Переместить или скопировать. Выберите мышью вариант (переместить в конец), установите флажок Создавать копию (щелкните мышью — появится галочка). В результате появится рабочий лист с именем Лист3(2).
Вставка нового рабочего листа. Выберите во вкладке Главная команду Вставить>Лист. В книге появляется рабочий лист перед активным листом. Вставьте новый рабочий лист перед Лист1. (Обратите внимание на то, как он называется!)
Переименование листа. Листам можно дать новые имена. Поставьте курсор на ярлычок листа Лист1, нажмите правую кнопку мыши. В контекстном меню выберите пункт Переименовать. Дайте имя: Пример. (В дальнейшем, когда будет говориться о выборе пункта из контекстного меню, слова "нажмите правую кнопку мыши", как правило, будут опускаться.) Можно и проще: сделать двойной щелчок по ярлычку листа и ввести его новое имя.
Удаление рабочего листа. Удалите лист с именем Лист3(2): поставьте на его ярлык курсор мыши и в контекстном меню выберите Удалить.
Очистка рабочего листа. Уничтожьте содержимое рабочего листа Лист2. Перейдите на него и щелкните правой кнопкой мыши кнопку на пересечении адресных полос. Будут выделены все ячейки рабочего листа. В контекстном меню выберите Удалить. Лист остался, удалено лишь его содержимое.
Упражнение 5 Вставка пустых строк и столбцов. На листе Пример вставьте три пустые строки перед третьей строкой. Для этого выделите на левой адресной полосе строки 3, 4, 5 и во вкладке Главная выберите команду Вставить>Строки на лист или в контекстном меню выберите пункт Вставить. Аналогично вставьте два пустых столбца перед столбцом А.
Упражнение 6 Заполнение блока одинаковыми числами. Перейдите на Лист2. Требуется ввести во все ячейки блока В1:D9 одно и то же число 5. Выделите блок В1:D9. Введите с клавиатуры число 5. Нажмите сочетание клавиш <Ctrl+Enter>. Снимите выделение. Введите в блок В3:Р3 число 10.
Упражнение 7 Прогрессии. Перейдите на Лист4 и назовите его Прогрессии.
В практической работе часто требуется создавать столбец с порядковыми номерами.
Выделите А2. Выберите во вкладке ГлавнаяЗаполнить Прогрессия. В диалоговом окне введите: Расположение: по столбцам, шаг 1, предельное значение 14. Нажмите кнопку ОК.
Если перед вызовом меню выделить блок для ввода, например А2:А10, то предельное значение задавать не нужно. Проделайте это (предварительно очистив блок АЗ:А15).
Порядковые номера можно ввести и без использования меню. Введем порядковые номера в столбец С, начиная с С2. Для этого введем в С1 число 1, в С2 число 2, выделим блок С1:С2, поставим курсор мыши на маленький черный квадратик в правом нижнем углу ячейки — маркер заполнения
,
и
нажмем на левую клавишу мыши. Не отпуская
ее, потащим маркер
заполнения
вниз (рядом с маркером появится квадратик,
в котором меняется текущий порядковый
номер по мере продвижения маркера
вниз). На ячейке С15
отпускаем левую кнопку мыши. Блок С2:С15
заполнен порядковыми номерами.Заполните блок Е1:Е12 нечетными числами, начиная с 7.
Введите в строку, начиная с С4, арифметическую прогрессию с первым элементом 4, разностью -1, предельным значением -5.
Введите в столбец, начиная с Р6, геометрическую прогрессию с первым элементом 4, знаменателем 2, последним элементом 256. А что получится, если в качестве последнего элемента задать 254?
Упражнение 8 Автозаполнение. С помощью маркера заполнения легко "размножить" строки, заканчивающиеся порядковыми номерами.
Введите в ячейку K6 текст "Товар 1". Поставьте курсор мыши на маркер заполнения этой ячейки и тащите его вниз до ячейки K10. Вы получите в ячейках строки "Товар2", "Товар3" и т.д.
Введите в ячейку L6 текст "1 место". Поставьте курсор мыши на маркер заполнения этой ячейки и тащите его вправо до ячейки N6. Вы получите в ячейках строки "2 место", "3 место".
Проверьте как работает автозаполнение с текстом без чисел, с днями недели, названиями месяцев.
Упражнение9 Форматирование. Вставьте в рабочую книгу новый лист и назовите его Форматирование. Основные команды по форматированию ячеек расположены во вкладке Главная.
Рассмотрим некоторые из этих команд.
Введите в ячейки А1, В1, С1 строки Начислено, Удержано, Долг. Текст в ячейке А1 частично обрезан. Для изменения ширины столбца А необходимо поместить курсор между названиями столбцов А и В (курсор примет вид двунаправленной стрелки), нажать на левую клавишу мыши и, удерживая ее, сдвинуть границу столбца. Аналогичным образом изменяется ширина строки.
Другой способ изменения ширины столбца – выполнить автоподбор ширины столбца. Для этого выделите столбцы А:С и выберите Главная>Формат>Автоподбор ширины. Ширина столбцов будет соответствовать длине строк.
Если при вводе
формулы вместо результата вычисления
в ячейке отображается ######,
это значит, что не хватило места для
вывода результата и необходимо увеличить
ширину ячейки.
Выделите А1:С1 и задайте размер шрифта 14. Вновь выполните автоподбор ширины столбцов. Сделайте текстовые строки полужирными, курсивными, подчеркнутыми. Для этого воспользуйтесь соответствующими кнопками. Слово Удержано выведите шрифтом красного цвета, слово Долг выведите на голубом фоне.
Измените ориентацию текста, разместив текст вертикально (Выравнивание>Ориентация).
Окружите каждую ячейку текущей области рамкой.
Создание заголовка текущей области. Озаглавим текущую область Расчеты. Для этого необходимо вставить строку перед первой и объединить ячейки А1:С1. Чтобы объединить ячейки, их необходимо выделить, затем выбрать соответствующую кнопку на панели инструментов. В результате, вместо трех выделенных ячеек появится одна, текст в которой будет выравниваться по центру. Введите в ячейку А1 слово Расчеты. У Вас должно получиться (см. рис. 2).
A
B
C
1
Расчеты
2
Начислено
Удержано
Долг
Рис. 2
Упражнение 10 Формат числа. Числа, вводимые в ячейку, могут быть представлены в различных форматах. Для задания формата числа используются команды группы число во вкладке Главная. Рассмотрим некоторые из возможных форматов.
Введите в ячейку А3 число 38,787. Выделите эту ячейку и нажмите мышью кнопку Уменьшить разрядность. Число примет вид 38,79. Однако в строке ввода Вы по-прежнему видите 38,787. Само число не изменилось, изменилось лишь его представление: две цифры после десятичного разделителя. Продолжайте щелкать по этой кнопке и изучайте результат. Щелкая кнопку Увеличить разрядность, добейтесь, чтобы в ячейке вновь отображалось 38,787.
Введите в B3 число 45547,678. Нажмите кнопку Денежный формат. Число приобретает вид:
45 547,68 р.
Введите в ячейку C3 число ,12. Будет выведено число 0,12. Щелкните по кнопке Процентный формат. Число примет вид 12%. В ячейку А6 введите 0,14. Также наложите процентный формат. Получится 14%.
Упражнение 11 Формулы. Для расчетов необходимо в ячейки рабочей книги вводить формулы. Формула начинается со знака равенства (), за которым следуют ссылки на ячейки, в которых хранятся данные, имена функций, соединенные знаками математических операций.
Вставьте в рабочую книгу новый лист и назовите его Вычисления. Введите в ячейку А1 букву х, а в ячейку А2 букву у. Присвоим ячейкам В1 и В2 имена х и у: выделим В1, в окне ввода имени над столбцом А (в одной строке со строкой ввода) появится адрес В1, выделим его мышью и наберем х, нажмем <Enter>. Аналогично дадим ячейке В2 имя у.
Поместим в В1 число 4, а в В2 число 3. Введем в ячейку В3 формулу
. ВExcel
она будет выглядеть так: =(1+х)/(4*у)
или без использования имен: =(1+В1)/(4*В2).
В ячейке В3
выводится результат 0,416667.Введем в ячейку В4 формулу
. ВВ4
введем =(х-2)/(5+(2*x)/(y^2+3)).
В ячейке В4
выводится результат 0,352941.В ячейку В6 введите формулу
. Какое сообщение
будет выведено в ячейке? Почему? Исправьте
формулу, прибавив к знаменателю 1.Вычислить
в ячейкеЕ1.
Для
вычислений необходимо использовать
функцию логарифма. Для ввода функций
используем команды во вкладкеФормулы
или кнопку слева от строки ввода
.Откроется диалоговое окно Мастер функций – шаг 1 из 2. Слева – категория функции, справа – список функций внутри категории. Логарифм относится к математическим функциям, поэтому в левом окне выбираем строку Математические, справа находим функцию LOG. В нижней части окна появляется краткое описание функции:
LOG(число, основание_логарифма) возвращает логарифм числа по заданному основанию
Щелкаем ОК и переходим в диалоговое окно Мастер функций – шаг 2 из 2. В этом окне поля для ввода аргументов функции. В окно Число вводим 2, в окно Основание_логарифма вводим 3. Обратите внимание, что ниже и правее поля ввода появится результат 0,630929754. Можно сейчас нажать ОК, а затем перейти к редактированию формулы, чтобы добавить +1, или, находясь во втором окне мастера функций щелкнуть мышью в поле ввода и сразу вслед за сформированной частью формулы =LOG(2;3) ввести +1. Окончательный вид формулы =LOG(2;3)+1. Нажимаем клавишу <Enter>. Результат: 1,63093.
В ячейку E2 ввести формулу для вычисления функции
. Формула
возвращает значение1,610922.Вложенные функции. Введите в ячейку E3 формулу для вычисления
. Эта функция
представляет собой композицию двух
функций
и
. Соответствующие
функцииExcel:
=TAN(x)+1
и =КОРЕНЬ(z),
их
композиция: =КОРЕНЬ(TAN(x)+1),
результат: 1,468952.
Для вставки функции TAN
в качестве
аргумента функции
КОРЕНЬ,
используйте панель функций, которая
открывается под окном имени ячейки
слева от строки ввода.
Упражнение 12 (Ссылки)
Относительные ссылки. Относительная ссылка в формуле основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка, например A1. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки.
Например, запишите в ячейку В2 формулу =А1*2. А теперь скопируйте эту формулу из ячейки B2 в ячейку B3. Посмотрите как формула изменилась, при копировании относительная ссылка А1 автоматически изменилась на A2.
Абсолютные ссылки. Для абсолютной адресации вводится символ $.Абсолютная ссылка ячейки в формуле всегда ссылается на ячейку, расположенную в определенном месте, например $A$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр.
Например, запишите в ячейку С2 формулу =$А$1+В2. А теперь скопируйте эту формулу из ячейки С2 в ячейку С3. Посмотрите как изменилась формула. При копировании ссылка $А$1 остается прежней.
Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.
Например, запишите в ячейку D2 формулу =А$1+В2. При копировании этой формулы в ячейку Е3, смешанная ссылка в формуле изменится с A$1 на B$1.
ПРИМЕЧАНИЕ Используйте функциональную клавишу F4 для изменения типа ссылки (абсолютная/относительная/смешанная)
Занятие 7 (Простые вычисления.)
|
Учет расхода бензина | ||||||||
|
Учетный период |
начало |
1-Мар-09 |
|
|
| |||
|
конец |
31-Авг-09 | |||||||
|
|
|
|
|
|
| |||
|
Дата покупки |
Марка бензина |
Показания aдометра (км) |
Цена литра |
Количество литров |
Стоимость | |||
|
7-Мар-08 |
Аи-95 |
24 170 |
22,2 р. |
18,5 |
..?.. | |||
|
27-Мар-08 |
Аи-95 |
24 370 |
22,2 р. |
13,5 |
..?.. | |||
|
5-Апр-08 |
Аи-95 |
24 434 |
22,3 р. |
5,5 |
..?.. | |||
|
11-Апр-08 |
Аи-95 |
24 596 |
22,3 р. |
8,2 |
..?.. | |||
|
23-Апр-08 |
Аи-98 |
24 862 |
25 р. |
12,9 |
..?.. | |||
|
13-Май-08 |
Аи-98 |
25 057 |
24,9 р. |
13,9 |
..?.. | |||
|
13-Май-08 |
Диз |
25 154 |
19,6 р. |
7,4 |
..?.. | |||
|
11-Июн-08 |
Аи-92 |
25 239 |
20,5 р. |
10,5 |
..?.. | |||
|
12-Июл-08 |
Аи-95 |
25 435 |
22,35 р. |
14,2 |
..?.. | |||
|
19-Июл-08 |
Диз |
25 713 |
19,5 р. |
12,4 |
..?.. | |||
|
28-Июл-08 |
Аи-98 |
26 135 |
24,95 р. |
14,1 |
..?.. | |||
|
7-Авг-08 |
Аи-92 |
26 384 |
20,5 р. |
13,1 |
..?.. | |||
|
7-Авг-08 |
Аи-95 |
26 416 |
22,3 р. |
5,8 |
..?.. | |||
|
23-Авг-08 |
Аи-92 |
26 784 |
20 р. |
15 |
..?.. | |||
|
Средняя цена литра: |
..?.. |
Итого: |
..?.. | |||||
|
Время расходования 1 литра |
(дн/л) |
..?.. | ||||||
|
Пробег на один литр |
(км/л) |
..?.. | ||||||
|
Стоимость одного дня |
(руб./дн) |
..?.. | ||||||
|
Стоимость одного км |
(руб./км) |
..?.. | ||||||
Указания
Создайте документ «Учет бензина», содержащий сведения о расходе бензина некоторым автомобилем с 1 марта по 31 августа.
Задайте соответствующий формат для данных в каждой колонке (финансовый, числовой, дата). См. упражнение 10.
Задайте формулу для вычисления стоимости в первой строке и, используя маркер заполнения, скопируйте ее в нижние ячейки по столбцу «Стоимость».
Вычислите среднюю цену одного литра топлива и общую стоимость, используя функции СРЗНАЧ и СУММ. См. приложение.
Используя арифметические операции и функции сделайте статистические расчеты в конце таблицы.
Для вычисления количества дней между двумя датами из последней даты нужно отнять первую.
Занятие 8 (Построение графиков, условное форматирование.)
|
Сравнительный анализ тарифов оплаты услуг сотовой телефонной связи | |||||||||||
|
Тариф 1: |
1,5 |
рубля за минуту + абонентская плата: |
250 |
| |||||||
|
Тариф 2: |
2,8 |
рубля за минуту, без абонентской платы |
|
|
| ||||||
|
время
минуты |
тариф 1 |
тариф 2 | |||||||||
|
5 |
257,50р. |
14,00р. | |||||||||
|
10 |
265,00р. |
28,00р. | |||||||||
|
15 |
272,50р. |
42,00р. | |||||||||
|
20 |
280,00р. |
56,00р. | |||||||||
|
25 |
287,50р. |
70,00р. | |||||||||
|
30 |
295,00р. |
84,00р. | |||||||||
|
35 |
302,50р. |
98,00р. | |||||||||
|
40 |
310,00р. |
112,00р. | |||||||||
|
45 |
317,50р. |
126,00р. | |||||||||
|
50 |
325,00р. |
140,00р. | |||||||||
|
55 |
332,50р. |
154,00р. | |||||||||
|
60 |
340,00р. |
168,00р. | |||||||||
|
65 |
347,50р. |
182,00р. | |||||||||
|
70 |
355,00р. |
196,00р. | |||||||||
|
75 |
362,50р. |
210,00р. | |||||||||
|
80 |
370,00р. |
224,00р. | |||||||||
|
85 |
377,50р. |
238,00р. | |||||||||
|
90 |
385,00р. |
252,00р. | |||||||||
|
95 |
392,50р. |
266,00р. | |||||||||
|
100 |
400,00р. |
280,00р. | |||||||||
|
105 |
407,50р. |
294,00р. | |||||||||
|
110 |
415,00р. |
308,00р. | |||||||||
|
115 |
422,50р. |
322,00р. | |||||||||
|
120 |
430,00р. |
336,00р. | |||||||||
|
125 |
437,50р. |
350,00р. | |||||||||
|
130 |
445,00р. |
364,00р. | |||||||||
|
135 |
452,50р. |
378,00р. | |||||||||
|
140 |
460,00р. |
392,00р. | |||||||||
|
145 |
467,50р. |
406,00р. | |||||||||
|
150 |
475,00р. |
420,00р. | |||||||||
|
155 |
482,50р. |
434,00р. | |||||||||
|
160 |
490,00р. |
448,00р. | |||||||||
|
165 |
497,50р. |
462,00р. | |||||||||
|
170 |
505,00р. |
476,00р. | |||||||||
|
175 |
512,50р. |
490,00р. | |||||||||
|
180 |
520,00р. |
504,00р. | |||||||||
|
185 |
527,50р. |
518,00р. | |||||||||
|
190 |
535,00р. |
532,00р. | |||||||||
|
195 |
542,50р. |
546,00р. | |||||||||
|
200 |
550,00р. |
560,00р.
| |||||||||
|
205 |
557,50р. |
574,00р. | |||||||||
|
210 |
565,00р. |
588,00р. | |||||||||
|
215 |
572,50р. |
602,00р. | |||||||||
|
220 |
580,00р. |
616,00р. | |||||||||
|
225 |
587,50р. |
630,00р. | |||||||||
|
230 |
595,00р. |
644,00р. | |||||||||
|
235 |
602,50р. |
658,00р. | |||||||||
|
240 |
610,00р. |
672,00р. | |||||||||
|
245 |
617,50р. |
686,00р. | |||||||||
Указания
Создайте документ «Тарифы».
Стоимость за минуту и абонентскую плату для каждого тарифа укажите в отдельных ячейках.
Введите данные в колонку «Время», используя прогрессию. См. упражнение 7.
В колонках «Тариф 1» и «Тариф 2» задайте формулы для вычисления оплаты по правилам этих тарифов. При указании в формуле величины абонентской платы используйте абсолютную ссылку. См. упражнение 12.
На одном графике постройте две прямые, отражающие изменение величины оплаты для обоих тарифов
Выделите данные во второй и третьей колонке, включая шапку, и постройте диаграмму Вставка > Диаграммы. Выберите тип диаграммы График. В качестве подписей для оси Х используйте данные из первого столбца «Минуты». Добавьте соответствующие заголовки для осей и всего графика.
Отразите цветом в таблице момент, когда значения тарифов пересекутся. Для этого установите условное форматирование во второй и третьей колонках так, чтобы цвет изменялся следующим образом:
Для Тарифа 1зеленый цвет ячейки, если значение в ней меньше, чем значение в ячейке справа, и фиолетовый, если значение в ячейке больше, чем значение в ячейке справа.
Для Тарифа 2фиолетовый цвет в ячейке, если значение в ней больше, чем значение в ячейке слева, и зеленый, если значение в ячейке меньше, чем значение в ячейке слева. (Самостоятельно).
Зададим условное форматирование для Тарифа 1.
Для этого выделите данные в первой колонке и вызовите панель Главная > Условное форматирование > Управление правилами > Создать правило. Опишите первое условие и задайте для него формат – зеленый цвет заливки у ячейки.
Точно так же создайте второе правило и опишите второе условие – ЗНАЧЕНИЕ ЯЧЕЙКИ_ БОЛЬШЕ ЛИБО РАВНО_ =С3 и задайте для него формат – фиолетовый цвет заливки у ячейки.
В диспетчере правил у вас должны появиться оба условия.
Самостоятельно задайте условное форматирование для Тарифа 2.
В отдельной ячейке вычислите точно, на какой минуте размеры платежей будут равны. Для этого постройте соответствующее уравнение и выразите Х через известные величины. Полученную формулу занесите в ячейку.
Занятие 9 (Логические функции)
|
Дни недели |
Ночь |
Утро |
День |
Вечер |
Средняя |
Комфорт |
Уточнение |
|
Понедельник |
2 |
7 |
11 |
9 |
7,3 |
Тепло |
Тепло |
|
Вторник |
20 |
9 |
10 |
7 |
11,5 |
Тепло |
Жарко |
|
Среда |
0 |
-5 |
-9 |
-7 |
-5,3 |
Мороз |
Холодно |
|
Четверг |
-1 |
0 |
8 |
5 |
3,0 |
Тепло |
Тепло |
|
Пятница |
-9 |
-12 |
-14 |
-10 |
-11,3 |
Мороз |
Очень холодно |
|
Суббота |
2 |
5 |
2 |
1 |
2,5 |
Тепло |
Тепло |
|
Воскресенье |
-2 |
0 |
-1 |
1 |
-0,5 |
Мороз |
Холодно |
Указания
Заполните первый столбец «Дни недели»– используйте автозаполнение.
Заполните таблицу температур за 7 дней и установите для этих ячеек проверку ввода данных, задав для условие проверки Действительные числа между -70 и 50.
Данные > Проверка
Вычислите среднесуточные температуры используя функцию СРЗНАЧ. См. приложение
В столбце «Комфорт»должно содержаться слово«Мороз», если соответствующаясредняя температураниже нуля, иначе –«Тепло». Для этого используйте функцию ЕСЛИ.См. приложение (Функции Excel)
Столбец «Уточнение»: используйте вложенные функции ЕСЛИ по следующему правилу
если температура меньше -10С, то «очень холодно»;
если температура меньше 0С, то «холодно»;
если температура меньше 10С, то «тепло»;
если температура больше 10С, то «жарко».
Используйте условное форматирование для столбца «Комфорт».
Защитите лист от редактирования, разрешив изменять только значения температур:
Выделите группу ячеек с исходными температурами.
Исключите их из группы защищаемых ячеек. Формат ячеек > Зашита > Защищаемая ячейка(убрать галочку)
Установите защиту листа Рецензирование > Изменения > Защита листа
Занятие 10 (Функция ВПР. Выбор варианта вычисления.)
|
Начисление стипендии по итогам сессии | ||||||||||
|
Базовая стипендия: |
1300,00р. |
| ||||||||
|
| ||||||||||
|
№ |
ФИО |
Оценки |
Стипендия |
Пометить на отчисление | ||||||
|
Англ. язык |
Философия |
Логика |
|
| ||||||
|
1 |
Иванов И. И. |
4 |
5 |
4 |
..?.. |
..?.. | ||||
|
2 |
Петров П. П. |
3 |
4 |
3 |
..?.. |
..?.. | ||||
|
3 |
Сидоров С. С. |
5 |
2 |
4 |
..?.. |
..?.. | ||||
|
4 |
Тихонов Т. Т. |
5 |
5 |
5 |
..?.. |
..?.. | ||||
|
5 |
Романов Р. Р. |
2 |
2 |
2 |
..?.. |
..?.. | ||||
|
|
……… |
|
|
|
|
| ||||
|
|
|
|
|
|
|
| ||||
|
Количество: |
Отличники |
..?.. |
|
|
Хорошисты |
..?.. |
|
|
Троечники |
..?.. |
|
|
Двоечники |
..?.. |
Указания
Постройте таблицу расчета стипендии по итогам сессии как показано выше.
Заполните колонки №, ФИО(не менее 10-ти Фамилий)
Занесите оценки по трем предметам и установите для этих ячеек проверку ввода данных, задав условие проверки Целые числа между 2 и 5.
В столбце Стипендиявычислите величину стипендии для каждого студента по следующим правилам:
Если сессия сдана на все пятерки, (т.е. Минимальная оценка=5)то базовая стипендия увеличивается на 50%,
Если сессия сдана без троек и без двоек, (т.е. Минимальная оценка=4)то базовая стипендия увеличивается на 25%,
Если сессия сдана с тройками, (т.е. Минимальная оценка=3)то назначается базовая стипендия,
Если была хотя бы одна двойка, (т.е. Минимальная оценка=2)то стипендия не назначается.
Этапы решения:
Постройте вспомогательную таблицу, вычислив Величину стипендиипо правилам, описанным выше. В формуле используйте ячейку с базовой стипендией.
Минимальная оценка
Величина стипендии
2
..?..
3
..?..
4
..?..
5
..?..
Вернитесь к основной таблице и, используя функции ВПР и МИН(См. ниже),вычислите стипендию для каждого студента.
Пояснения:
Стипендия каждого студента вычисляется с помощью функции ВПР, где заданы следующие параметры:
Искомое_значение— минимальная оценка студента (функция МИН);
Таблица— диапазон ячеек с данными из вспомогательной таблицы;
Номер_столбца— 2, т.е. из второго столбца вспомогательной таблицы подставить найденное значение.
функция ВПР найдет искомое значение, т.е. минимальную оценку студента,
сравнив ее со значениями самой левой колонки во вспомогательной таблице, найдет соответствующую строку,
и в качестве результата вернет значение из второго столбца в найденной строке, т.е. величину стипендии.
Используя функцию ЕСЛИв столбцеПометить на отчисление, укажите формулу, которая автоматически добавляет символ «*», если у студента все оценки «2».
Упорядочьте таблицу, расположив фамилии в алфавитном порядке.
Главная > Редактирование > Сортировка
Используя функцию СЧЕТЕСЛИ(См. приложение)отдельно посчитайте количество отличников, хорошистов, троечников и двоечников и на основании этих данных постройте круговую диаграмму, отражающую процентное соотношение таких студентов.
Задайте условное форматирование так, чтобы красным цветом помечались "пятерки".
ВПР
Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца массива таблицы.
Синтаксис
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение — значение, которое должно быть найдено в первом столбце табличного массива. Этот аргумент может быть значением или ссылкой.
Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими.
Номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д. Если значение аргумента «номер_столбца»:
Интервальный_просмотр — логическое значение, определяющее, какое соответствие должна найти функция ВПР — точное или приблизительное.
!!! Самостоятельно пересчитайте стипендию с помощью функции ЕСЛИ.
Занятие 11 (Финансовый анализ в Excel. Механизм подбора параметра)
Задача 1
Рассчитайте 30-летнюю ипотечную ссуду со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ, которая возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.
|
Расчет ипотечной ссуды |
|
|
| |
|
Исходные данные |
|
|
|
|
|
Цена |
201 900р. |
|
|
|
|
Первый взнос |
20% |
|
|
|
|
Годовая процентная ставка |
8% |
|
|
|
|
Размер ссуды |
161 520р. |
|
|
|
|
|
Ежегодные выплаты |
Ежемесячные выплаты | ||
|
Срок погашения ссуды |
30 |
лет |
360 |
месяцев |
|
Результат расчета |
|
|
|
|
|
Периодические выплаты |
14 347р. |
|
1 185р. |
|
|
Общая сумма выплат |
430 422р. |
|
426 664р. |
|
|
Общая сумма комиссионных |
268 902р. |
|
265 144р. |
|
Указания
Постройте таблицу расчета ипотечной ссуды как показано выше.
Занесите исходные данные. Размер ссуды вычислите исходя из «Цены» и «Первого взноса»
Сделайте расчет для ежегодных и ежемесячных периодических выплат. Периодические выплаты вычисляются с помощью функции ПЛТ: