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

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

Удаление данных

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

должно быть удалено. Затем можно воспользоваться одним из способов

удаления данных. Для этого в меню Правка

есть подменю Очистить, ко-

торое содержит следующие четыре команды:

Все

- удаляет содержимое и отменяет формат ячейки;

Форматы

- отменяет только формат ячейки;

Содержимое

- удаляет только содержимое ячейки;

Примечания

- удаляет только комментарии к ячейке.

Команда удаления Очистить содержимое

есть также в контекст-

ном меню. С помощью этой команды можно удалить только содержимое

ячеек. Тот же результат достигается просто нажатием клавиши Del. Если

пользоват

ель случайно удалил нужную информацию, то

командой Отме-

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

Форматирование данных

Рис. 91. Формат ячеек: вкладка Число

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

формат данных. После этого можно либо открыть правой кнопкой мыши

контекстное меню и вызвать в нем команду Формат ячеек

, либо вызвать

команду Формат Ячейк

и из меню Формат. В любом случае на экране

появится диалоговое окно

Формат ячеек. Используя его, можно устано-

вить форматы представления данных в ячейках: числовые форматы (Рис.

221

91), выравнивание (Рис. 92), границы и вид ячеек, а также определить сте-

пень защиты данных.

Рис. 92. Формат ячеек: вкладка Выравнивание

Отмена операций

Действие, которое выполнено последним, можно отменить командой

Отменить дейс

твие из меню Правка или комбинацией клавиш Ctrl+Z.

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

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

Команда отмены после выполнения превращается в команду под-

тверждения изменения Вернуть

действие. Выполнив эту команду, мож-

но снова ввести в таблицу измененные данные и т.д. Это можно сделать

также с помощью клавиатурной комбинации Ctrl

+Z или щелчка по соот-

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

Предыдущее действие можно повторить - для этого служит команда

Повторить дейст

вие из меню Правка. Для повторения последнего

действия можно также нажать клавишу

F4 или щелкнуть по соответст-

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

10.7. Использование встроенных функций Microsoft Excel

Мастер функций

Поскольку многие сложные и несложные вычисления встречаются

очень часто, то программа Excel предлагает более 200 заранее запрограм-

мированных функций.

Функциями в Excel называют заранее определен-

222

ную формулу (производителем программы или созданную пользователем),

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

ввода ее аргументов.

Для быстрого поиска необходимой функции они сгруппированы по

категориям:

10 недавно использовавшихся – список 10 последних функций, ко-

торые использовались пользователем

;

Полный алфавитный перечень

– весь список функций, упорядо-

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

;

Финансовые

– алгоритмы расчета некоторых финансовых показате-

лей (амортизации, будущей стоимости и др.);

Дата и время – алгоритмы вычислений и различных преобразований

данных в виде дат и времени;

Математические – алгоритмы арифметических и тригонометриче-

ских вычислений;

Статистические – алгоритмы расчета показателей относящихся к

математической статистике;

Ссылки и массивы – алгоритмы, позволяющие определять позицию

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

Работа с базой данных – алгоритмы основных операций, исполь-

зуемых для анализа данных из списков или баз данных;

Текстовые – алгоритмы, производящие различные операции с тек-

стовыми значениями (например, преобразование чисел в текст и наобо-

рот);

Логические – алгоритмы, производящие проверку условий и опре-

деляющие результат как ИСТИНА или ЛОЖЬ;

Проверка свойств – алгоритмы, производящие проверку ячеек на

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

Встроенный Мастер функций помогает правильно применять функ-

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

формулы с именем функции и одним или несколькими аргументами, ука-

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

значения или адреса ячее

к.

Например:

=СУММ(А5:А9)

– сумма ячеек А5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) – среднее значение ячеек G4, G5, G6.

Функции могут быть вложенными одна в другой, например:

=ОКРУГЛ(СРЗНАЧ(H4:H8);2).

Функция расч

ета среднего значения (СРЗНАЧ) являет

ся вложенной

в функцию округления (ОКРУГЛ). Можно сделат

ь в формуле не более 7

вложенностей. Расчет по формулам со множеством вложенных функций

начинается с последней, по уровню вложенности, функции (т.е., согласно

223

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

округление вычисленного среднего значения.

Рис. 93. Строка формул и диалоговое окно функции Просмотр

Для введения функции в ячейку необходимо выделить ту ячейку, в

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

ветствующей кнопке

- Мастер функций откройте диалоговое окно.

В левом поле этого окна перечислены категории функций, а в правом

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

Для того чтобы увидеть все функции, следует щелкнуть мышью по опции

Все

в поле категорий.

После щелчка мышью по названию искомой функции ее название

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

Здесь же указываются параметры (аргументы) функции и их количество.

После нажатия кнопки ОК появится диалоговое окно выбранной

функции (например, функция Просмотр) (см. Рис. 93).

Необходи

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

этого окна ввести аргументы функции (числовые значения или ссылки на

ячейки). Чтобы указать аргументы, можно щелкнуть кнопку

, находя-

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

функции; для выхода из этого режима следует щелкнуть кнопку , кото-

рая находится справа от поля, или клавишу Esc. Для завершения ввода

формулы нажмите ОК

Названия Обязательных параметров выделены жирным шрифтом,

эти поля обязательно подлежат заполнению. Если не введены обязатель-

224

ные параметры при нажатии кнопки ОК будет выдано сообщение об

ошибке.

Необязательные параметры заполняются по необходимости.

Чтобы в качестве аргумента вложить функцию, нужно поставить

курсор в поле параметра и выбрать из

Списка функций в строке формул

необходимую. Желательно сначала ввести все аргументы первой функции,

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

После этого в указанной ячейке таблицы появится результат вычис-

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

деть текст функции вместе с соответствующими параметрами.

Редактирование функций

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

помощью мастера функций и «вручную».

Выбрав ячейку, где находится функция, щелчком по пиктограмме

Мастер фу

нкций открывается соответствующее диалоговое окно. В этом

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

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

ходятся аргументы, следует либо непосредственно внести новые адреса,

либо выделить соответствующую группу ячеек. После окончания редакти-

рования следует щелкнуть по кнопке ОК

. Если функций в формуле не-

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

названию в строке формулы.

Редактирование аргументов функций можно выполнить непосредст-

венно в строке формул, не обращаясь к

Мастеру функций. Для этого

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

исправить формулу, и внести изменения «вручную». Где был выполнен

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

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

Щелчок по кнопке Отмена  слев

а от формулы отменяет все про-

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

ния. Щелчок по кнопке 

(Enter) слева от формулы подтверждает изме-

нение, и в ячейке появляется новое значение.

Вычисление суммы

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

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

В наборной строке появится знак равенства и слово

SUM, за кото-

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

грамма предлагает после анализа близлежащих ячеек в качестве области

суммирования. Если программа Excel «угадала» правильно, нажмите

Enter.

В противном случ

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

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

225

вишу Ctrl. Эту клавишу нужно удерживать в нажатом состоянии до тех

пор, пока все группы не будут выделены.

Адреса групп можно ввести с клавиатуры. В этом случае нужно

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

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

зависимости от установки опций

Язык и стандарты в среде Windows).

Кроме этого, в формулу можно ввести и числовые слагаемые. После этого

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

Текстовый режим индикации функций

В обычном режиме программа Excel вводит в таблицу результаты

вычислений по формулам. Можно изменить этот режим и вывести тексто-

вый режим индикации (отображения) формул, нажав комбинацию клавиш

Ctrl+~

или Сервис Параметры, на вкладке включить Формулы.

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

а тексты самих формул и функций. В этом режиме увеличивается ширина

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

ошибку.

Сообщения об ошибках

Если формула в ячейке не может быть правильно вычислена, Excel

выводит в ячейку сообщение об ошибке. Если формула содержит ссылку

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

также будет выводиться сообщение об ошибке. Значение сообщений об

ошибках следующее:

#### – ширина ячейки не позволяет отобрази

ть число в заданном

формате;

#ИМ

Я? – Excel не смог распознать имя, использованное в формуле;

#ДЕЛ/0! – в формуле д

елается попытка деления на нуль;

#ЧИСЛО! – нарушены правила за

дания операторов, принятые в ма-

тематике;

#Н/Д – такое сообщени

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

задана ссылка на пустую ячейку;

#ПУС

ТО! – неверно указано пересечение двух областей, которые не

имеют общих ячеек;

#ССЫЛКА! – в формуле за

дана ссылка на несуществующую ячейку;

#ЗНАЧ! – использован недопустимый тип аргумент

а.

10.8. Работа с массивами данных

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

либо несколько значений, либо одно значение. Формулы массива обычно

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

226

аргументами массива. При этом все аргументы массива должны быть пря-

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

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

ек.

Массивы формул удобно использовать для введения однотипных

формул и обработки данных в виде таблиц. Например, для вычисления мо-

дуля от чисел, размещенных в ячейках A1, B1, C1, D1, E1, вместо ввод

формул в каждую ячейку можно ввести одну формулу–массив для всех

ячеек. Excel добавляет вокруг массива формул фигурные скобки { }, по ко-

торым его можно отличить (Рис. 94).

Для создания массива формул необходимо:

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

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

ав в качестве аргумен-

тов группу ячеек-аргументов;

- в последнем окне вместо кнопки ОК нажать комбинацию клавиш

Ctrl+Shift+Enter.

Для редактирования массива формул необходимо:

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

- щелкнуть мышью внутри строки редактирования и отредактиро-

вать формулу;

- нажать комбинацию клавиш Ctrl+Shift+Enter.

Рис. 94. Массив формул

Другой пример формулы массива для одной ячейки:

={СУММ(ЕСЛИ(А1:Е1=-12; А2:Е2;0))},

Эта формула просматривает все ячейки массива А1:Е1 и, если они

равны –12, суммирует соответствующие ячейки строки 2.

Группирование элементов таблицы

Excel позволяет группировать элементы в сводной таблице для того,

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

сяцы в кварталы для построения диаграммы или для печати.

Для группирования элементов таблицы необходимо:

- выделить строки или столбцы, которые будут подчинены итоговой

строке или столбцу (это будут строки или столбцы, которые необходимо

сгруппировать);

- в меню Данные выбрать пункт Группа и ст

руктура;

227

- выбрать пункт Группировать.

Так можно создать все необходимые уровни структуры. Например,

таблица (Рис. 95) содержит 3 уровня детализации. Трети

й уровень скрыва-

ет строки 2-4 и 6-8, второй – скрывает строки 2-9. Таким образом, первый

уровень детализации содержит только строки 1 и 10. Для перехода между

уровнями используются кнопки с соответствующими цифрами в верхнем

левом углу таблицы.

Рис. 95. Группы и структура

Чтобы снять группирование, следует выделить необходимые элемен-

ты, выбрать пункт Группа и ст

руктура меню Данные, затем пункт Раз-

группировать

Сводные таблицы

Сводная таблица – это интерактивный перекрестный отчет Excel, со-

держащий итоговые данные и выполняющий анализ таких данных, как за-

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

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

ких фактов по каждому числу из длинного списка обобщаемых чисел.

Сводная таблица представляет собой интерактивную таблицу, с по-

мощь

ю которой можно быстро объединять и сравнивать большие объемы

данных. Можно простым перетаскиванием мышью менять местами строки

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

показывать подробные данные по нужным областям.

В приведенном примере (Рис. 96) на основании базы данных о про-

дажах спо

ртивных товаров по кварталам с помощью сводной таблицы бы-

ли быстро определены суммы объемов продаж по видам спортивных това-

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

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

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

ние.

Создать сводную таблицу можно с по

мощью мастера сводных таб-

лиц и диаграмм (команда Сводная т

аблица меню Данные). Диалоговое

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

228

внешней базе данных. Затем создается макет сводной таблицы и предлага-

ется список доступных полей. При перетаскивании полей из окна списка в

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

гов по полям и построение таблицы (отчета).

Рис. 96. Создание сводных таблиц

10.9. Работа с базами данных

С помощью Excel можно создавать и обрабатывать базы данных. Ба-

за данных

в Excel – таблица, состоящая из однотипных записей (строк).

Столбцы таблицы являются

полями записи в базе данных. Под имена по-

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

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

телефонов и адреса абонентов.

Для работы с базой данных необходимо сначала создать соответст-

вующую таблицу. Если выделить ячейку в таблице и выбрать одну из ко-

манд обработки баз данных в меню Данные

, Excel автоматически опреде-

ляет и обрабатывает всю таблицу. Данные, расположенные в столбцах и

строках рабочего листа, обрабатываются ка

к набор полей, которые обра-

зуют записи (Рис. 9

7).

Рис. 97. База данных

229

Сортировка данных

Сортировка позволяет

переупорядочить строки в таблице по

любому полю. Например, чтобы

отсортировать данные по цене

изделия. Для сортировки данных

следует выделить одну ячейку

таблицы и вызвать команду

Сортировка меню Данные

В поле списка Сортировать

по выбирается по

ле, по которому бу-

дут отсортированы данные, и тип

сортировки:

по воз

растанию – цифры

сортируются по возрастанию, текст –

в алфавитном порядке, логические

выражения – ЛОЖЬ предшествует ИСТИНА

Рис. 98. Сортировка

по уб

ыванию – сортировка в обратном порядке.

В поле списка Затем по у

казывается поле, по которому будут от-

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

поле. Во втором поле Затем по ук

азывается поле, по которому будут от-

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

чевых полях.

Для сортировки данных также используются кнопки

Перед

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

сортировать записи.

При сортировке по одному столбцу строки с одинаковыми значе-

ниями в этом столбце сохраняют прежнее упорядочение. Строки с пусты-

ми ячейками в столбце, по которому ведется сортировка, располагаются в

конце сортируемого списка. Excel позволяет также сортировать не всю

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

и столбцы.

Формы данных

При выполнении характерных для баз данных операций, таких как

поиск, сортировка, подведение итогов, Excel автоматически рассматривает

таблицу как базу данных.

При просмотре, изменении, добавлении и удалении записи в базе

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

использовать

формы данных. При обращении к команде Форма меню

Данные Excel счи

тывает данные и создает диалоговое окно формы дан-

ных. В форме данных на экран выводится одна запись. При вводе или из-

230