Учебник. А. Ю. Босова Москва бином. Лаборатория знаний 2016 11 класс Базовый уровень Учебник
 Скачать 4.61 Mb.
|
|
Глава 1. Обработка информации в эт Если содержимым ячеек, которое копируется или перемещается, являются формулы, то можно вставить в выделенные ячейки не сами формулы, а вычисленные по ним значения. Для этого необ ходимо скопировать содержимое исходных ячеек в буфер обмена, выделить позицию вставки и выполнить команду Главная → Вста- вить → Вставить значения. Данные из буфера обмена можно вставить в выделенные ячей ки таким образом, что они не заменят имеющиеся там данные, а, например, суммируются с ними. Эту и ряд других возможностей можно исследовать в окне Специальная вставка (рис. 1.2), вызы ваемом командой Главная → Вставить → Специальная вставка. рис. 1.2. Окно Специальная вставка Если необходимо в несколько расположенных подряд ячеек ввести повторяющиеся данные или изменяющиеся по определён- ным закономерностям последовательности данных, то можно вос- пользоваться функцией автозаполнения. Для этого нужно: 1) внести данные в две первые ячейки; 2) выделить эти ячейки; 3) установить указатель мыши над маркером заполнения — ма- леньким чёрным квадратиком в правом нижнем углу выде- ленной ячейки (вид указателя мыши при наведении его на маркер заполнения меняется на знак +); 15 табличный процессор. Основные сведения §1 4) нажать левую кнопку мыши, протянуть (перетащить) указа- тель по заполняемым ячейкам и отпустить кнопку мыши. Примените описанный выше приём для получения следующих рядов: 1) 1, 1, 1, …; 2) 5, 10, 15, …; 3) 100, 200, 300, …; 4) урок, урок, урок, …; 5) январь, февраль, март, …; 6) товар 1, товар 2, товар 3, … Попробуйте добиться такого же результата с помощью команды Заполнить → Прогрессия… (вкладка Главная, группа редактиро- вание). Любым способом введите следующие ряды: 1) 2, 4, 8, 16, …; 2) 0, 5, 10, 15, … Вспомните, как называются такие последовательности. Если содержимым ячейки является формула, включающая ссылки, то при копировании этой ячейки в формуле может про- исходить автоматическое изменение ссылок. Ссылка, которая изменяется при копировании формулы, называется относительной. Ссылка, которая не изменяется при копировании формулы, назы вается абсолютной. Ссылка, в которой при копировании формулы изменяется только номер строки или только имя столбца, называется смешанной. Большинство ссылок в формулах относительные. При копи- ровании в составе формулы в другую ячейку они автоматически изменяются в соответствии с новым положением скопированной формулы, т. е. они изменяются относительно месторасположения формул. В этом состоит суть принципа относительной адресации. При копировании формулы с относительными ссылками [стол бец] [строка] на n строк ниже (выше) и на m столбцов правее (ле вее) ссылка заменяется на [столбец ± n] [строка ± m]. При копировании формулы в пределах одного столбца (одной стро ки) обозначения столбцов (номера строк) в формулах не изменя ются. 16 Глава 1. Обработка информации в эт Иногда нужно, чтобы при копировании формул адреса ячеек не менялись. В этом случае используют абсолютные ссылки. Для создания абсолютной ссылки служит знак $. С помощью этого знака можно зафиксировать весь адрес ($А$1), только стол- бец ($А1) или только строку (А$1). В двух последних случаях говорят о смешанных ссылках. Чтобы быстро преобразовать ссылку из относительной в абсолют ную и наоборот, можно выделить её в строке ввода и нажать кла вишу F4 (Microsoft Excel) или Shift+F4 (OpenOffice Calc). Вспомните, как можно быстро получить смешанные ссылки. Если в формуле для ссылки на ячейку использовать её имя, то при копировании формулы эта ссылка изменяться не будет. Иначе говоря, имя ячейки в формуле является абсолютной ссыл- кой. При перемещении формулы имеющиеся в ней ссылки не из- меняются. Пример 1. В ячейке B1 записана формула =2*$A1. Выясним, какой вид приобретёт формула, после того как со- держимое ячейки B1 скопируют в ячейку C2. В формуле используется смешанная ссылка: при копировании формулы имя столбца останется неизменным, а номер строки уве- личится на 1. Таким образом, после копирования в ячейке С2 окажется формула =2*$A2. Пример 2. Дан фрагмент электронной таблицы: A B C 1 15 13 2 14 12 =($A2+B2)/2 Выясним, чему станет равным значение ячейки С1, если в неё скопировать формулу из ячейки С2. Так как копирование формулы происходит внутри одного столбца, имена столбцов в формуле не изменятся, а номер стро- ки в ссылках уменьшится на единицу. Формула примет вид: =($A1+B1)/2. В ячейке С1 отобразится число 14. 17 табличный процессор. Основные сведения §1 Пример 3. Дан фрагмент электронной таблицы: A B C D 1 1 1 1 1 2 2 2 2 =B$3+$C2 3 3 3 3 3 4 4 4 4 4 Выясним, чему будет равна сумма значений диапазона ячеек E1:E4 после копирования в него формулы из ячейки D2. Формулы копируются в ячейки соседнего столбца. Поэтому буквенное обозначение столбца в относительной ссылке изменит- ся на следующее по алфавиту. Следовательно, первое слагаемое в формуле примет вид: С$3 (ссылка на номер строки здесь аб- солютная, она останется неизменной). Во втором слагаемом не- изменным является обозначение столбца. А номер строки при копировании формулы в ячейки E1, E2, E3 и E4 соответственно: уменьшится на единицу, останется неизменным, увеличится на единицу, увеличится на 2. A B C D E 1 1 1 1 1 =C$3+$C1 2 2 2 2 =B$3+$C2 =C$3+$C2 3 3 3 3 3 =C$3+$C3 4 4 4 4 4 =C$3+$C4 После вычисления значений по формулам ячеек E1, E2, E3 и E4 (4, 5, 6 и 7) находим сумму значений диапазона ячеек E1:E4, равную 22. Пример 4. Требуется с помощью формул в ЭТ построить таб- лицу двузначных чисел, фрагмент которой представлен на ри- сунке: A B C D E F G H I J K 1 0 1 2 3 4 5 6 7 8 9 2 1 10 11 12 13 14 15 16 17 18 19 3 2 20 21 22 23 24 25 26 27 28 29 4 3 30 31 32 33 34 35 36 37 38 39 5 4 40 41 42 43 44 45 46 47 48 49 18 Глава 1. Обработка информации в эт Возможный алгоритм действий может быть таким: 1) в диапазон B1:K1 ввести числа от 0 до 9 (можно воспользо- ваться маркером заполнения); 2) в диапазон A2:A10 ввести числа от 1 до 9; 3) в ячейку B2 записать формулу двузначного числа: =A2*10+B1 (A2 — число десятков; B1 — число единиц); 4) внести изменения в формулу с учётом следующего: • при копировании формулы вниз должен изменяться номер строки, «отвечающей» за количество десятков, а имя столб- ца A, «отвечающего» за разряд десятков, должно оставаться неизменным ($A2); • при копировании формулы вправо должно изменяться имя столбца, «отвечающего» за количество единиц, а номер стро- ки 1, «отвечающей» за разряд единиц, должен оставаться неизменным (B$1); 5) скопировать отредактированную формулу (=$A2*10+B$1) во все ячейки диапазона B2:K10. СамОе ГлаВнОе Прикладные программы, предназначенные для работы с дан- ными, представленными в таблицах, называются табличными про- цессорами (ТП) или просто электронными таблицами (ЭТ). Основными объектами табличного процессора являются рабо- чая книга, лист, электронная таблица, строка, столбец и ячейка. Ячейка— это наименьшая структурная единица электронной таб лицы, которая образуется на пересечении столбца и строки. Адрес ячейки определяется именем столбца и номером строки, на пересечении которых она находится. Содержимым ячейки может быть число, текст или формула. Формула начинается со знака «=» и может содержать скобки, числа, тексты, ссылки на ячейки, знаки операций и функции. В формулах используются не сами исходные данные, а ссыл- ки на ячейки, в которых эти данные находятся. При изменении данных в ячейках происходит автоматический пересчёт значений всех формул, содержащих ссылки на эти ячейки. Различают относительные, абсолютные и смешанные ссылки. Ссылка, которая изменяется при копировании формулы, назы- вается относительной. Ссылка, которая не изменяется при ко- пировании формулы, называется абсолютной. Ссылка, в которой при копировании формулы изменяется только номер строки или только имя столбца, называется смешанной. 19 табличный процессор. Основные сведения §1 Вопросы и задания 1. Что понимают под табличным процессором и электронными таблицами? 2. Сравните интерфейс известных вам текстового и табличного процессоров. Что у них общего? Чем они различаются? 3. Что такое адрес (имя) ячейки ЭТ? Как задаётся адрес ячей- ки, адрес диапазона ячеек? 4. Выясните, куда в табличном процессоре перемещается таб- личный курсор при нажатии клавиш Home, End, PageUp, PageDown. Куда перемещается табличный курсор при на- жатии комбинации клавиш: Ctrl + →→, Ctrl + →↓, Ctrl + ←, Ctrl + →↑, Ctrl + Home, Ctrl + End? Проведите аналогию с пе- ремещениями текстового курсора в текстовом процессоре. 5. Какие типы данных могут быть занесены в ячейку ЭТ? 6. Какие существуют особенности ввода числовых значений в ЭТ? 7. Вспомните основные правила ввода формул в ЭТ. Где вы уже встречались с аналогичными правилами ввода арифме- тических выражений? 8. В чём суть принципа относительной адресации в ЭТ? Что происходит при копировании формул, содержащих относи- тельные ссылки? 9. В каких случаях в формулах используются абсолютные ссылки? 10. В чём заключается преимущество использования ссылок в формулах? 11. На основании чего можно судить о том, что табличный про- цессор интерпретировал введённые в ячейку данные как текст? Как число? 12. Сравните приёмы копирования и вставки данных в тексто- вом и табличном процессорах. Что у них общего? Чем они различаются? 13. Как осуществляется автозаполнение ячеек? 14. Как ввести следующее четверостишие А. Ерикеева в одну ячейку электронной таблицы? Наступила осень, Пожелтел наш сад. Листья на берёзе Золотом горят. 20 Глава 1. Обработка информации в эт 15. Значение переменной x находится в ячейке А1, значение переменной у — в ячейке А2, значение переменной z — в ячейке А3. Запишите формулы для вычисления в элек- тронных таблицах значений выражений: 1) (x + y + z) : 3; 2) 5x 3 + 4y 2 – 3z. 16. Только путём ввода последовательностей составьте таблицу умножения: A B C D E F G H I 1 1 2 3 4 5 6 7 8 9 2 2 4 6 8 10 12 14 16 18 3 3 6 9 12 15 18 21 24 27 4 4 8 12 16 20 24 28 32 36 5 5 10 15 20 25 30 35 40 45 6 6 12 18 24 30 36 42 48 54 7 7 14 21 28 35 42 49 56 63 8 8 16 24 32 40 48 56 64 72 9 9 18 27 36 45 54 63 72 81 За сколько операций вам удалось это сделать? 17. Дан фрагмент электронной таблицы: А В С 1 40 10 =A1+B$1 2 30 20 Чему будет равно значение ячейки С2, если скопировать в неё формулу из ячейки С1? 18. В ячейке B3 записана формула =C$2+$D3+2. Какой вид приобретёт формула после копирования её в ячейку B2? 19. Измерьте длину, ширину и высоту кухни, прихожей и жи- лых комнат вашей квартиры. Создайте в табличном процес- соре таблицу с результатами измерений. Вычислите площадь пола, площадь стен и объём каждого из помещений, а так- же общую площадь всех помещений. 20. В табличном процессоре создайте таблицу вида: Страна Площадь, кв. км население, млн чел. Плотность Проценты 21 §2 редактирование и форматирование в тП Занесите в таблицу информацию о десяти странах, имеющих самую большую численность населения. Введите в соответст- вующие ячейки формулы для вычисления: 1) общей площади и общего количества населения этих де- сяти стран (предусмотрите соответствующие ячейки под созданной таблицей с данными); 2) плотности населения в каждой из этих стран; 3) процентов, которые составляет население каждой из этих стран по отношению к общему количеству населения в мире. 21. В табличном процессоре вычислите значения функции y = x 2 + x – 12 на промежутке [–5; 5] с шагом 0,5. 22. Подготовьте краткое сообщение о первых электронных таб- лицах. § 2 редактирование и форматирование в табличном процессоре 2.1. редактирование книги и электронной таблицы В процессе работы с табличным процессором в книгу часто приходится добавлять (вставлять) новые листы, удалять, пере- именовывать, перемещать или копировать существующие листы. Самостоятельно найдите не менее двух способов вставки нового листа в книгу и удаления существующего листа из книги. Исследуйте контекстное меню ярлычка листа. Выясните, какие операции с листом можно выполнить с его помощью. Зачастую возникает необходимость внести в уже созданную электронную таблицу строки, столбцы или диапазоны ячеек. Для вставки в таблицу новых столбцов (строк) необходимо выделить столбцы (строки), перед которыми нужно вставить новые, вызвать контекстное меню и выбрать в нём команду Вставить. Если выделить один столбец (строку), то перед ним вставится один новый столбец (строка). Если же выделить несколько строк (столб цов) подряд, то перед ними вставится столько же новых столбцов (строк), сколько было выделено. 22 Глава 1. Обработка информации в эт Существует несколько вариантов вставки ячейки или диа- пазона ячеек (рис. 1.3). рис. 1.3. Вставка пустой ячейки Удаление строк, столбцов, ячейки или диапазона ячеек про- исходит аналогично. Самостоятельно исследуйте возможности вставки (удаления) ячей ки или диапазона ячеек, имеющиеся в окне Добавление ячеек (Удаление ячеек), вызываемом командой Вставить… (Удалить…) контекстного меню. 2.2. форматирование объектов электронной таблицы Основными операциями форматирования являются: форма- тирование данных, форматирование ячеек, изменение ширины столб цов и высоты строк. Форматирование числовых данных. Формат отображения данных позволяет представлять их в наиболее подходящем для пользователя виде. При вводе любых данных используется фор- мат Общий. Тип данных при этом определяется автоматически. Для форматирования содержащихся в ячейке (ячейках) дан- ных: 1) эту ячейку (ячейки) выделяют; 2) правой кнопкой мыши вызывают её контекстное меню; 3) вызывают диалоговое окно Формат ячеек; 4) задают необходимый формат на вкладке Число. 23 §2 редактирование и форматирование в тП При форматировании данных сами данные не изменяются, изменя ется лишь их внешний вид. Реальное значение числовых данных можно увидеть в строке фор мул, сделав соответствующую ячейку текущей. Пример. Введём в диапазон ячеек А1:А9 названия основных форматов данных. В ячейку В1 введём число 12,3 и скопируем его в диапазон В2:В9 (рис. 1.4). Будем поочерёдно выделять ячейки диапазона В2:В9 и приме- нять форматы, указанные в соответствующих ячейках столбца А, сравнивая при этом вид числа в ячейке и в строке формул. Формат Общий является форматом по умолчанию. Он ис- пользуется для представления чисел в большинстве случаев так, как они были введены. Если ширина ячейки недостаточна для отображения введённого с клавиатуры числа, оно автоматически представляется в экспоненциальном виде. Формат Числовой используется для представления числа в виде десятичной дроби с заданным количеством десятичных зна- ков. Если число в ячейке имеет меньше десятичных знаков, чем предусмотрено форматом, то при отображении в ячейке оно будет дополнено нулями справа, а если больше — будет округлено. Формат Денежный используется для установки значений тех же свойств, что и для формата Числовой, с добавлением к числу обозначения денежной единицы, которое выбирается из списка. Формат Дата используется для представления числа в виде даты определённого типа. В Microsoft Excel в формате Дата все даты сохраняются как натуральные числа. Отсчёт начинается с рис. 1.4. Формат числа 24 Глава 1. Обработка информации в эт 01.01.1900, и этой дате соответствует число 1. Каждой следующей дате соответствует следующее натуральное число: 02.01.1900 — 2, 03.01.1900 — 3, ..., 06.06.2006 — 38 874, ..., 01.09.2010 — 40 422. Такое представление дат позволяет выполнять операции над ними. Так, количество дней между двумя датами определя- ется разностью чисел, которые соответствуют этим датам. Напри- мер, разность 01.09.2016 – 01.01.2016 будет вычисляться таким образом: 42 614 – 42 370 = 244. В формате Процентный данные представляются числом, ко- торое является результатом умножения содержимого ячейки на 100, со знаком % в конце. Над данными, представленными в формате Текстовый, можно выполнять операции и как над числами, и как над текстами. По умолчанию числа в формате Текстовый выравниваются в ячейке по левому краю, в других форматах — по правому краю. Форматирование ячеек. При форматировании ячейки (ячеек) электронной таблицы можно устанавливать: • границы ячейки, их цвет, тип линий и др.; • цвет фона ячейки, цвет и стиль узора, способы заливки и др.; • защиту ячейки, режим скрытия формул; • формат числовых данных (числовой формат); • значения свойств символов в ячейке: шрифт, начертание, раз- мер, подчеркивание, горизонтальное и вертикальное выравни- вание, ориентацию и др. Для этого можно использовать элементы управления групп Шрифт, Выравнивание, Число, Стили, Ячейки вкладки Главная на ленте или элементы управления, расположенные на вкладках окна Формат ячеек. Окно Формат ячеек можно вызвать, исполь- зуя кнопки открытия диалоговых окон групп Шрифт, Выравни- вание или Число, а также с помощью контекстного меню ячейки (ячеек). Операции изменения шрифта, цвета, размера и начертания символов в ячейках электронной таблицы аналогичны соответст- вующим операциям форматирования символов в текстовом про- цессоре. Как и текстовые процессоры, и редакторы презентаций, таб- личные процессоры предоставляют возможности стилевого форма- тирования. В них включён стандартный набор стилей (вкладка Главная, группа Стили), которые можно использовать для оформ- ления объектов электронной таблицы. Этот набор можно допол- нять собственноручно разработанными стилями. 25 §2 редактирование и форматирование в тП Формат ячейки можно применить к другим ячейкам, исполь- зуя инструмент Формат по образцу (вкладка Главная, группа Буфер обмена). Для очистки всех установленных форматов, т. е. для возврата к формату по умолчанию, нужно выделить ячейки и выполнить команду Очистить →→ Очистить форматы (вкладка Главная, груп- па Редактирование). На вкладке Защита окна формат ячеек можно установить или отменить режимы защиты ячеек и скрытия формул. Режим защи ты ячеек запрещает несанкционированное изменение данных, а режим скрытия формул — отображение данных в строке формул. Для включения этих режимов нужно установить соответствующие флажки (Защищаемая ячейка и Скрыть формулы), щёлкнуть по кнопке Ок, после чего выполнить команду Защитить лист (вклад ка рецензирование, группа изменения). Откроется окно Защита листа, в котором можно установить пароль для снятия режимов защиты и скрытия, а также разрешить выполнение определённых операций для данных режимов. Форматирование электронной таблицы. Иногда ширины столб- цов (высоты строк), установленной по умолчанию, не хватает, что- бы полностью отобразить содержимое ячеек, или наоборот, для более компактного вида заполненной части таблицы целесообразно уменьшить ширину некоторых столбцов (высоту некоторых строк). Для быстрого автоподбора ширины всех столбцов листа нужно нажать кнопку Выделить всё и выполнить двойной щелчок мы- шью на границе между заголовками двух любых столбцов. Если дважды щёлкнуть на любой границе между заголовками двух строк, то выполнится автоподбор высоты всех строк выделенного диапазона. Предложите ещё не менее двух способов изменения ширины столб цов (высоты строк). В некоторых случаях удобно несколько ячеек, образующих связный диапазон, объединить в одну ячейку. В такую объеди- ненную ячейку можно ввести, например, текст заголовка таблицы или нескольких столбцов. Для этого ячейки нужно выделить, а затем воспользоваться инструментом Объединить и поместить в центре (вкладка Главная, группа Выравнивание). После такого объединения все эти ячейки будут рассматри- ваться как одна ячейка, адресом которой будет считаться адрес верхней левой ячейки соответствующего диапазона. Данные, ко- торые были в ячейках до объединения (кроме тех, что были в |