Лекция 3 ИНФОРМАТИКА Российский университет дружбы народов Экономический

Лекция 3 ИНФОРМАТИКА Российский университет дружбы народов Экономический факультет Кафедра экономико-математического моделирования Курс лекций Москва 2011

Темы лекции: Ш Табличный процессор Microsoft Excel 2010 2

Краткая история табличных процессоров Общепризнанным родоначальником электронных таблиц как отдельного класса прикладного программного обеспечения является Дэн Бриклин, совместно с Бобом Фрэнкстоном разработавший легендарную программу Visi. Calc в 1979 г. Этот табличный редактор для компьютера Apple II стал "убойным приложением", превратившим персональный компьютер из экзотической игрушки для технофилов в массовый инструмент для бизнеса. Впоследствии на рынке появились многочисленные продукты этого класса - Super. Calc, Microsoft Multi. Plan, Quattro Pro, Lotus 1 -2 -3, Microsoft Excel, Open. Office. org Calc, таблицы Apple. Works и gnumeric. 3

Интерфейс Microsoft Excel-2010 q Интерфейс MS Excel 2010 Ш Кнопка «Файл» Ш Лента, вкладки, группы, элементы управления Ш Контекстные меню Ш Строка формул Ш Строка состояния Ш Панель быстрого доступа Ш Мини-панель инструментов Ш Справка Excel 4

Интерфейс Microsoft Excel-2010 Лента Строка формул Кнопка вызова мастера «Вставить функцию» Поле имени Кнопка вызова диалогового окна Кнопка «Развернуть строку формул» Поле выделения всего листа Кнопки прокрутки листов Активная ячейка Ярлычок активного рабочего листа Маркер заполнения 5

Документ Excel 2010 Документ Excel называется книгой. Книга Excel сохраняется на диске в виде одного файла, имеющего расширение xlsx. Книга состоит из одного или нескольких рабочих листов. Рабочий лист представляет собой двумерную таблицу ячеек. Таблица имеет строки и столбцы. По умолчанию строки нумеруются, а столбцам присваиваются одно- , двух- и трехбуквенные имена: A-Z, AA-AZ, BA-BZ, … IV, IW-ZZ, AAA--XFD. Столбцов может быть не более 2^14=24*26^2+6*26+4=16384 (в Excel 2003 – 2^8=9*26+22=256), строк – 2^20=1048576 (в Excel 2003 – 2^16=65536). 6

Окно рабочей книги q Окно рабочей книги Ш Заголовки столбцов и строк Ш Поле выделения всего листа Ш Ярлыки листов и кнопки прокрутки листов 7

Типы данных в ячейках Excel В ячейках Excel могут содержаться данные только трех типов: q Числовые данные q Текст q Формула В Excel нет данных процентного типа, денежного типа, типа даты или времени и т. п. – есть данные числового типа, представленные на рабочем листе в разных форматах (процентном, денежном и др. ). Просмотреть предустановленные в Excel числовые форматы можно так: 1 -й способ: Вкладка «Главная» Группа «Число» Элемент управления «Числовой формат» . 8

Диапазоны (массивы, блоки) в Excel могут быть трех типов: q одномерные q двумерные q трехмерные Примеры. D 4: D 9, D 4: H 4 – одномерные вертикальный и горизонтальный диапазоны, D 4: H 9 – двумерный диапазон, Март!D 4: Июнь!H 9, Лист3!D 4: Лист6!H 9 – трехмерные диапазоны (в трехмерном диапазоне участвуют листы книги в том порядке, в каком они в книге и присутствуют, а не, например, в календарном или числовом, как в приведенных выше примерах!). Диапазоны Excel также называют массивами или блоками. 9

Текстовые и числовые данные q Отображение текстовых данных в ячейках q Отображение числовых данных в ячейках q Редактирование данных в ячейках q Форматы представления числовых данных Просмотреть предустановленные в Excel числовые форматы можно и так: 2 -й способ: Вкладка «Главная» Группа «Ячейки» Формат ячеек… Число. 3 -й способ: Контекстное меню на ячейке Формат ячеек… Число. 10

Числовые данные Пример. Число 1 в процентном формате будет выглядеть на листе как 100%, в денежном – как 1 р. или, например, $1, в одном из форматов времени как 01. 00 0: 00, в одном из форматов даты как 01 января 1900 г. . Excel ведет отсчет времени с первой секунды 0 -го января 1900 года. Да, такой день отсутствовал в календаре, но для программы Excel он был, и число 0 в формате даты он так и представит. Соответственно, число 1 будет отображаться как 1 января 1900 года, число 33 – 2 февраля 1900 года, и т. д. Обратите внимание, целые числа соответствуют времени начала суток (полуночи). Нецелые числа будут в формате времени отображаться как некоторое соответствующее время суток. Например, число 1, 5 – это 12: 00 (полдень) 1 января 1900 года, 1, 75 – 18: 00 (6 часов вечера, прошло ¾ суток) тех же суток. 11

Числовые данные Пример. Число 1 в процентном формате будет выглядеть на листе как 100%, в денежном – как 1 р. или, например, $1, в одном из форматов времени как 01. 00 0: 00, в одном из форматов даты как 01 января 1900 г. . Excel ведет отсчет времени с первой секунды 0 -го января 1900 года. Да, такой день отсутствовал в календаре, но для программы Excel он был, и число 0 в формате даты он так и представит. Соответственно, число 1 будет отображаться как 1 января 1900 года, число 33 – 2 февраля 1900 года, и т. д. Обратите внимание, целые числа соответствуют времени начала суток (полуночи). Нецелые числа будут в формате времени отображаться как некоторое соответствующее время суток. Например, число 1, 5 – это 12: 00 (полдень) 1 января 1900 года, 1, 75 – 18: 00 (6 часов вечера, прошло ¾ суток) тех же суток. 12

Формулы и ссылки на ячейки Ввод формул начинается со знака =. В формулах могут присутствовать константы, ссылки на ячейки, функции. Пример формулы: =12, 46^3+(1, 2/YA$340+ СУММ($C 4; G 6: W 56) – ЕСЛИ(G 11>$F$5; H 3; J 6 -1) В формулах Excel ссылки на ячейки могут быть относительными, абсолютными и смешанными. У абсолютной ссылки перед столбцом и строкой присутствует знак $, у смешанной ссылки знак $ присутствует либо перед столбцом, либо перед строкой, относительные ссылки не содержат знака $. Примеры ссылок: $A$45 (абсолютная), $A 45 и A$45 (смешанные), A 45 (относительная). Главное отличие этих типов ссылок – разное их поведение при копировании ячеек, содержащих формулы со ссылками на ячейки, в другие ячейки. 13

Ссылки на ячейки q Абсолютные ссылки при копировании не изменяются. q Относительные ссылки при копировании перестраиваются. q У смешанных ссылок перестраивается относительная часть. q При переносе формул из одной ячейки в другую все ссылки ведут себя одинаково – не изменяются. Как перестраиваются относительные ссылки, поясним на примере. Пусть в ячейке F 19 содержится формула с относительной ссылкой =C 10. Тогда, например, при копировании этой формулы в ячейку H 13 (на два столбца правее и на 6 строк выше) относительная ссылка и перестроится соответственно, станет такой: =E 4 (на два столбца правее и на 6 строк выше (номер строки уменьшится на 6)). 14

Ссылки на ячейки Допустимо изменять тип ссылок вручную, добавляя и удаляя эти самые $-ры, но профессиональная работа в Excel предполагает использования для этих целей клавиши F 4 (встать на ссылку в строке формул и последовательно нажимая клавишу F 4 выбрать из четырех возможных вариантов необходимый). Тип ссылки выбирает программист, т. е. пользователь. Сама программа Excel никак не сможет предугадать полет Ваших мыслей и при щелчке на ячейке вставит относительную ссылку. 15

Функции В программе Excel 2007 более 800 (852) стандартных функций. Подразделяются они на категории. Есть функции математические, финансовые, даты и времени, статистические, логические, текстовые и др. Для добавления в формулы функций, удобнее всего использовать мастер «Вставить функцию» (кнопка fx слева от строки формул, другой способ – Вкладка «Формулы» Библиотека функций Вставить функцию). Там же можно просмотреть категории функций, списки функций в категориях и получить справку по функциям. Большинство функций имеют аргументы (пример функции, не имеющей аргументов – функция категории «Дата и время» СЕГОДНЯ), 16

Функции некоторые функции требуют строго определенного количества аргументов (например, математическая функция КОРЕНЬ допускает только один числовой аргумент), другие допускают использование разного, но строго определенного количества аргументов (например, логическую функцию ЕСЛИ можно использовать с двумя и тремя аргументами). Есть в Excel и функции, количество аргументов у которых может быть достаточно произвольным (например, математическая функция СУММ допускает от одного до 255 аргументов). Примеры: =((ТДАТА() – СЕГОДНЯ())*24*60 =-1^2 =A 4^(1/3)+($C$4+5)/КОРЕНЬ(ПИ()) Аргументы функций заключаются в круглые скобки и отделяются друг от друга при русских региональных настройках по умолчанию знаком «; » (точка с запятой), при американских настройках – знаком «, » (запятая). 17

Функции Если Вы будете вставлять функции в формулы самостоятельно, не используя мастер «Вставка функции» (так часто делают, если функция проста и хорошо известна пользователю), то рекомендую вводить их имена в нижнем регистре. При правильно введенном имени Excel автоматически переведет имя функции в верхний регистр. В русской версии Excel почти все функции переведены на русский язык. Соответствие русских и американских имен стандартных функций можно найти в системе с установленной программой Excel 2010 в папке %Program. Files%Microsoft OfficeOffice 141049Funcs. xls. Копия этого файла помещена на портале Economist в разделе «Материалы курса» / MS Excel. Используя технологию VBA (Visual Basic for Applications) пользователь также может создавать собственные пользовательские функции, но это не тема нашего курса. Непереведенными остались в основном лишь математические функции. 18