Учебник. А. Ю. Босова Москва бином. Лаборатория знаний 2016 11 класс Базовый уровень Учебник
 Скачать 4.61 Mb.
|
|
Глава 1. Обработка информации в ЭТ Формула для вычисления суммы, которую клиент получит за вклад по окончании срока вклада, будет иметь вид: =БС(11%/12; 36; 0; –300000). В результате вычислений получаем 416 663,58 руб. Пример 6. Клиент хочет сделать вклад на 2 года на сумму 100 000 руб. под 10,5% годовых с ежемесячным начислением процентов. При этом он имеет возможность ежемесячно попол нять вклад ещё на 2000 рублей. Выясним, какую сумму клиент получит по окончании срока вклада. Для нахождения результата мы воспользуемся той же функ цией, что и в примере 5. Отличие состоит в том, что аргумент плт в этом случае примет значение –2000. Формула для вычисления суммы, которую клиент получит за вклад по окончании срока вклада, будет иметь вид: =БС(10,5%/12; 24; –2000; –100000). В результате вычислений получаем 176 409,84 руб. Как изменится формула в примере 6, если клиент ежемесяч- но будет не пополнять счёт на 2000 руб., а снимать со счёта по 1000 руб.? 3.5. Текстовые функции В основном табличные процессоры используются для работы с числами, но в них предусмотрена и возможность работы с тек стом. Например, в электронные таблицы заносятся наименования товаров и услуг, фамилии, имена и отчества сотрудников, партнё ров и клиентов, их адреса, телефоны и многое другое. Для обработки текста в табличных процессорах имеется набор функций, которые можно использовать для определения длины текста, номера позиции первого вхождения символа в текст, ча сти текста, который удовлетворяет определённому условию и др. Аргументами текстовых функций могут быть текстовые дан ные (их нужно заключать в кавычки), ссылки на ячейки с тек стом, ссылки на ячейки с числами. Рассмотрим примеры некоторых текстовых функций Microsoft Excel. Функция СТРОЧН преобразует все буквы обрабатываемого текста в строчные, а функция ПРОПИСН, наоборот, — в пропи сные. Функция ПРОПНАЧ делает прописной первую букву каж дого слова, а все остальные буквы — строчными. 41 Встроенные функции и их использование § 3 Функция СОВПАД позволяет сравнить две текстовые стро- ки в Microsoft Excel. Если они в точности совпадают, то воз- вращается значение ИСТИНА, в противном случае — ЛОЖЬ (функция учитывает регистр, но игнорирует различие в фор- матировании). Какое значение появится в ячейке С1, если в неё записать форму- лу =СОВПАД(A1; B1)? Какое значение появится в ячейке С2, если в неё скопировать формулу из ячейки С1? Объясните следующий результат сравнения двух текстов: Функция СЖПРОБЕЛЫ удаляет из текста все лишние про- белы, кроме одиночных между словами. Эту функцию полезно применять к данным, которые импортируются в рабочие листы Microsoft Excel из внешних источников. Вспомните, как можно удалить все лишние пробелы из документа с помощью инструментов текстового процессора. Кроме лишних пробелов импортируемые данные могут со- держать и различные непечатаемые символы. Для удаления из текста всех непечатаемых символов предназначена функция ПЕЧСИМВ. Выскажите свои предположения о назначении текстовых функций ДЛСТР, ЛЕВСИМВ, ПРАВСИМВ, ПСТР по результатам их работы: 1) В1=ДЛСТР(А1) 2) В1=ЛЕВСИМВ(А1;3) 42 Глава 1. Обработка информации в эт 3) В1=ПРАВСИМВ(А1;3) 4) В1=ПСТР(А1;3;2) Функция СЦЕПИТЬ последовательно объединяет значения указанных аргументов в одну строку. Функция ПОВТОР повторяет текстовую строку указанное ко- личество раз. Строка задаётся как первый аргумент функции, а количество повторов — как второй. Чему равен результат вычисления по формуле ячейки С2, если ре зультат вычисления по формуле ячейки А2 равен 6? Функцию ПОВТОР можно применить и для «графического» представления числовых значений. Например, с её помощью мож- но визуализировать информацию об успеваемости некоторого уче- ника, получившего в текущем триместре 40 отметок «отлично», 45 — «хорошо» и 15 — «удовлетворительно». Функции НАЙТИ и ПОИСК очень похожи. Они находят вхо- ждение одной строки в другую и возвращают положение первого символа искомой фразы относительно начала текста. Различие в том, что первая учитывает регистр, а вторая — нет. Какие значения будут отображены в ячейках А2 и В2? 43 Встроенные функции и их использование §3 Функция ПОДСТАВИТЬ заменяет определённый текст или символ на новое значение. Её применяют, когда заранее извест- но, какой текст необходимо заменить, а не его местоположение. Функция ЗАМЕНИТЬ заменяет символы в заранее известном месте строки на новые. Функцию применяют, когда известно, где располагается текст, при этом сам он не важен. 1. Что будет отображено в ячейках В1 и В2? 2. С помощью какой из двух последних рассмотренных функций можно удалить все пробелы из текстовой строки? Как это сде лать? 3. В ячейке содержится текст «колокол» (без кавычек). Что будет результатом вычислений по формуле: =ДЛСТР(A1)–ДЛСТР(ПОДСТАВИТЬ(A1; "о";""))? 4. Сформулируйте алгоритм подсчёта количества вхождений опре делённого символа в заданную строку. СамОе ГлаВнОе В любом табличном процессоре используются встроенные функ- ции — заранее написанные процедуры преобразования данных. Каждая встроенная функция имеет имя — как правило, это сокращённое название производимого ею действия. Функции вы- зываются с некоторыми аргументами и возвращают единственное значение — результат обработки. Аргументом функции может быть число, текст, выражение, ссылка на ячейку или диапазон ячеек, результат другой функ- ции. Всё многообразие встроенных в табличные процессоры функ- ций принято делить на категории по их назначению, выделяя среди них математические, статистические, логические, тексто- вые, финансовые и другие типы функций. Для решения уравнений, построения графиков функций и т. д. могут быть полезны математические функции. Для автоматизации статистической обработки данных предназ- начены статистические функции. С их помощью можно вычис- лить наибольшее, наименьшее или среднее значение, подсчитать количество ячеек, содержащих заданную информацию, и т. д. 44 Глава 1. Обработка информации в эт Функция, результатом которой является ИСТИНА или ЛОЖЬ, называется логической. Финансовые функции используются для вычисления размеров выплат при погашении кредитов, банковских процентов на вкла- ды, для определения процентной ставки и др. Для обработки текста в табличных процессорах имеется на- бор функций, которые можно использовать для определения длины текста, номера позиции первого вхождения символа в текст, части текста, который удовлетворяет определённому усло- вию и др. Вопросы и задания 1. Раскройте суть математического понятия «функция». Что такое аргумент функции? Какие функции вы знаете из кур- са алгебры? 2. Что представляют собой функции в электронных таблицах? На какие категории они подразделяются? 3. Выясните, чему равен результат функции ОКРУГЛ, если заданное число разрядов больше нуля, меньше нуля, равно нулю. 4. Сколько аргументов могут иметь функции в электронных таблицах? Приведите примеры. 5. Данные каких типов могут быть аргументами функций? Приведите примеры. 6. Какие функции относятся к категории логических? 7. Какие значения будут в ячейках диапазона А2:В5 в резуль- тате вычисления по соответствующим формулам? 8. Прочитайте формулу: =ЕСЛИ(A1=100; "Всегда"; ЕСЛИ(И(A1>=80; A1<100); "Обычно"; ЕСЛИ(И(A1>=60; A1<80); "Иногда"; "Никогда"))). 45 Встроенные функции и их использование §3 Постройте фрагмент блок-схемы, соответствующий формуле. 9. Какие формулы надо использовать, чтобы для заданных зна- чений переменной x вычислить соответствующие значения функции: sin , ; , ; , 2 x x x x x x x 5 5 5 1 4 5 2 y 10. Десять спортсменов-многоборцев принимают участие в сорев- нованиях по пяти видам спорта: бег на 60 м с барьерами, прыжок в высоту, толкание ядра, прыжок в длину, бег на 800 м. На квалификационном этапе по каждому виду спор- та спортсмен может набрать от 0 до 30 очков. Спортсмен проходит в группу финалистов, если он набирает в сумме 100 и более очков. Создайте электронную таблицу следую- щего вида: Введите данные и выполните необходимые расчёты. 11. Как изменится цена некоторого товара, если сначала её уве- личить на 25%, а затем уменьшить на 25%? 12. Клиент хочет выяснить, какие условия вклада в банк вы- годнее ему: 10,5% годовых с начислением процентов ежеме- сячно или 12% годовых с начислением процентов каждые полгода. Какая функция нужна для решения этой задачи? 13. Для чего в табличный процессор включены текстовые функ- ции? 46 Глава 1. Обработка информации в эт § 4 инструменты анализа данных 4.1. Диаграммы Как правило, электронные таблицы содержат большое количе- ство числовых данных, которые требуется сравнивать, оценивать их изменение с течением времени, определять соотношение между ними т. д. Проводить подобный анализ большого количества чи- словых данных значительно легче, если изобразить их графиче- ски (визуализировать). Для графического представления числовых данных используются диаграммы. Диаграмма — это графическое представление числовых данных, позволяющее быстро оценить соотношение нескольких величин. Табличные процессоры позволяют строить диаграммы следу- ющих типов: • гистограмма; • линейчатая диаграмма; • круговая диаграмма; • график; • диаграмма с областями; • поверхностная диаграмма; • лепестковая диаграмма и др. Чтобы в Microsoft Excel просмотреть все доступные типы диа грамм, изучите группу Диаграммы на вкладке Вставка (рис. 1.8). Выясните, какие типы диаграмм можно создавать в табличном процессоре, имеющемся в вашем распоряжении. рис. 1.8. Типы диаграмм в Microsoft Excel В диаграмме любого типа можно выделить следующие объ- екты (рис. 1.9): 1 — область диаграммы (в ней размещаются все объекты диа- граммы); 2 — название диаграммы, чётко описывающее то, что представ- лено на диаграмме; 47 инструменты анализа данных §4 3 — область построения диаграммы (непосредственно в ней рас- полагается сама диаграмма); 4 — ось значений (вертикальная, ось Y). На ней находится шкала с определённым шагом, устанавливаемым автоматически, в зависимости от наименьшего и наибольшего значений дан- ных, изображённых на диаграмме. Именно по этой шкале можно оценить данные, представленные на диаграмме; 5 — ряды данных — наборы числовых данных, некоторым образом связанных между собой и размещённых в электронной табли- це в одной строке или столбце. На диаграмме ряд данных изображается геометрическими фигурами одного вида и цвета; 6 — ось категорий (горизонтальная, ось X). На ней отображаются значения определённого свойства данных; 7 — легенда, поясняющая соответствие между названиями рядов и используемыми на диаграмме цветами. По умолчанию на- звания рядов являются названиями строк (или столбцов) ди- апазона данных, по которым построена диаграмма; 8 — названия осей. Воспроизведите в табличном процессоре диаграмму, представлен ную на рисунке 1.9. С помощью контекстного меню исследуйте свойства каждого объекта этой диаграммы. рис. 1.9. Основные элементы диаграммы 48 Глава 1. Обработка информации в эт На диаграммах разных типов числовые данные могут быть представлены точками, отрезками, прямоугольниками, секторами круга, прямоугольными параллелепипедами, цилиндрами, кону- сами и другими геометрическими фигурами. При этом размеры геометрических фигур или расстояния от них до осей пропорци- ональны числовым данным, которые они отображают. Диаграммы, создаваемые в электронных таблицах, динами- ческие — при редактировании данных в таблице размеры или количество фигур, обозначающих эти данные, автоматически из- меняются. Вспомните основные приёмы построения диаграмм, известные вам из курса информатики основной школы. Рассмотрим самые распространённые типы диаграмм. Гистограммы целесообразно создавать тогда, когда нужно сравнить значения нескольких наборов данных, графически из- образить отличия значений одних наборов данных от других, по- казать изменения данных с течением времени. Различают следующие виды гистограмм: • гистограмма с группировкой; • гистограмма с накоплением; • нормированная гистограмма с накоплением; • объёмная гистограмма. В гистограмме с группировкой прямоугольники, которые яв- ляются графическими изображениями числовых данных из раз- ных наборов, располагаются рядом друг с другом (см. рис. 1.9). В гистограмме с накоплением прямоугольники, изображающие числовые данные, располагаются друг над другом (рис. 1.10). Это даёт возможность оценить суммарные данные и вклад каждой составляющей в общую сумму. В нормированной гистограмме с накоплением вертикальная ось имеет шкалу в процентах. Это даёт возможность оценить долю (процентную часть) данных в общей сумме (рис. 1.11). Подумайте, по какой из трёх диаграмм проще всего определить: 1) продажа каких напитков неуклонно возрастала; 2) продажа каких напитков принесла наибольшую прибыль в июле; 3) динамику изменений суммарной выручки от продажи всех трёх напитков; 4) вклад от продажи каждого напитка в общую выручку. 49 инструменты анализа данных §4 рис. 1.10. Пример гистограммы с накоплением рис. 1.11. Нормированная гистограмма с накоплением Линейчатые диаграммы аналогичны гистограммам и отлича- ются от них лишь горизонтальным расположением геометриче- ских фигур. К типу диаграмм Круговая относятся плоские и объёмные круговые диаграммы. Их целесообразно использовать тогда, ког- да нужно отобразить части одного целого, сравнить соотношение частей между собой и отношение частей к целому. Круговые диаграммы позволяют отобразить только один ряд данных. Они теряют наглядность, если содержат много элементов данных. Несколько круговых диаграмм можно заменить, напри- мер, одной нормированной гистограммой с накоплением. 50 Глава 1. Обработка информации в ЭТ Подумайте, сколько разных круговых диаграмм можно построить по информации, содержащейся в диаграмме, представленной на рисунке 1.9. Сколько круговых диаграмм потребуется для того, чтобы изобра- зить информацию, представленную на гистограмме с накоплением (см. рис. 1.10)? Диаграммы типа График целесообразно использовать, если количество данных в наборе достаточно большое, если нужно от образить динамику изменения данных во времени, сравнить из менения нескольких рядов данных (рис. 1.12). Рис. 1.12. Пример диаграммы График с маркерами Точечные диаграммы с гладкими кривыми можно использо вать для построения графиков функций, предварительно заполнив диапазон ячеек значениями аргумента и соответствующими значе ниями функции. Можно построить на одной диаграмме графики двух функций и использовать их для приближённого решения уравнения. Пример. Найдём на отрезке [0; 1,2] корень уравнения cos(x) = x , построив в табличном процессоре графики функций, соответствующих левой и правой частям равенства. Для этого: 1) используя стандартные функции COS и КОРЕНЬ, построим таб лицу значений функций для x, изменяющегося с шагом 0,1: 51 Инструменты анализа данных § 4 Рис. 1.13. Графики функций 2) по значениям диапазона В2:N3 построим графики функций COS(x) и КОРЕНЬ(x) (рис. 1.13); 3) заменим номера точек, проставленные по горизонтальной оси, на значения аргумента х рассматриваемых функций. Для это- го вызовем контекстное меню горизонтальной оси и выберем пункт Выбрать данные. Появится окно Выбор источника дан- ных (рис. 1.14). В открывшемся окне нажмём на кнопку изменения подписей горизонтальной оси и выберем диапазон со значениями аргу- мента (рис. 1.15). Рис. 1.14. Окно Выбор источника данных 52 Глава 1. Обработка информации в ЭТ Рис. 1.15. Окно Подписи оси После редактирования (совмещения) точки пересечения осей и добавления вертикальных линий сетки график приобретёт вид, представленный на рисунке 1.16. Рис. 1.16. График после редактирования В результате построения графиков функций видно, что корень уравнения приблизительно равен 0,64. Построенную диаграмму можно редактировать: • изменять способ формирования ряда данных: из данных стро ки или из данных столбца; • изменять диапазон ячеек, по которым строится диа грамма; • изменять тип, вид или макет диаграммы; • вставлять, перемещать, удалять или изменять название диа граммы, легенды, подписей данных; • изменять отображение осей и линий сетки и др. Построенную диаграмму можно форматировать. При этом можно применить стилевое форматирование сразу ко всей диа грамме, воспользовавшись одним из стилей оформления диа грамм. Кроме того, можно форматировать отдельные объекты диаграммы, которые предварительно надо выделить. 53 инструменты анализа данных §4 Некоторые объекты диаграммы, например ряд, состоят из не- скольких частей. Чтобы выделить только одну часть, например отдельную точку ряда, необходимо сначала выделить весь объект, а затем выбрать нужную его часть. 4.2. Сортировка данных Данные в электронной таблице можно сортировать, т. е. из- менять порядок их расположения в строках или столбцах. В от- сортированных данных легче найти необходимые значения, осу- ществить их анализ, выявить имеющиеся закономерности и др. Сортировка — это упорядочение данных в таблице. Сортировка данных может проводиться по возрастанию (от наименьшего к наибольшему) или по убыванию (от наибольшего к наименьшему). В Microsoft Excel соответствующие инструменты размещены на вкладке Данные в группе Сортировка и фильтр. В Microsoft Excel сортировка данных по возрастанию заклю- чается в следующем: • символы упорядочиваются в порядке размещения их кодов в кодовой таблице Unicode; • числа и даты упорядочиваются от наименьшего значения к наибольшему и располагаются перед текстовыми данными, причём сначала располагаются числа; • текстовые данные сначала упорядочиваются по их первым символам; если первые символы в текстах совпали, то они упорядочиваются по вторым символам; тексты, в которых сов- пали первые два символа, упорядочиваются по их третьим символам и т. д.; • логическое значение ЛОЖЬ размещается перед значением ИСТИНА; • пустые ячейки всегда располагаются последними. При сортировке данных по убыванию порядок расположения будет обратный, за исключением пустых ячеек, которые всегда располагаются последними. Далее представлены введённые данные (диапазон А1:А15) и ре зультаты их сортировки. Определите, где представлены данные, отсортированные по возрастанию, где — по убыванию. Объясните полученные результаты. |