Курносова А.П., Улезько А.В. Практикум по информатике

Подождите немного. Документ загружается.

Чтобы воспользоваться при вводе данных списком для выбора

имеющих ограничения ячеек, установите флажок

Список допустимых

значений

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

ны ограничения, пусты или отсутствует список корректных данных, сни-

мите флажок

Игнорировать пустые ячейки.

Чтобы выводить подсказку, а также давать пояснения по поводу не-

корректных данных или предотвращать их ввод, укажите типы сообщений

на вкладках

Сообщение для ввода и Сообщение об ошибке.

Если список корректных данных мал, его можно вводить непосред-

ственно в поле

Источник, используя разделитель элементов списка – точ-

ку с запятой (;). Так, допустимо набрать

Низкий; Средний; Высокий в

поле

Источник. Если список большой можно ввести эти данные в ячейки

на листе и в поле

Источник сделать ссылку на эти ячейки.

Ограничение колич

ества вводимых в ячейку символов

Выделите ячейки, на которые нужно наложить ограничения.

Выберите команду Проверка в мен

ю Данные, а затем откройте

вкладку

Параметры.

Выберите команду

Длина текста в окне Тип данных.

Выберите нужный оператор в окне

Значение. Для этого оператора

следует указать границы диапазона. Границами диапазона могут служить

числовые значения, ссылки на ячейки и формулы.

Флажок

Игнорировать пустые ячейки позволяет оставить ячейку,

на которую наложены ограничения, пустой, а также установить границы,

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

формулу, в которую входит такая ячейка.

Чтобы задействовать ограничения, которые были наложены на пус-

тые ячейки так, как если бы эти ячейки содержали нули, снимите флажок

Игнорировать пустые ячейки.

Чтобы выводить подсказку, а также давать пояснения по поводу не-

корректных данных или предотвращать их ввод, укажите типы сообщений

на вкладках

Сообщение для ввода и Сообщение об ошибке.

Определение правильности вводим

ых данных с помощью формулы

Выделите ячейки, на которые нужно наложить ограничения.

Выберите команду Проверка в мен

ю Данные, а затем откройте

вкладку

Параметры.

Выберите тип

Другой в окне Тип данных.

В поле

Формула введите формулу для расчета логического значения

(ИСТИНА или ЛОЖЬ). Данные, вводимые в ячейку, считаются некоррект-

ными, если результатом вычислений является значение ЛОЖЬ. Перед

формулой необходимо поставить знак равенства (=).

211

Например, на листе производится расчет прибыли путем вычитания

суммы вводимых издержек из расчетной суммы дохода. Может возникнуть

необходимость поддерживать прибыль на уровне, превышающем опреде-

ленный процент издержек. В этом случае для ячеек D6:D13, служащих для

ввода издержек, следует определить допустимые данные с помощью фор-

мулы следующего вида: =Прибыль > СУММ(D6:D13) * 15%, где «При-

быль» — это

имя ячейки.

Поиск некорректных данных на ли

сте

Во время проверки листа Excel находит все ячейки, которые содер-

жат значения, противоречащие ограничениям, заданным с помощью ко-

манды Проверка в меню Данные. Сюда входят значения, неп

осредст-

венно введенные в ячейку, значения, полученные в результате вычислений

по формулам, копирования или автозаполнения, а также значения, которые

были помещены в ячейки

макросами.

Убедитесь, что отображается панель инструментов

Зависимости.

Для этого выберите

Зависимости в меню Сервис, а затем проверьте, что

установлен флажок для отображения панели

Зависимости.

На панели инструментов

Зависимости нажмите кнопку Обвести

неверные данные

Чтобы узнать, какие сообщения и ограничения на данные имеет

ячейка, выберите ячейку, а затем – команду Проверка в меню Данные.

Управление рабочими листами

Отдельные рабочие листы одного документа расположены на экране

друг под другом. С помощью именного указателя, расположенного в ниж-

ней части экрана, можно переходить с одного листа на другой. На именном

указателе находятся ярлычки (корешки) рабочих листов, расположенные в

порядке возрастания номеров:

Лист1, Лист2 и т.д.

Рис. 88. Ярлычки листов и кнопки их прокрутки

Щелчок мышью, например, по второму корешку вызывает появление

рабочего листа

Лист2. Ярлычок активного рабочего листа выделяется на

именном указателе белым цветом. Сначала на указателе видны ярлычки

первых трех рабочих листов. Слева от именного указателя расположены

пиктограммы стрелок, с помощью которых можно изменять содержимое

указателя, чтобы получить доступ к следующим листам документа.

Кнопки прокрутки позволяют осуществлять переход на первый, пре-

дыдущий, следующий или последний лист. Перемещени

е по листам можно

также осуществлять путем перехода по ярлычкам листов.

212

Добавление рабочих листов

Команды добавления расположены в меню Вставка. Пользователь

может добавить в документ элементы следующих типов:

 рабочие листы для создания таблиц;

 диаграммы (в качестве элемента таблицы или на отдельном

листе);

 рабочий лист для записи макрокоманды в виде программного

модуля (с использованием макросов);

 рабочий лист для создания диалогового окна.

Новый лист всегда вставляет

ся перед активным рабочим листом. Ес-

ли лист предназначен для создания таблицы, то, независимо от занимаемой

позиции, он будет иметь название

Лист4 с последующим увеличением но-

мера при добавлении новых таблиц.

Перемещение рабочих листов

Установите указатель мыши на корешке рабочего листа, который

нужно переместить, и щелчком правой кнопки мыши откройте контекст-

ное меню. С помощью команды Переместить/ск

опировать откройте

диалоговое окно с тем же названием и укажите в нем новую позицию пе-

реставляемого листа. Закройте окно Переместить/скопирова

ть щелч-

ком по кнопке OK, и рабочий лист зай

мет новую позицию. Если включить

опциональную кнопку Создавать копи

ю, то данный рабочий лист оста-

нется на прежней позиции, а новую позицию займет его копия. Название

копии листа будет образовано путем добавления порядкового номера к

имени копируемого листа, например

Лист1(2).

Можно переставить сразу несколько листов. Для этого промаркируй-

те эти листы, щелкнув по их именам в именном указателе при нажатой

клавише Shift. Затем выполните команду Переместить/скопирова

ть,

которая в этом случае будет применяться сразу ко всем маркированным

рабочим листам.

Переместить рабочий лист (листы) можно также перетаскиванием их

мышью в нужное место.

Переименование рабочих листов

Установите указатель мыши на корешке рабочего листа, который

нужно переименовать, и щелкните правой кнопкой мыши. Откроется кон-

текстное меню, в котором с помощью команды Переименовать можно

перейти непосредственно на имя рабочего листа и изменить его. Туда

можно попасть, используя двойной щелчок левой кнопки мыши по назва-

нию рабочего листа. В этом поле укажите новое имя ли

ста, которое долж-

но содержать не более 31 символа, включая пробелы. После ввода имени

нажмите клавишу мыши, установив ее указатель в любом другом месте, и

213

на именном указателе появится новое имя рабочего листа. При задании

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

квадратные скобки «[ ]»; двоеточие «:»; косая черта «/»; обратная косая

черта «\».

Коррекция высоты строк и ширины столбцов

Прежде всего, корректируемая строка или столбец таблицы должны

быть выделены. Для этого необходимо щелкнуть левой кнопкой мыши по

номеру (координате) строки или столбца. В меню Формат

находятся под-

меню Строка

и Столбец. При выборе одного из этих подменю открыва-

ется меню следующего уровня. В подменю Столбец

вызовите команду

Ширина

, тогда откроется диалоговое окно, в котором пользователь может

указать ширину столбца. Для стандартного шрифта в качестве единицы

измерения можно использовать один символ. Например, если в столбце

нужно записать стандартным шрифтом 10 символов, то в поле ввода

Ши-

рина столбца

следует указать число 10. Команда Автоподбор ширины

из меню Столбец автоматически корректирует ширину столбца в соот-

ветствии с его содержимым.

Коррекцию можно выполнить и с помощью манипулятора мыши.

Для этого указатель мыши нужно установить на границу между номерами

строк или названиями столбцов. При этом указатель мыши приобретает

вид двунаправленной стрелки  . Если нажать левую кнопку мыши и, не

отпуская ее, не

много сдвинуть указатель мыши, то можно увидеть штри-

ховую линию, которая показывает смещение границы строки. Переместите

эту линию в нужную позицию и отпустите кнопку мыши, тогда в таблице

появится новая граница строки. Если дважды щелкнуть мышью по номеру

строки (адресу столбца), то высота (ширина) этой строки (столбца) будет

автоматически скорректирована по ее содержимому.

10.5. Ввод данных в таблицу

Любая ячейка таблицы может быть заполнена данными. Для ввода

данных в ячейку надо щелкнуть левой кнопкой мыши по той ячейке, в ко-

торой предполагается разместить информацию (ячейка выделяется жир-

ным контуром) и начать ввод. По окончании ввода по умолчанию тексто-

вые данные выравниваются по левому краю ячейки, числовые – по право-

му.

Виды указателей и соответствующих им режимов работы мыши в

рабочей области MS Excel

- основной режим – режим выбора (выделения) ячеек. Данный режим

работает, когда указатель мыши наведен на область любой ячейки.

Щелчок левой кнопки мыши в этом режиме позволяет выделить кур-

сорной рамкой ячейку, находящуюся под указателем мыши. Перетас-

214

кивание – перемещение мыши с нажатой левой кнопкой – позволяет в

этом режиме выделить несколько ячеек (Рис. 89. блок яче

ек с B2 по

С5).

Щелчок правой кнопки мыши в основном и некоторых других режи-

мах позволяют вызвать контекстное меню к объекту (ячейке или выделен-

ному блоку ячеек, столбцам и строкам), находящемуся под указателем

мыши.

, - режим выделения соответственно строки (строк) или столбца

(столбцов). Включается, когда указатель мыши наведен на заголовок

строки или столбца. Щелчок левой кнопки мыши в этом режиме по-

зволяет выделить строку или столбец, заголовок которого находится

под указателем мыши (Рис. 89. столб

ец Е). Перетаскивание указателя

с нажатой левой кнопкой в данном режиме позволяет выделить не-

сколько строк или столбцов (Рис. 89.строки 7 и 8);

Рис. 89. Условный пример: режимы выделения ячеек, строк и столбцов

, - режим изменения соответственно высоты строки (строк) или ши-

рины столбца (столбцов). Включается, когда указатель мыши наведен

на границу между заголовками строк или столбцов. Двойной щелчок

левой кнопки мыши в этом режиме позволяет выполнить автоподбор

высоты или ширины по максимальному размеру значений в ячейках

строки или столбца. Перемещение мыши с нажатой левой кнопкой в

данном режиме по

зволяет пользователю «вручную» изменить высоту

или ширину (Рис. 90. строка 5 и столбец D);



- режим заполнения ячеек, доступен пользователю, когда указатель мы-

ши наведен на маркер правого нижнего угла выделенной ячейки (яче-

ек); Перемещение мыши с нажатой левой кнопкой в данном режиме

позволяет пользователю, заполнить соседние по вертикали или гори-

зонтали ячейки на основании значения или формулы выделенной

ячейки (ячеек) (Рис. 90. с пом

ощью заполнения: на основании значе-

ния ячейки А1 «Январь» в ячейках А2 и А3 будут значения «Февраль»

и «Март»; на основании прогрессии, заданной в ячейках C1 и D1 – «1»

и «3» в ячейках E1 и F1 будут значения «5» и «7»; при перетаскивании

под указателем появляется примечание со значением, которое появит-

ся в текущей ячейке – «Март» в A3 и 7 в F1). Двойной щелчок левой

215

кнопки мыши в этом режиме позволяет на основании значения или

формулы выделенной ячейки (ячеек) выполнить автозаполнение не-

прерывного блока нижестоящих ячеек (Рис. 90. ячей

ки с E3 по E7) Ав-

тозаполнение производится до последней ячейки непрерывнозапол-

ненного заполоненного блока, расположенного в соседнем столбце (с

D3 по D7), если слева или справа ячейки пустые, заполнение возмож-

но только перетаскиванием). С помощью данного режима очень удоб-

но копировать формулы или значения и заполнять ячейки с заданной в

двух или нескольких ячейках прогрессией;

- режим перемещения ячеек, доступен, когда указатель мыши наведен

на рамку выделенной ячейки (ячеек). Перетаскивание мыши с нажатой

левой кнопкой в данном режиме приводит к перемещению выделен-

ной ячейки (ячеек) в место, где будет отпущена левая кнопка;

Рис. 90. Условный пример: режимы изменения размеров строки и столбца,

заполнения и перемещения ячеек

Построение таблиц

Все данные таблицы записываются в так называемые ячейки, кото-

рые находятся на пересечении строк и столбцов таблицы. «По умолчанию»

содержимое ячейки представляется программой Excel в стандартном фор-

мате, который устанавливается при запуске программы. Например, для чи-

сел и текстов задается определенный вид и размер шрифта.

В программе Excel имеются контекстные меню, которые вызываются

правой кнопкой мыши, когда промаркирована некоторая область таблицы.

Эти мен

ю содержат много команд обработки и форматирования таблиц.

Команды форматирования можно также вызвать на панели форматирова-

ния (вторая строка пиктографического меню), щелкнув мышью по соот-

ветствующей пиктограмме.

Ввод формул (Табличные вычисления)

Возможность использования формул и функций является одним из

важнейших свойств программы обработки электронных таблиц. Это, в ча-

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

таблице.

216

Текст формулы, которая вводится в ячейку таблицы, должен начи-

наться со знака равенства (=), чтобы п

рограмма Excel могла отличить фор-

мулу от текста. Знак равенства вводится с клавиатуры или нажатием на

кнопку = - Редак

тирование формулы в строке формул (Рис. 93). По-

сле знака равенства в ячейку записывается математическое выражение, со-

держащее аргументы, арифметические операции и функции.

В качества аргументов в формуле обычно используются числа и ад-

реса ячеек. Для обозначения арифметических операций могут использо-

ваться следующие символы: + (сложен

ие); - (вычитание);  (умножение); /

(деление); ^ (возвед

ение в степень).

Предположим, что в ячейке

А1 таблицы находится число 100, а в

ячейке

В1 - число 20. Чтобы разделить первое число на второе и результат

поместить в ячейку

С1, в ячейку С1 следует ввести формулу =А1/В1 и на-

жать на клавиатуре Enter или в строке формул кнопку 

(см. Рис. 87).

Адреса ячеек А1 и В1 вводятся латинским буквами.

Ввод адресов ячеек можно существенно упростить, используя мани-

пулятор мыши. После ввода знака = следует просто щелкнуть мышью по

первой ячейке, зат

ем нажать / и щелкн

уть по второй ячейке, затем нажать

Enter.

Формула может содержать ссылки на ячейки, которые расположены

на другом рабочем листе или даже в таблице другого файла. Однажд

ы вве-

денная формула может быть в любое время модифицирована. Встроенный

Менеджер формул помогает пользователю найти ошибку или неправиль-

ную ссылку в большой таблице.

Кроме этого, программа Excel позволяет работать со сложными

формулами, содержащими несколько арифметических действий и функ-

ций.

Редактирование формул

Чтобы начать редактировать содержимое ячейки, нужно сначала вы-

делить эту ячейку. Далее включить режим редактирования, нажав клавишу

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

можно нажать кнопку = в

строке формул (см. Рис. 87). В режиме редак-

тирования активизируется строка формул, в которой виден текст формулы,

а не результат ее вычисления.

Чтобы изменить формулу, нужно щелкнуть мышью в той позиции

строки, где необходимо внести изменения. Изменения вносятся так же, как

производится ввод формул.

Где был выполнен щелчок, появится мерцающий текстовый курсор.

С этой позиции можно вводить новые символы с клавиатуры. Щелчок по

кнопке  (Esc) слева от формулы отменяет вс

е произведенные изменения,

так что содержимое ячейки остается без изменения. Щелчок по кнопке 

217

(Enter) слева от формулы подтверждает изменение, и в ячейке появляется

новое значение.

Ссылки на ячейку или на группу ячеек

В Excel каждая ячейка имеет свой адрес (координаты). Ссылка на

ячейку или группу ячеек

– способ указания адреса (координат) конкрет-

ной ячейки или нескольких ячеек. С помощью ссылок можно использовать

в формуле данные, находящиеся в различных местах листа, а также ис-

пользовать значение одной и той же ячейки в нескольких формулах. Кроме

того, можно ссылаться на ячейки, находящиеся на других листах книги

или в другой книге, или на данные другого приложения. Ссылки на яче

йки

других книг называются внешними ссылками. Ссылки на данные других

приложений называются удаленными ссылками.

«По умолчанию» в Excel используются ссылки типа

A1, в которых

столбцы обозначаются буквами от A до IV (256 столбцов максимально), а

строки числами — от 1 до 65536. Альтернативный способ, когда названия

столбцов указываются арабскими цифрами и в адресе сначала указывается

номер строки, а потом столбца – тип

R1C1 (изменить тип ссылок можно в

меню

Сервис, Параметры, Общие, Стиль ссылок R1C1). Чтобы указать

ссылку на ячейку, введите адрес (координаты) ячейки, то есть букву заго-

ловка столбца, а затем номер строки. Например,

D50 является ссылкой на

ячейку, расположенную в пересечении столбца

D с 50-й строкой. Чтобы

сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапа-

зона, поставьте двоеточие (:), а зат

ем — ссылку на правый нижний угол

диапазона.

Ссылки можно делать не только на ячейки текущего листа, но и на

ячейки других листов и файлов (книг). При этом в ссылке должно быть на-

звание листа, на ячейки которого делается ссылка, отделенное от адреса

ячеек восклицательным знаком, например,

Лист1!A10. А при ссылке на

другую книгу добавляется название книги в квадратных скобках

[Кни-

га2]Лист5!$A$5

В следующей таблице представлены варианты возможных ссылок.

Чтобы сослаться на Введите

ячейку столбца A и 10-й строки A10

диапазон ячеек столбца A с 10-й строки по 20-ю A10:A20

диапазон ячеек в 15-й строке со столбца B по столбец E B15:E15

все ячейки в 5-й строке 5:5

все ячейки между 5-й и 10-й строками включительно 5:10

все ячейки в столбце H H:H

все ячейки между столбцами H и J включительно H:J

Относительные и абсолютные ссылки

При перемещении формулы ссылки, расположенные внутри форму-

лы, не изменяются. При копировании формулы абсолютные ссылки не из-

218

меняются, а относительные ссылки изменяются.

При создании формулы ссылки обычно изменяются относительно

расположения ячейки, содержащей формулу. В следующем примере ячей-

ка

С5 содержит формулу =A5*В5. Ссылка на ячейку А5 (как и В5) назы-

вается относительной (т.е. адрес относительно названия столбца и номера

строки).

A B С

5 100 2 =А5*В5

6 200 5

При копировании формулы, содержащей относительные ссылки, в

другие ячейки ссылки будут меняться относительно адреса новой ячейки.

В следующем примере формула из ячейки

С5 копируется в ячейку С6. В

ячейке

С6 будет содержаться формула =A6*В6, которая при копировании

на 1 строку ниже изменила номер строки на 1. А при копировании форму-

лы из ячейки

С5 в ячейку D6, будет содержаться формула =B6*C6, в кото-

рой изменился адрес ячеек относительно перемещения на 1 строку ниже и

1 столбец вправо.

A B С D

5 100 2 =А5*В5

6 200 5 =A6*В6 =B6*C6

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

формулы в другую ячейку, воспользуйтесь абсолютными ссылками. На-

пример, если необходимо умножить все значения столбца

А на содержи-

мое ячейки

B5 (=A5*В5, =A6*В5, =A7*В5 и т.д.), то, чтобы не вводить

формулу в каждую ячейку, а копировать ее, необходима абсолютная ссыл-

ка. Для создания абсолютной ссылки на ячейку

В5, поставьте знак доллара

(

$) перед той частью адреса, которая не должна изменяться (В$5). Чтобы

создать абсолютную ссылку на ячейку

В5, поместите знак доллара так:

=A5*$В$5

При копировании такой формулы, как по строкам, так и по столбцам,

ссылка на ячейку

В5 не будет меняться. И, например, после копирования

ее в ячейку

D45 будет формула =В45*$В$5.

Можно фиксировать и диапазоны ячеек:

$В$2:$В$20.

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

дактирования формул можно использовать клавишу F4. Если поставить

курсор у адреса ячейки и нажать 1 раз F4 , будет зафиксирована вся ячейка

(

$C$1 – абсолютная ссылка), 2 раза – только номер строки (C$1 – абсо-

лютная ссылка по строке

), 3 раза – только название столбца ($C1 – абсо-

лютная ссылка по столбцу

) и 4 раза – снимается фиксация (C1 – относи-

тельная ссылка

219

10.6. Корректировка табличных документов

Выделение ячеек

Отдельные ячейки таблицы выделяются (маркируются) автоматиче-

ски с помощью указателя ячеек. Чтобы перевести указатель в заданную

ячейку, нужно щелкнуть по ней левой кнопкой мыши или использовать

клавиши управления курсором. Для выделения нескольких ячеек нужно

щелкнуть в начале выделяемой области (левый верхний угол) и, удерживая

кнопку мыши нажатой, перемещать манипулятор в конец област

и (правый

нижний угол). Чтобы отменить маркировку области, можно просто щелк-

нуть по невыделенной ячейке. Для выделения нескольких ячеек с помо-

щью клавиатуры необходимо установить указатель ячеек в начальную

ячейку области, а затем, удерживая клавишу Shift нажатой, распростра-

нить выделение на всю область с помощью кла

виш управления курсором.

Одна строка или столбец таблицы выделяются щелчком по номеру

(адресу), который расположен в заголовке строки или столбца. Для выде-

ления нескольких строк или столбцов нужно щелкнуть по номеру первой

строки (столбца), а затем, удерживая кнопку мыши нажатой, переместить

манипулятор в нужную позицию.

Выделить неско

лько несмежных ячеек или диапазонов можно мыш-

кой с нажатой клавишей Ctrl. При нажатой клавише Shift можно выделить

диапазон ячеек от первой до последней ячейки диапазона, указанных мыш-

кой.

Копирование данных

Прежде всего нужно выделить группу ячеек, содержимое которых

должно быть скопировано, а затем вызвать команду Копировать из меню

Правка

. После этого выделенная область будет заключена в рамку. Те-

перь следует установить указатель мыши в позицию, куда должны быть

скопированы данные, и нажать клавишу ввода Enter. Содержимое выде-

ленной области появится в новом месте. Если область, в которую нужно

поместить копию, тоже выд

елена, то размеры обеих групп должны быть

одинаковыми, в противном случае будет выдано сообщение об ошибке.

В контекстном меню правой кнопки мыши также есть команда копи-

рования. Для копирования можно также использовать комбинацию клавиш

Ctrl

+Insert или соответствующую пиктограмму на панели инструментов.

Если нужно удалить содержимое группы ячеек и поместить его на

новое место в таблице, то следует использовать команду Вырезать

из

меню Правка.

220