Лабораторная работа № 1. Данные. Прогрессии. Списки. Форматы. Мастер функций.

Открыть меню кнопки Пуск сочетанием Ctrl+ Esc или клавишей ÿ (между Alt и Ctrl), из перечня возможных действий выбрать Создать документ Office. В открывшемся окне на вкладке Общие выбрать Новая книга и нажать ОК.  Нажать клавишу F12 (команда «Сохранить как...») и сохранить книгу под новым именем в папке с фамилией студента (эта папка должна быть в составе папки с номером учебной группы).

Присвоить книге пароль для открытия, для этого через меню Сервис-Параметры-Безо­пасность в поле Пароль для открытия файла ввести пароль (сочетание букв, цифр), нажать ОК и ввести пароль еще раз для подтверждения. После этого необходимо сохранить файл с изменениями. Текущее сохранение файла – сочетание клавиш Shift+ F12.

В новой книге по умолчанию открывается какое-то количество листов – 3, 5, др. Этот па­раметр можно изменить через меню Сервис-Параметры-Общие в поле Листов в новой книге. Всего в книге может быть открыто 256 листов. В нижней части рабочего поля листа имеются ярлычки листов: "Лист1", "Лист2".... Новые листы открываются через контекстное меню ярлыка листа пунктом Добавить. Ознакомьтесь с другими пунктами контекстного меню листа. Присвойте текущему листу имя Данные. Можно задать цвет ярлыку листа.

Рабочий лист разбит на ячейки, которые расположены на пересечении столбцов и строк. Столбцы обозначены буквами (латинскими, одной или двумя), на листе -   2⁸ = 256 столбцов; строки – цифрами, на листе 2¹⁶ = 65536 строк. Для перемещения в позицию самого крайнего столбца нажмите сочетание клавиш Ctrl+ à, для перехода на самую последнюю строку надо нажать Ctrl+ â. Для возврата в первую ячейку (начало листа) нажать Ctrl+ Home. Вместо этих клавиш можно нажимать следующие: End, à; End, â; End, Home. Запятая означает, что клавиши нажимаются последовательно (это не сочетания!). 

Перемещаться по ячейкам можно с помощью функциональных клавиш и сочетаний: Enter, Tab, Shift+Enter, Shift+Tab, à, ß, â, á.

Ввод данных в ячейки завершается нажатием клавиш Enter, Tab, Shift+Enter, Shift+Tab. Обратите внимание, куда перемещается выделение ячейки.

Каждая ячейка имеет адрес. Адрес состоит из двух координат: имени столбца (буква или две) и номера строки. Адрес отражается в поле имени, которая располагается над именем столбца А (на рисунке в ней указан адрес ячейки С3). Имя ячейки может содержать от двух до семи символов (например: IV65536 - последняя ячейка листа) – но всегда это означает только две координаты: столбец+строка.

Для перемещения в любую ячейку можно использовать следующие способы:

- с помощью клавиш перемещения курсора (à, ß, â, á);

- с помощью команды меню Правка-Перейти, в окне Переход указать адрес или ссылку на ячейку (например, К25); эта же команда задается сочетанием клавиш Ctrl+ G;

- указав (набрав) адрес ячейки в поле имени (наберите адрес перехода AQ200 – за пределами окна).

Для работы с отдельными данными в ячейках необ­ходимо сначала выделить ячейки, а затем производить действия над ними.

 

Способы выделения элементов листа с помощью мыши или клавиш:

- столбец - щелчок на его заголовке или комбинация клавиш < Ctrl+Пробел >;

- строка -  щелчок по № строки или комбинация клавиш < Shift+Пробел >;

- несколько столбцов или строк подряд  – протаскивание мыши (с нажатой левой клавишей) по диапазону обозначений выделяемых объектов;

- несколько ячеек, столбцов или строк, находящихся в различных местах листа – щелчки мышью (левой клавишей) по обозначениям выделяемых объектов при нажатой клавише Ctrl;

- всех ячеек листа - щелчок по кнопке, расположенной на пересечении адресных полос.

- выделение блока ячеек (блок – прямоугольная область смежных ячеек): щелкнуть мышью по левой верхней ячейке, а затем нажать и держать клавишу Shift и щелкнуть по правой нижней ячейке (по диагонали); можно получить тот же самый результат, протянув курсором с нажатой левой клавишей мыши по блоку ячеек.

Для снятия выделения надо щелкнуть мышью по любой ячейке.

Необходимо потренироваться, выделяя те или иные ячейки, блоки ячеек, столбцы и строки (диапазоны и сочетания). При всей простоте этих действий они требуют определенных навыков.

 

Ввод данных.

Повторяем: для ввода данных необходимо переместиться в нужную ячейку, набрать данные (число, строка или формула) и нажать Enter, Tab, Shift+Enter, Shift+Tab (или щелкнуть кнопку с зеленой галочкой рядом со строкой формул). Введя в ячейки А1 и А2 цифру 5 и слово текст, посмотрите, как они позиционированы относительно границ ячейки (рисунок слева). Число располагается справа, а текст - слева. Это главные признаки числа и текста!

Если завершать ввод данных нажатием кнопки с зеленой галочкой, остается выделенной ячейка, куда были введены данные. Это удобно, если надо в дальнейшем производить операции с этой ячейкой (например, строить из нее прогрессию).

Редактирование данных (особенно формул) лучше всего производить в строке формул. Име­ется возможность редактировать данные или формулы прямо в ячейке – надо нажать функциональную клавишу F2 или дважды щелкнуть мышью по ячейке.

Ввести в ячейку А3 значение 22.25, а в ячейку А4 – 22,25. Какой разделитель дробной части является правильным? -  Тот, который набран в значении, смещенном в ячейке вправо.

Ввести в ячейку А5 дату, например, 22.09.10 – она при вводе преобразуется в дату с 4 циф­рами обозначения года (по умолчанию). Если расширить столбец А, то будет четко видно, что дата смещена к правой границе ячейки, т.е. она – тоже число.

Применим к ячейке А5 сочетание клавиш Ctrl+1 (Формат ячеек, этот пункт можно вызвать в контекстном меню ячейки). Щелкнем по слову Общий (на вкладке Число), нажмем кнопку ОК. Вместо введенной даты появится число 40443 (то есть дата и в самом деле число!). В Excel все даты хранятся именно в виде чисел, которые представляют собой число дней, прошедших с начала прошлого века (с начала 1900 года). Такой способ хранения (единый ноль отсчета в шкале интервалов) позволяет производить с датами арифметические действия. А формат представления даты (вид на экране монитора) можно выбрать из большого набора шаблонов. На рисунке слева возможные форматы отображения даты (только часть форматов!) приведены в столбце Тип:.

Введем в ячейку А6 дату: 15.03.90, а в ячейку А7 формулу: =А5-А6. После нажатия Enter наложим на эту ячейку Общий формат (рисунок справа). Получим разность дат – количество дней между ними. Если считать, что в ячейку А6 введен день рождения, то в ячейке А7 будет число уже прожитых дней на момент той даты, которая введена в ячейку А5. В ходе введенной даты идет следующий день.

 

Проверка данных. Условное форматирование. В Excel имеется возможность формирования запрета на ввод "запрещенных значений". Например, в ячейку А10 надо ввести целое число, большее или равное 10. Если будет введено число, меньшее 10, должно будет выдано сообщение об ошибке. Установим эти параметры проверки вводимых данных.

Выделим ячейку А10. Выбираем команду меню Данные-Проверка. Появится диалоговое окно с тремя вкладками. На первой вкладке (Параметры) выбираем значения в окнах Условие проверки, указанные на рисунке. На второй и третьей вкладке задаем сообщения, которые будут появляться при вводе (например, " Ввести целое число, не меньше 10. ") и сообщение, если нарушится указанное условие.

Кроме того, в Excel имеется средство, с помощью которого легко наложить формат (шрифт, границы, цвет) на ячейку или блок. В зависи­мости от значения, полученного в ячейке, ее формат будет меняться.

На листе Данные ввести в блок А11:А35 натуральный ряд чисел (например, введем в ячейку А11 единицу, а затем размножим ее вниз при нажатой клавише Ctrl до 35 строки. Получим ряд чисел от 1 до 25).

Требуется наложить на А11:А35 следующий формат: если число лежит в диапазоне от 1 до 10, то оно выводится курсивом; если от 11 до 20, то выводится полужирным курсивом в рамке; если больше или равно 21, то выводится красными цифрами на голубом фоне.

Выделить блок А11:А35. Выбрать меню: Фомат-Условное форматирование. В диалоговом окне указать для Условия 1: значение между 1 и 10 (запо­лняется четыре поля ввода – рисунок слева), щелкнуть кнопку "Формат". Появится новое диалоговое окно с тремя вкладками. На вкладке "Шрифт" выбрать "курсив" и щелкнуть "ОК". После этого щелкнуть кнопку "А также»". Диалоговое окно расширится вниз. Заполнить "Условие 2", еще раз щелкнуть "А также»" и заполнить "Условие 3". Нажать ОК. По­смотрите, как выглядит формат ячеек в зависимости от значений в ячейках.

Условное форматирование можно задавать формулой. Введем в К1:К31 прогрессию дат: в К1 запишем 01.01.10, щелкнем по зеленой галочке (закончится ввод значения, появится четыре цифры обозначения года), размножим ячейку вниз до 31 строки – появятся даты от 1 до 31 января 2010 года. Нужно выделить красным цветом ячейку, в кото­рой будет отображаться число, соответствующее Воскресенью.

Выделим диапазон К1:К31, выберем команду: Формат-Условное форматирование. В диалоговом окне надо указать для Условия 1 формулу, как показано на рисунке выше. Щелкнуть кнопку "Формат", вкладку Вид и выбрать красный цвет, щелкнуть ОК, и еще раз ОК. В диапазоне дат столбца В красным цветом будут выделены воскресные дни января 2010 года (рисунок справа).

Построение прогрессий.

В ячейку В1 введем единицу, в ячейку В2 тройку. Выделим эти две ячейки и размножим их до 10 строки. Получим арифметическую прогрессию, первый член которой равен 1, шаг прог­рессии  2, предельное значение 19. Все эти параметры мы ввели вручную.

Построить в столбце С прогрессию четных чисел, начиная с 0 до той же строки.

В столбце D построить арифметическую прогрессию от 0 до 100 с шагом 5.

Кроме ручного способа построения арифметической прогрессии есть способ использования меню. Но – всегда надо в ячейку, откуда начнется построение прогрессии, вручную ввести знач­ениепервого ее члена и щелкнуть по кнопке с зеленой галочкой (или нажать Enter и вернуться в ячейку). Итак, в ячейку Е1 вводим 0, щелкаем по кнопке с зеленой галочкой и вызываем меню Правка -Заполнить-Прогрессия. В появившемся окне Про­грессия выбираем параметры, как по­казано на рисунке слева, и нажимаем кнопку ОК. В столбце Е будет по­строена арифметическая прогрессия – точно такая же, как построенная ранее в столбце D.

Перейти на новый лист, переиме­новать его именем График и в столбце А построить арифметическую прогрессию от 0 до 360 с шагом 10 любым из рассмотренных способов. Вернуться на лист Данные.

Простейшей арифметической прогрессией является натуральный ряд чисел, начиная с любо­го числа.                     Есть несколько способов построения таких прогрессий.

1. Введем в ячейку С11 единицу, а затем размножим ее вниз при нажатой клавише Ctrl до 20 строки. Получим ряд чисел от 1 до 10.

2. Введем в ячейку В11 число 101, а затем размножим ячейку вниз, нажав правую клавишу мыши, до 20 строки и там отпустим клавишу. В появившемся контекстном меню выбрать Прогрессия и сразу щелкнуть ОК. Получим порядковые номера от 101 до 110.

3. Ввести числа 1 и 2 в любые ячейки друг под другом, а затем выделить обе ячейки и раз­множить их вниз до нужной строки.

Прогрессия дат строится еще проще: ввести дату и размножить ее (скопировать ячейку) до нужного предела – даты будут увеличиваться на единицу в каждой следующей ячейке.

Убедимся, что число столбцов на листе – 256. Выделим первую строку и в контекстном меню щелкнем пункт Добавить ячейки. В ячейку А1 введем единицу, щелкнем по кнопке с зеле­ной галочкой, через меню Правка -Заполнить вызовем окно Прогрессия, введем предельное значение 300 и нажмем кнопку ОК. Нажмем сочетание Ctrl+ à, окажемся в самом правом столбце с именем IV, а номер этого столбца, отраженный в 1 строке, будет равен 256.

В отличие от арифметической геометрическую прогрессию можно построить только с по­мощью меню Правка -Заполнить. Введем в ячейку F1 двойку, а в окне Прогрессия зададим следующие параметры: по столбцам, геометрическая, шаг 2, предельное значение 100000, нажмем ОК. Будет построена геометрическая прогрессия, которая представляет собой последовательность двоек в степени, соответствующей номеру строки. Например, в строке 8 будет число 2⁸=256, в строке 10: 2¹⁰=1024, в последней строке: 2¹⁶=65536.

Создание списков.

Если ввести в ячейку строку, которая содержит число (например, "глава 1"), то при размно­жении содержимого ячейки числовое значение будет увеличиваться на единицу ("глава 2", "глава 3", и т.д.). Полезное свойство для заполнения значениями заголовков таблиц, например: "1 квар­тал", "2 квартал" и т.д.

Введем в ячейку G1 буквы Пн и размножим ячейку вниз. Дни недели будут обновляться до Вс, а затем снова повторится список. То же самое произойдет, если будет введено полное назва­ние дня недели.

Аналогичный эффект будет, если в ячейку Н1 ввести янв или январь, а затем размножить ячейку вниз. После декабря снова повторится полный набор месяцев.

Эти эффекты происходят потому, что в меню Сервис-Параметры-Списки имеются те самые перечни значений, которые повторяются при размножении ячеек (рисунок). Можно создать свой список: для этого надо щелкнуть мышью в окне Элементы списка и ввести эти элементы по одному в строке (нажимая после каждого Enter), или все элементы в одну строку, разделяя их запятой, после чего нажать кнопку Добавить. Новый список переместится в левый столбец и его можно будет использовать для автоматического создания списка: ввести значение любого эле­мента и размножить его на заданное число позиций.