 |
Задачи для самостоятельного решения
|
|
|
|
ФИНАНСОВАЯ АКАДЕМИЯ ПРИ ПРАВИТЕЛЬСТВЕ РФ
Кафедра «Информационные технологии»
УТВЕРЖДАЮ
Первый проректор
_______________ М.А.Эскиндаров
«______»_________________200__г.
И.В.Миронова О.Н.Цветкова
Методические материалы по
использованию MS Excel при изучении
дисциплины «Информатика»
Методические указания и задания
Для студентов Института ММЭ и АУ, обучающихся по специальности
08011665 «Математические методы в экономике»
Рекомендовано Ученым советом по специальности «Математические методы в экономике» (протокол № ___от____________200__г.)
Одобрено кафедрой «Информационные технологии»
(протокол № 7 от 2 марта 2006г.)
Москва 2006
ФИНАНСОВАЯ АКАДЕМИЯ ПРИ ПРАВИТЕЛЬСТВЕ РФ
Кафедра «Информационные технологии»
И.В.Миронова О.Н.Цветкова
Методические указания и задания
Методические материалы по
использованию MS Excel при изучении
дисциплины «Информатика»
Для студентов Института ММЭ и АУ, обучающихся по специальности
08011665 «Математические методы в экономике»
Москва 2006
УДК 004.67(078)
ББК32.973.2
М64
Миронова И.В., Цветкова О.Н. Методические материалы по использованию MS Excel при изучении дисциплины «Информатика». Учебное издание для студентов института ММЭ и АУ, обучающихся по специальности «Математические методы в экономике» – 08011665.
– М.: Финансовая академия при Правительстве РФ, кафедра «Информационные технологии», 2006. – 66 с.
Рецензент: Золотарюк А.В., к.т.н., доцент, кафедра «ИТ».
Настоящее пособие предназначено для освоения пакета MS Excel. Оно содержит упражнения и задания для самостоятельной работы, позволяющие студентам закрепить навыки работы в данном приложении.
Пособие предназначено для проведения практических занятий под руководством преподавателя, а также для самостоятельной подготовки студентов.
|
|
|
Миронова Ирина Васильевна
Цветкова Ольга Николаевна
Компьютерный набор: Миронова И.В., Цветкова О.Н.
Компьютерная верстка: Миронова И.В., Цветкова О.Н.
Формат 60х90/16. Гарнитура Times New Roman
Усл. 4 п.л. Изд. № 8.18 – 2006. Электронное издание и Тираж 50 экз.
Заказ № _______
Отпечатано в Финансовой Академии при Правительстве РФ
Полное и частичное воспроизведение или размножение каким-либо способом
допускается только с письменного разрешения Финансовой академии при
Правительстве РФ
© Финансовая академия при Правительстве РФ, 2006
Оглавление:
1 Адресация. 4
1.1 Относительная, абсолютная, смешанная. 4
1.2 Стиль R1C1. 5
1.3 Имена. 5
1.4 Задачи для самостоятельного решения. 8
2 Форматирование. 9
2.1 Числовые форматы, пользовательские форматы.. 9
2.2 Условное форматирование. 11
2.3 Задачи для самостоятельного решения. 12
3 Проверка вводимых данных. 14
3.1 Проверка значений. 14
3.2 Проверка формул. 16
3.3 Задачи для самостоятельного решения. 19
4 Работа с внешними данными. 20
4.1 Получение данных из текстовых файлов (импорт текстового файла) 20
4.2 Задачи для самостоятельного решения. 20
5 Встроенные функции. 21
5.1 Логические. 21
5.2 Финансовые. 22
5.3 Текстовые. 27
5.4 Дата и время. 31
5.5 Табличные формулы (формулы массива) 34
5.6 Категория «Ссылки и значения» (функция ПРОСМОТР) 38
5.7 Разные задачи. 40
6 Построение диаграмм и графиков. 41
6.1 Построение диаграмм. 41
6.2 Построение графиков. 44
6.2.1 Декартова система координат. 44
6.2.2 Полярная система координат. 45
7 Таблицы подстановки. 46
7.1 Создание таблицы подстановки с одной переменной. 46
7.2 Создание таблицы подстановки с двумя переменными. 48
7.3 Задачи для самостоятельного решения. 49
8 Подбор параметра. 50
8.1 Нахождение корней уравнения (подбор параметра) 51
8.2 Нахождение корней уравнения методом деления отрезка пополам. 53
|
|
|
8.3 Задачи для самостоятельного решения. 54
9 Поиск решения (Оптимизация) 55
9.1 Сценарии. 60
9.2 Задачи для самостоятельного решения. 61
10 Списки. 66
10.1 Выбор элементов списка с помощью Автофильтра. 66
10.2 Фильтрация списка с использованием сложных критериев (расширенный фильтр) 67
10.3 Функции баз данных. 68
10.4 Консолидация. 69
10.5 Сводные таблицы.. 70
10.6 Задачи для самостоятельного решения. 70
11 Прогнозирование. 71
Список литературы: 76
Адресация
Относительная, абсолютная, смешанная
Относительная ссылка в формуле, например B7, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.
Абсолютная ссылка в формуле, например $B$7, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется.
Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $B1, $C1 и т. д. Абсолютная ссылка строки приобретает вид B$1, C$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется.
Стиль R1C1
При использовании стиля R1C1 строки и столбцы нумеруются, начиная с единицы. Номер строки указывается после буквы R (row – строка), а номер столбца – после буквы С (column – столбец). В этом стиле также возможны относительные и абсолютные ссылки. Квадратные скобки служат признаком относительности ссылки. По умолчанию все ссылки относительны, т.е. записываются в формате R[1]C[1]. Относительные ссылки могут быть как положительными, так и отрицательными (см. ниже примеры). Абсолютные ссылки записываются без скобок и могут принимать только положительные значения.
Пример 1. R[2]C[2] – Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее;
Пример 2. R[-2]C – Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце;
Пример 3. R[-1] – Относительная ссылка на строку, расположенную выше текущей ячейки;
Пример 4. R2C2 – Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце;
|
|
|
Пример 5. R – Абсолютная ссылка на текущую строку.
Чтобы включить или выключить стиль ссылок R1C1
1. Выберите пункт Параметры в меню Сервис и перейдите на вкладку Общие.
2. В меню Сервис установите или снимите флажок Стиль ссылок R1C1.
Имена
Имя может быть присвоено ячейкам, диапазонам, константам, формулам.
Имена должны удовлетворять некоторым условиям. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть: буквами, числами, точками и знаками подчеркивания. Имя может состоять из строчных и прописных букв, но Microsoft Excel их не различает. Имена не могут иметь такой же вид, как и ссылки на ячейки, например E$100 или R1C1. Пробелы в имени недопустимы. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки. Имя может содержать до 255 знаков.
Способы присвоения имен ячейкам и диапазонам:
1. В окне Присвоение имени, которое можно вывести, выполнив команду Вставка / Имя / Присвоить или нажав комбинацию клавиш Ctrl+F3, введите имя в поле Имя, убедитесь, что в поле Формула указан правильный адрес, и нажмите на кнопку ОК.
2. В поле Имя (раскрывающееся меню, расположенное в левой части строки формул) введите имя для предварительно выделенных ячеек и нажмите клавишу Enter.
3. Чтобы присвоить имена, используя текст смежных ячеек, выделите этот текст и ячейки, которым должно быть присвоено имя, и выполните команду Вставка / Имя / Создать или нажмите комбинацию клавиш Ctrl+Shift+F3.
Имя, которое присваивается ячейке или диапазону обычно можно использовать в любом из листов рабочей книги. Если вы хотите создать имя уровня рабочего листа, то в поле Имя перед именем необходимо задать имя листа, за которым следует восклицательный знак. Например, Лист2!Затраты или 'Новый лист'!Прибыль. Если в имени рабочего листа есть пробелы, его необходимо заключать в кавычки.
Можно присвоить имя ячейкам и диапазонам, находящимся на нескольких рабочих листах. Это делается с помощью "трехмерной" ссылки, которая выглядит следующим образом: ПервыйЛист:ПоследнийЛист! Диапазон.
Например, 'Лист1:Лист3'!$A$1:$C$7.
При удалении первого или последнего листа ссылка будет автоматически откорректирована. При добавлении нового листа между первым и последним, имя будет учитывать и этот новый лист.
|
|
|
Когда вставляется или удаляется строка выше именованного диапазона или столбец слева от него, Excel автоматически изменяет ссылку на этот диапазон так, чтобы она соответствовала новому адресу диапазона. Если новая строка или столбец вставляются внутрь именованного диапазона, то диапазон расширяется и будет включать в себя новые строки и столбцы. При удалении строки или столбца внутри именованного диапазона этот диапазон соответствующим образом изменяется.
Заданное имя можно использовать в формулах. Вводя формулу, вы можете выполнить команду Вставка / Имя / Вставить или нажать клавишу F3. Появится диалоговое окно Вставка имени. Выберите имя из списка и нажмите ОК. Чтобы применить новое имя к формулам рабочего листа выполните команду Вставка / Имя / Применить. Если имя нужно применить только к формулам некоторого диапазона, то предварительно выделите этот диапазон. По умолчанию имена являются абсолютными ссылками.
Чтобы присвоить имя константе, откройте окно Присвоение имени. Введите имя в поле Имя, в поле Формула введите формулу, содержащую константу. Например, =0,02 или ="Москва". Щелкните на кнопке ОК, чтобы закрыть окно.
В поле Формула окна Присвоение имени можно ввести формулу, содержащую обращение к функциям рабочего листа. Например, =ТЕКСТ (СЕГОДНЯ(); "ММММ"). Если мы назовем эту формулу ТекущийМесяц, то введя в любую ячейку рабочей книги формулу = ТекущийМесяц, мы получим в ней название текущего месяца.
В именованных формулах можно использовать и ссылки на ячейки. Например, создав формулу =СУММ(Лист1!$A$2:$A$4) под именем Итог, вы можете в ячейке любого рабочего листа вычислить необходимую сумму просто введя =Итог. В данном случае в формуле использованы абсолютные ссылки. В именованных формулах можно использовать и относительные ссылки. Если находясь в ячейке A5 листа Лист1 формуле =СУММ(Лист1!A2:A4) присвоить имя Итог1, то введя ее в ячейку C5 листа Лист2 вы получите сумму ячеек C2:C4 листа Лист1. Если требуется, чтобы значения брались с текущего листа, формула должна иметь вид =СУММ(!A2:A4). В данном случае восклицательный знак обязателен, ссылки нужно набирать вручную, не используя мышь, и не забудьте, что в ссылках используются латинские буквы.
Имена, не относящиеся к диапазонам, не появляются в поле Имя или диалоговом окне Переход (вызывается клавишей F5). Однако константы и формулы отображаются в диалоговом окне Вставка имени.
|
|
|
Задачи для самостоятельного решения
Задача 1. В A2:A3 расположены значения "вторник", "пятница". Нужно, чтобы они повторялись в ячейках A3:A10.
Задача 2. В блоке B2:B10 расположены числа. Записать в блок C2:C10 формулы, позволяющие получить в нем сумму чисел столбца B нарастающим итогом.
Задача 3. Вычислить значение многочлена f(x)=anxn+an-1xn-1+…+ a1x+ aо, используя следующее соотношение f(x)=(…((anx+an-1)x+ an-2)x +…+ a1)x+ aо. (При n=5, a5=1, a4=3, a3=5, a2=2, a1=0, a0=1, x=0,1, f(x)=1,02531).
Задача 4. Включите стиль ссылок R1C1. Введите данные по образцу:
Заполните таблицу умножения, используя стиль ссылок R1C1.
Задача 5. Вычислите таблицу значений функции f(x,y)=x2-y2, где x меняется от -2 до 3 с шагом 0.25, а y – от 0 до 2 с шагом 0.1. Результаты отображать с тремя знаками после точки.
Задача 6. Составить таблицу цен товаров в магазине. Поля таблицы: «Наименование товара», «Дата поступления товара в магазин», «Цена товара при поступлении», «Текущая цена товара». В магазине товар уценивается каждый раз на 2% от текущей цены через 10, 20, 30 и т.д. дней после поступления, однако цена не может упасть более чем в два раза.
Форматирование
|
|
|
