Лаб.практикум по инф-ке_МУ. С. Л. Миньков лабораторный практикум по информатике
 Скачать 4.64 Mb.
|
|
Enter или клавишу перемеще- ния курсора. Если число не входит в ячейку, то Excel отображает его либо в экспоненциальной форме (1230000000 1,23Е+09; 0,0000567 5,67E-05), либо вместо числа ставит знаки ####. То- гда необходимо раздвинуть границы ячейки. В Excel можно выбрать различные форматы представления чисел: Главная | группа Ячейки | Формат | Формат ячеек | Число | Числовые форматы (рис. 1.4).Это же самое можно про- делать через вкладку Главная | Число или черезконтекстно- зависимое меню, щелкнув по ячейке правой кнопкой мыши и вы- брав Формат ячеек. Если при вводе числа допущена ошибка (например, по- ставлена десятичная точка вместо десятичной запятой 1 ), то оно будет восприниматься как текст. Это легко заметить: текст по умолчанию выравнивается по левому краю ячейки, а число — по правому. 1 Какой именно знак используется для разделения целой и дробной частей числа определяется в операционной системе. В Windows это можно сделать, выбрав путь Пуск | Панель управления | Языки и региональ- ные стандарты | Настройка | Числа | Разделитель целой и дробной ча- сти числа и определив нужный знак. 13 Рис. 1.4 — Диалоговое окно Формат ячеек (закладка Число) Если текст не входит в ячейку, то можно выполнить сле- дующие действия: – раздвинуть границы ячеек по горизонтали, встав курсо- ром на границу между буквами столбцов (широкий крест курсора превращается в черный крестик со стрелками) и, удерживая нажатой левую клавишу мыши, сдвинуть границу на требуемое расстояние; – объединить несколько ячеек и в них записать текст. Для этого необходимо выделить несколько соседних ячеек и открыть диалоговое окно Формат ячеек, как это было описано выше, и выбрать Выравнивание | Объединение ячеек (рис. 1.5). Эта опция также вынесена и на Ленту (вкладка Главная | Выравни- вание | Объединить и поместить в центре); – организовать перенос текста в ячейке по словам в диало- говом окне Формат ячеек: Выравнивание | Переносить по словам. 14 Рис. 1.5 — Диалоговое окно Формат ячеек (закладка Выравнивание) В виде формулы в ячейке записывается арифметическое или логическое выражение, состоящее из чисел, адресов ячеек и функций, соединенных между собой знаками арифметических операций и операций отношения, и начинающееся со знака «=» (частая ошибка — набор формулы производится не со знака «=», а с пробела). При его записи следует соблюдать обычные правила алгоритмических языков: арифметические операции выполняют- ся слева направо в порядке старшинства (возведение в степень — «^», умножение — «*», деление — «/», сложение — «+», вычита- ние — « »). Для изменения порядка выполнения операций ис- пользуются круглые скобки, аргумент функции также берется в круглые скобки, причем вычисление функции обладает высшим приоритетом по сравнению с арифметическими операциями. Помните, что адреса ячеек набираются только латин- ским шрифтом! Но гораздо проще вместо набора адреса ячейки навести на нее указатель и щелкнуть левой кнопкой мыши. Двойной щелчок левой клавишей мыши на ячейке с введен- ными данными осуществляет переход в режим редактирования данных. При этом указатель приобретает вид вертикальной линии. 15 Переход в режим редактирования данных также можно осуществить щелчком по строке формул. Для того чтобы переместить данные, следует выделить ячейку или блок, поместить курсор на рамку ячейки или блока (при этом курсор примет форму светлой стрелки со знаком «че- тырех направлений»), нажать левую клавишу мыши и, удерживая ее, переместить ячейку или блок в требуемое место. Копирование данных производится аналогично перемещению, но с нажатой клавишей Ctrl (при этом курсор приобретает вид светлой стрелки со знаком «+»). Аналогичные действия можно провести с помощью кон- текстно-зависимого меню (Копировать, Вырезать, Вставить) или через вкладку Главная | группа Буфер обмена. Пример 1.1. Рассмотрим применение некоторых возможно- стей Excel при создании таблицы выполнения плана (рис. 1.1). 1. В ячейку В2 вводим текст «Задание. Определить процент выполнения плана и рост», в ячейку В3 — текст «производства по предприятиям». Изменять ширину ячеек нет смысла, т. к. в со- седние справа ячейки текст не вносится и ничто не помешает увидеть набранный текст полностью. 2. Объединяем ячейки В5:B6 и вводим текст «Предприятия отрасли», центрируя его (Формат ячеек | Выравнивание | По вертикали (По горизонтали) | По центру) и изменяя ширину ячейки. 3. Объединяем ячейки C5:E5 и вводим текст «Объем произ- водства, млн руб.». Аналогично объединяем F5:F6 — текст «Процент выполнения плана», и G5:G6 — текст «Относительный прирост». В последних двух блоках также задаем режим Перено- сить по словам. Передвигая границы ячеек, добиваемся требуе- мого расположения текста в ячейках. 4. В ячейки В7, B8, B9 вводим названия предприятий, а в ячейки C6, D6, E6 — текст «Отчет...», «План...». 5. Заполняем ячейки C7:E9 входными данными. 6. Заполняем строку «Среднее по отрасли». Для этого выде- ляем ячейку C10 и выбираем вкладку Формулы. На появившейся ленте выбираем Вставить функцию | Категория | Статистиче- ские | Выберите функцию: | СРЗНАЧ (рис. 1.6)| ОК. В появив- шемся окне задаем диапазон C7:C9. После нажатия кнопки ОК 16 в ячейке C10 появляется результат нахождения среднего значе- ния содержимого ячеек C7:C9. Сама формула =СРЗНАЧ (C7:C9) высвечивается в строке формул. Окно Мастер функций также открывается щелчком по кнопке со знаком x f (Вставить функцию), расположенной слева от строки формул. Примечание. Excel содержит большое количество встроен- ных функций: математических, статистических, финансовых и других, сгруппированных по категориям. Знание и умелое при- менение этих функций облегчает процесс обработки информа- ции. Более подробную информацию о каждой функции можно найти, открыв справку по MS Excel (клавиша F1 клавиатуры) или выбрав ссылку Справка по этой функции в нижнем левом углу диалогового окна Мастер функций (рис. 1.6). Рис. 1.6 — Диалоговое окно Мастер функций 7. Копируем полученную формулу в ячейки D10 и E10. Для этого указываем на маленький черный квадратик в правом нижнем 17 углу ячейки C10 (курсор при этом превращается в черный кре- стик — маркер заполнения), нажимаем левую кнопку мыши и, не отпуская ее, двигаем мышь вправо, пока рамка не охватит ячейки D10 и E10. В ячейке D10 появится формула: = СРЗНАЧ (D7:D9), а в ячейке E10 — формула: = СРЗНАЧ (E7:E9). Таким образом, при копировании произошла автоматиче- ская замена адресов в формуле. Это очень полезное свойство Excel, позволяющее заметно упростить рутинные операции по вводу формул. Примечание. Если же при копировании требуется оставить неизменным адрес какой-нибудь ячейки (или только столбца, или только строки), то перед именем столбца и/или номером строки ставится символ $, например $D$5, H$4, $A2. Это называется аб- солютной адресацией ячейки. Помечать знаком $ номер строки (имя столбца) или снимать пометку можно также с помощью кла- виши F4, поместив курсор рядом с адресом ячейки. 8. Заносим в ячейку F7 формулу: =E7/D7, а в ячейку G7 — формулу: =(E7-C7)/C7. 9. Выделяем ячейки F7 и G7 и копируем сразу две формулы на ячейки F8:F10 и G8:G10 соответственно. Чтобы задать процентный формат чисел в ячейках F7:G10, выделяем их, переходим в Формат ячеек | Число | Числовые форматы: | Процентный | Число десятичных знаков | 2. Это же самое выполняется быстрее при помощи кнопки Процентный формат на вкладке Главная | Число. Если число десятичных знаков меньше (или больше) требуемого, то следует использовать кнопки Увеличить разрядность или Уменьшить разрядность (там же). Таблица заполнена. 10. Оформим таблицу, нарисовав внутренние и внешние рамки: Формат ячеек | Границы или используя кнопку Грани- цы на вкладке Главная | группа Шрифт. 18 Можно подобрать для разных частей таблицы различный фон (тип штриховки, цвет штриховки, цвет фона): Формат ячей- ки | Заливка или используя кнопки Цвет заливки и Цвет текста на вкладке Главная | группа Шрифт. Аналогичные возможности предоставляет вкладка Главная | группа Стили | Стили ячеек и Разметка страницы | Темы. Примечание. Для распространения формата одной ячейки на другую удобно пользоваться кнопкой Формат по образцу на вкладке Главная | Буфер обмена. Сначала нужно щелкнуть по «родительской» ячейке, затем по кнопке (к маркеру «прилип- нет» знак кисти), затем по ячейке, куда нужно перенести формат «родительской» ячейки. При этом переносятся все параметры «ро- дительской» ячейки: шрифт, формат числа, цвет, границы и т. п. Теперь таблица окончательно готова — и в вычислительном аспекте, и в эстетическом. При изменении исходных данных в ячейках C7:E9 результа- ты, находящиеся в ячейках C10:E10 и F7:G10, будут автоматиче- ски пересчитываться. 1.4 Автозаполнение В Excel существует полезная функция автозаполнения, ре- комендуемая при заполнении рядов данных. Если ввести в две соседние ячейки последовательно два числа, составляющие начало арифметической прогрессии, например 1 и 2, затем их выделить и, как при копировании, с по- мощью маркера заполнения «протащить» выделение на несколь- ко ячеек, то ряд продолжится: 1, 2, 3, 4 и т. д. Excel также позволяет вводить и нечисловые последова- тельности. Например, если ввести в две соседние ячейки Январь и Февраль и осуществить описанную выше операцию, то в сле- дующих ячейках появится Март, Апрель и т. д. Эти последова- тельности, или списки, можно сформировать самому и дать Excel запомнить их. Для этого необходимо открыть меню Excel (Меню или Кнопка Office) и выбрать Параметры (Excel) | Создавать 19 списки для сортировки и заполнения | Изменить списки и в окне Элементы списка записать (разделяя Enter) элементы, составляющие список. Режим автозаполнения также включается при вызове ко- манды Прогрессия на вкладке Главная | группа Редактирова- ние | Заполнить. Перед этим надо задать начальное значение ря- да и выделить диапазон заполнения. 1.5 Примечание Информацию, находящуюся в любой ячейке Excel, можно прокомментировать с помощью вставки Примечание. Для этого необходимо выбрать ячейку, к которой следует добавить приме- чание, вызвать контекстно-зависимое меню, выполнить команду Вставить примечание и ввести текст примечания в появившем- ся поле. После окончания ввода текста следует щелкнуть кнопкой мыши вне области примечания. Для просмотра примечаний в книге просто наведите указате- лем мыши на ячейку с примечанием (она помечена красным тре- угольником). Чтобы сразу увидеть все примечания, нужно нажать кнопку Показать все примечания на вкладке Рецензирование. 1.6 Дублирование данных Одно и то же значение можно внести в несколько ячеек од- новременно. Для этого выделите ячейки, в которые необходимо внести данные (они могут быть как смежными, так и несмежны- ми), введите данные и нажмите клавиши Ctrl+Enter. Если выделено несколько листов (это производят при нажа- той клавише Ctrl), то вносимые в активный лист изменения ав- томатически дублируются в соответствующих ячейках всех остальных выбранных листов. Если данные были введены на одном листе, то их можно скопировать в соответствующие ячейки других листов. Для этого выделите листы, на которые необходимо скопировать данные, выделите ячейки, содержащие копируемые данные, выберите ко- манду По листам на вкладке Главная | группа Редактирование | Заполнить. 20 Чтобы отменить выделение нескольких листов книги, ука- жите любой невыделенный лист. Если же на экране видны только выделенные листы, наведите указатель на выделенный лист и нажмите правую кнопку мыши. Затем выберите команду Раз- группировать листы в контекстном меню. Задание 1.1. Оформление таблицы: 1) открыть книгу Excel и разместить на листе таблицу из выбранного варианта. Лист переименовать в соответствии с вариантом задания; 2) добавить, если необходимо, новые строки и столбцы; 3) дополнительные исходные данные, не указанные в основ- ной таблице, разместить во вспомогательных таблицах и ссы- латься на них через адресацию ячеек; 4) в позиции, помеченные вопросительным знаком, внести формулы в соответствии с требуемым алгоритмом вычисления; 5) оформить таблицу, выделив заголовки, исходные данные и результаты вычислений. Использовать вставку примечаний для пояснения содержимого ячеек. Вариант 1. Найти коэффициент Энгеля, т.е. рассчитать, ка- кой процент занимают расходы на продукты в общей сумме рас- ходов для разных групп населения. Какие данные лучше корре- лируются: расходы на продукты и расходы на жилье или расходы на продукты и расходы на одежду? Виды расходов Группа 1 Группа 2 Группа 3 Группа 4 Группа 5 Продукты, руб. 370 580 1300 1950 2300 Жилье, руб. 88 125 180 200 1400 Коммунальные услуги, руб. 44 60 120 120 120 Одежда, руб. 80 220 800 1500 3500 Другие расходы, руб. 350 860 1200 2500 4400 Итого ? ? ? ? ? Коэффициент Энгеля, % ? ? ? ? ? Примечание. Использовать функцию КОРРЕЛ. Вариант 2. Составить таблицу начисления заработной пла- ты работникам МП «Воронья слободка». 21 Ф.И.О. Тариф- ный раз- ряд Выполнение плана, % Тарифная ставка, руб. Заработная плата с премией, руб. Пряхин Н.П. Суховейко А.Д. Лоханкин В.А. Пферд Л.Ф. Севрюгов Л.А. Гигиенишвили Г.С. Птибурдуков А.И. 3 2 1 1 3 2 3 102 98 114 100 100 94 100 ? ? ? ? ? ? ? ? ? ? ? ? ? ? Примечание 1.Тарифная ставка определяется в зависимости от разряда: 1-й разряд — 14000 руб.; 2-й разряд — 16500 руб.; 3-й разряд — 18000 руб. Тарифные ставки оформить отдельной таблицей. Примечание 2.Размер премиальных определяется в зависи- мости от выполнения плана: – ниже 100 % — премия не начисляется; – 100 % — премия 20 % от тарифной ставки; – 101…….110 % — премия 30 %; – 111 ……115 % — премия 40 %. Для реализации алгоритма начисления используйте вложен- ные функции ЕСЛИ. Вариант 3. Рассчитать объем территориального фонда обя- зательного медицинского страхования в 2001 г. и сравнить пока- затели с 2000 г. Виды поступлений в ФОМС Сумма поступлений, млрд руб. Удельный вес показателя, % 2000 г. 2001 г. к итогу к 2000 г. 1. Страховые взносы 2. Платежи на обязательное ме- дицинское страхование нерабо- тающего населения 3. Штрафные санкции 4. Другие поступления 101,48 75,03 12,77 10,8 148,88 118,32 18,0 16,92 ? ? ? ? ? ? ? ? Итого ? ? ? ? 22 Вариант 4. Рассчитать ежедневный предполагаемый доход от деятельности киносети. Найти ежедневный средний доход с одного места. Кинотеатры Вместимость зрительного зала, мест Средний % посещаемо- сти Цена билета, руб. Количество сеансов в день Доход, тыс. руб. Родина Сибиряк Аэлита Авангард 700 150 300 500 45 40 60 30 140 130 125 140 4 5 3 5 ? ? ? ? Итого ? ? ? ? Вариант 5. Определить экономические показатели фирмы «Геркулес» в отчетном периоде. Се гм ен т рын ка С ебе сто и- м ос ть е ди ни- цы п роду к- ции, тыс . руб. Ц ен а ед ин иц ы проду кц ии , тыс . руб. Объем ре али за ци и, тыс . шт. За траты н а прои звод- ст во, тыс . руб. В ыруч ка от ре ал из ац ии , тыс . руб. Пр ибыль , тыс . руб. A B C D 14 13 14 11 16 15 16 13 10 15 20 10 ? ? ? ? ? ? ? ? ? ? ? ? Итого ? ? ? ? Вариант 6. Рассчитать сумму затрат на выработку тепла по котельным МП «ТепТоп». Статьи затрат Затраты по типам котельных, тыс. руб. Всего Газовая котель- ная Угольная котельная 1. Материалы 2. Амортизация 3. Вода 4. Электроэнергия 5. Заработная плата с начислениями 6. Топливо 7. Ремонтный фонд 8. Цеховые расходы (11,9 % от ст. 10) 9. Общие эксплуатационные расходы (9,1 % от ст. 10) 84,2 165,6 607,1 339,3 621,2 1234,5 590,0 ? ? 85,5 337,5 80,8 333,9 3081,0 2194,7 320,6 ? ? ? ? ? ? ? ? ? ? ? 10. Итого затрат по котельным ? ? ? 23 Статьи затрат Затраты по типам котельных, тыс. руб. Всего Газовая котель- ная Угольная котельная 11. Косвенные затраты 12. Всего затрат по котельным с учетом косвенных 13. Рентабельность (15 % от ст. 12) 391,5 ? ? 1709,5 ? ? ? ? ? 14. Итого затрат с учетом рентабельности ? ? ? 15. Доля затрат котельных разного типа, % ? ? ? Вариант 7. Проанализировать динамику поступления това- ров от поставщиков. Поставщики Поступление товаров, млн руб. Превышение поступлений Удельный вес поставщика в общей сумме поступлений, % 2010 г. 2011 г. Сумма, млн руб. В % к 2010 г. 2010 г. 2011 г. ООО «Прима» АОЗТ «Томь» ЧП «Сантик» ОАО «Гермес» 15,5 23,4 0,96 7,5 16,9 32,1 1,2 6,4 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Всего ? ? ? ? ? ? Вариант 8. Сравнить доходную часть городского бюджета в 2012 и 2013 годах. Статьи дохода Доход в 2012 г. (отчет) Доход в 2013 г. (план) Превышение доходов С ум м а, тыс руб. Уд ел ьн ый ве с, % С ум м а, тыс руб. Уд ел ьн ый ве с, % С ум м а, тыс руб. В % к 2012 г Налоговые доходы 1. Налоги на прибыль (доход), прирост капитала 2. Налоги на товары и услуги, лицензионные сборы 3. Налоги на совокупный доход 4. Налоги на имущество 5. Платежи за пользование природными ресурсами 6. Прочие налоги, пошлины и сборы ? 347660 396110 53810 266900 102600 236580 ? ? ? ? ? ? ? 666562 142887 35696 107253 382380 274296 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 24 Статьи дохода Доход в 2012 г. (отчет) Доход в 2013 г. (план) Превышение доходов С ум м а, тыс руб. Уд ел ьн ый ве с, % С ум м а, тыс руб. Уд ел ьн ый ве с, % С ум м а, тыс руб. В % к 2012 г Неналоговые доходы 1. Доходы от имущества, нахо- дящегося в госсобственности 2. Административные платежи и сборы 3. Штрафные санкции Итого доходов ? 10690 9500 3500 ? ? ? ? ? ? ? 37366 4500 3600 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Вариант 9. Рассчитать начисление стипендии студентам по итогам сессии. Ф.И.О. Оценки за экзамены Начислено стипендии, руб. Информа- тика Экономиче- ская теория Теория вероятности Базы данных 1. Иванопуло И.П. 2. Зверев Д.Б. 3. Калачов Н.А. 4. Калачова Е.Б. 5. Синицына З.С. 6. Писаревская Л.Г. 7. Тарасов А.Н. 8. Паровицкий С.Т. 5 4 4 4 4 5 3 4 5 4 5 3 5 5 5 4 5 5 5 2 3 4 2 5 5 5 5 4 5 4 4 4 ? ? ? ? ? ? ? ? Примечание. Размер стипендии составляет 1560 руб. Если все экзамены сданы на «пятерку», то надбавка составляет 50 %. Если есть хотя бы одна «четверка» (при остальных «пятерках»), то надбавка составляет 25 %. Если есть хотя бы одна «двойка», то стипендия не начисляется. Для реализации алгоритма начис- ления используйте вложенные функции ЕСЛИ. Вариант 10. Рассчитать сумму прибыли ЧП «Московские баранки». 25 Показатели Годы Превышение (понижение) показателей 2012 г. по сравнению с 2010 г. 2010 2011 2012 1. Объем продаж, т 2. Цена единицы продукции, руб./кг 3. Выручка от реализации продукции, руб. 4. Постоянные расходы, тыс.руб. 5. Переменные расходы (60 % от ст. 3), руб. 6. Общая себестоимость продукции, руб. 7. Удельная себестоимость продукции, руб./кг 8. Валовая прибыль, руб. 30 70 ? 800 ? ? ? ? 47 80 ? 900 ? ? ? ? 62 100 ? 1000 ? ? ? ? ? ? ? ? ? ? ? ? Вариант 11. Рассчитать поступление и расходование де- нежных средств избирательных фондов зарегистрированных кан- дидатов в депутаты на должность главы администрации. Показатели Состояние избирательного фонда кандидатов Полесов В.М. Чарушников М.П. Кислярский М.Б. Сумма, руб. % Сумма, руб. % Сумма, руб. % Поступило средств всего, в том числе: – от избирательной комиссии – собственные средства кан- дидата – пожертвования юридических лиц – пожертвования физических лиц ? 2000 800 125000 – ? ? ? ? ? ? 2000 800 1057300 – ? ? ? ? ? ? 2000 2000 4193410 10590 ? ? ? ? ? Израсходовано средств все- го, в том числе: – радио и телевидение – печатные издания – публичные мероприятия – канцелярские расходы – аренда помещений и авто- транспорта – прочие расходы ? – 114418 – – 8800 4940 ? ? ? ? ? ? ? ? 33412 604582 7780 4169 14392 30231 ? ? ? ? ? ? ? ? 752600 1332990 200330 106040 95170 1172180 ? ? ? ? ? ? ? Остаток неизрасходованных средств ? ? ? 26 Примечание. % — процент от общей суммы поступив- ших/израсходованных средств Вариант 12. Рассчитать структуру розничной цены продук- ции исходя из данных таблицы. Структурные составляющие розничной цены Значения показателей по видам продукции, руб. А В С D 1. Себестоимость 2. Рентабельность (25 % от п. 1) 3. Оптовая цена 4. Акциз (70 % от оптовой цены) 5. Наценка посредника 6. НДС (18 % от отпускной цены фирмы) 7. Торговая наценка (10 %) 500 ? ? ? 10 ? ? 710 ? ? ? 10 ? ? 360 ? ? ? 8 ? ? 120 ? ? ? 8 ? ? Розничная цена ? ? ? ? Вариант 13. Рассчитать прибыль, полученную от реализа- ции трех видов продукции. Показатель Продукция Итого А В С 1. Цена изделия, тыс. руб. 2. Количество изделий, реализуемых в рас- сматриваемом периоде, шт. 3. Выручка от реализации, тыс. руб. 4. Удельный вес каждого изделия в общем объеме реализации, % 5 500 ? ? 10 700 ? ? 20 300 ? ? – ? ? ? 5. Переменные расходы в расчете на одно изделие, тыс. руб. 6. Переменные расходы по каждому виду продукции, тыс. руб. 7. Постоянные расходы в рассматриваемом периоде, тыс. руб. 3 ? – 6 ? – 12 ? – – ? 6000 8. Итого расходов, тыс. руб. ? 9. Прибыль, тыс. руб. ? Вариант 14. Рассчитать изменение денежных средств с уче- том доходов, полученных от вложения финансовых средств в од- но-, трех- и шестимесячные депозиты к началу 7-го месяца. Доход от одномесячного депозита —1 %, от трехмесячного депозита — 4 %, от шестимесячного депозита — 9 %. Результаты расчета предлагается оформить в виде следующей таблицы. 27 Статьи доходов (расходов), тыс. руб. Период, месяц Итог на начало 7-го месяца 1 2 3 4 5 6 Начальная сумма Погашение депозитов Проценты по депозитам 1-месячный депозит 3-месячный депозит 6-месячный депозит Внутренние расходы Сумма на конец месяца 500 – – 50 100 150 30 ? ? ? ? 50 – – 30 ? ? ? ? 50 – – 30 ? ? ? ? 50 100 – 30 ? ? ? ? 50 – – 30 ? ? ? ? 50 – – 30 ? ? ? ? – – – – – Итого ? Примечание. Процентные ставки по депозитам также офор- мить в виде таблицы и при ссылке на них использовать адреса соответствующих ячеек. Вариант 15. Проанализировать затраты на рекламу произ- водителей товаров и услуг по различным секторам рынка. Стои- мость рекламных объявлений в газете «Реклама», руб. за 1 кв. см: первая полоса — 75, последняя полоса — 43, полоса с програм- мой ТВ — 30, обычная полоса — 15 (данные оформить отдельной таблицей и ссылаться на них через адреса ячеек). Для определения места по затратам использовать функцию РАНГ. Секторы рынка Площадь объявлений, кв. см Затраты на рекламу, тыс. руб. Место по за- тратам Первая полоса Последняя полоса Полоса с программой ТВ Обычная полоса Информационные технологии и услуги 430,8 135,0 208,8 5363,5 ? ? Продовольственные товары 41,3 0 0 4191,3 ? ? Строительно- хозяйственные то- вары и услуги 149,3 138,5 488,3 3697,8 ? ? Предметы гигиены и санитарии 0 0 0 949,0 ? ? Одежда и обувь 0 0 108,0 534,5 ? ? Мебель и торговое оборудование 134,0 0 0 2071,3 ? ? Лекарства и меди- цинские услуги 0 0 42,3 568,0 ? ? Автотовары и авто- услуги 50,0 0 0 2648,1 ? ? 28 Секторы рынка Площадь объявлений, кв. см Затраты на рекламу, тыс. руб. Место по за- тратам Первая полоса Последняя полоса Полоса с программой ТВ Обычная полоса Бытовая техника и ее обслуживание 0 420,0 0 1680,3 ? ? Недвижимость 76,3 0 0 2087,3 ? ? Итого ? ? ? ? ? ? 1.7 Построение диаграмм С помощью Excel можно превращать сухие и абстрактные строки и столбцы чисел в привлекательные и информативные диаграммы. Визуальное представление информации облегчает ее восприятие, помогает лучше представить поведение функцио- нальных зависимостей. В Excel используются различные типы диаграмм: гисто- граммы (изображение данных в виде столбиков различной фор- мы), графики (в виде линий), диаграммы-области (сплошная за- ливка), круговые, кольцевые, лепестковые, трехмерные и другие диаграммы. Вызов списка типов на лист Excel происходит по команде Вставка | группа Диаграммы | Вставка диаграммы (рис. 1.7). Рис. 1.7 — Типы диаграмм 29 1.7.1 Построение гистограммы Пример 1.2. Визуализируем некоторые данные из таб- лицы 1.1, показывающей доход от реализации колбасных изделий ООО «Рога и копыта». Таблица 1.1 Наименование изделий Объем про- изводства, т Цена за кг, руб. Торгово- сбытовая скидка, % Цена со скидкой, руб. Сумма, тыс. руб. Колбаса арбатовская п/к, 1 сорт 5 350 8 322 1750 Колбаса остаповская п/к, 1 сорт 12 360 8 331,2 4322 Колбаски восточные, п/к, в/с 2 580 8,5 530,7 1160 Колбаса «Геркулес» п/к, 2 сорт 14 250 7,8 230,5 3500 Колбаса черноморская п/к, в/с. 3 660 8,5 603,9 1980 Итого 36 12710 Порядок действий следующий: Вариант 1 1. Выделим первые два столбца вместе с заголовками, но без «Итого». В рассматриваемом примере выделять всю таблицу не рекомендуется, т. к. на одном поле построения диаграммы следует показывать строки или столбцы, содержащие группы данных одного наименования (или руб., или кг, или %). «Итого» вместе со слагаемыми обычно не показывают. 2. Выбираем вкладку Вставка | группа Диаграммы | Вставка диаграммы (рис. 1.7). 3. Выбираем тип диаграммы Гистограмма и вид диаграммы Объемная диаграмма с группировкой (название проявляется при наведении курсора на объект). 4. Щелчок по выбранному типу приводит к появлению ги- стограммы заказанного вида на выбранном массиве данных (рис. 1.8). 30 Рис. 1.8 — Гистограмма, построенная по двум столбцам табл. 1.1 (легенда удалена) Вариант 2 Можно начинать построение диаграммы и без выделения области построения, выбрав двойным щелчком вид диаграммы в окне Вставка диаграммы (рис. 1.7). При этом в областилиста Excel появится пустое поле, а в области ленты — лента «Работа с диаграммами» (рис. 1.9), вкладка Выбрать данные которой да- ет возможность выбрать необходимые данные для построения диаграммы (рис. 1.10). Также обратите внимание на вкладки этой ленты Макет и Формат, предоставляющие богатые возможности для форматирования созданной диаграммы. Рис. 1.9 — Лента Работа с диаграммами 31 Рис. 1.9 — Лента Работа с диаграммами Рис. 1.10 — Диалоговое окно Выбор источника данных Выбираем ту же область, но (для разнообразия) меняем ме- стами строки и столбцы (кнопка Строка/Столбец). Гистограмма принимает вид (рис. 1.11): подписи строк стали легендой диа- граммы, а заголовок — подписью строк. Столбики гистограммы имеют разные цвета. Но если пред- полагается размещение полученной гистограммы в черно-белом варианте, то вместо цветовой гаммы рекомендуется использовать различную штриховку. Рис. 1.11 — Гистограмма, построенная по двум столбцам табл. 1.1 Для этого, щелкнув по выбранному столбику правой кноп- кой мыши, выбираем в контекстном меню команду Формат ряда 32 данных. В появившемся окне выбираем Заливка | Узорчатая заливка (рис. 1.12) и соответствующий тип заливки столбика. Операцию повторяем для каждого столбца. Рис. 1.12 — Диалоговое окно Формат ряда данных Гистограмма приобретает следующий вид (рис. 1.13). Рис. 1.13 — Вариант гистограммы с узорчатой заливкой 33 Также в диалоговом окне Формат ряда данных можно из- менить и другие характеристики построенной гистограммы. После построения диаграммы имеется возможность изме- нить все ее параметры. Для этого достаточно щелкнуть правой клавишей мыши по области построения диаграммы и выбрать в появившемся контекстно-зависимом меню необходимую опцию (обратите внимание, что меню будет иметь разный вид в зависи- мости от того, по какому месту щелкнуть: по легенде, по оси, по стенке, по области диаграммы). 1.7.2 Построение графика функции одной переменной Тип диаграммы Точечная показывает отношения между численными значениями в нескольких группах. По умолчанию первая группа (столбец или строка) принимается за аргумент (х), а другие — за соответствующие значения функций. Точечная диаграмма имеет две оси значений, при этом одни числовые значения выводятся вдоль горизонтальной оси (оси X), а другие — вдоль вертикальной оси (оси Y). Точечные диаграм- мы обычно используются для иллюстрации и сравнения число- вых значений, например научных, статистических или техниче- ских данных. Пример 1.3. В табл. 1.2 приведена зависимость цены еди- ницы некоторого товара от объема его продаж (известная в эко- номике «кривая спроса D-D»). Таблица 1.2 Объем продаж, тыс. шт. 8 10 18 20 32 40 50 Цена, руб. 510 430 350 280 200 100 80 Для построения кривой спроса: 1) выделяем всю таблицу, вызываем Вставка | группа Диа- граммы | Вставка диаграммы | Точечная | Точечная с глад- кими кривыми | ОК; 34 2) убираем легенду, а вместо названия диаграммы «Цена, руб.» вписываем «Спрос»; 3) на ленте Работа с диаграммами выбираем вкладку Ма- кет и исполняем команду Названия осей | Название основной горизонтальной оси | Название под осью. Вместо текста «Название оси», появившегося на диаграмме, пишем «Объем продаж, тыс. шт.»; 4) аналогично для вертикальной оси выбираем опцию «По- вернутое название» и вписываем «Цена, руб.». Построение и форматирование графика закончено (рис. 1.14). Рис. 1.14 — График спроса, построененый по табл. 1.2 Если функция задана аналитической зависимостью ( ), y f x то нужно предварительно ее протабулировать, т. е. построить таблицу из двух строк (или столбцов) { , }, i i x y где 0 i x x i h — узловые точки; 0 ( ) / n h x x n — шаг табуляции; 0, , , i n а ( ). i i y f x Для заполнения ряда x можно использо- вать режим автозаполнения или формулу увеличения значения х на один шаг. Пример 1.4. Построить на одном поле графики зависимости функций y = sin(x) и y = cos(x) в диапазоне x [–2;2]. Табулируем функции, т. е. строим таблицу значений функ- ций y = sin(x) и y = cos(x) в указанном диапазоне (рис. 1.14). 35 Рис. 1.14 — Таблица и график функций у = sin(x) и у = cos(x) Выделяем всю таблицу, выбираем Вставка | группа Диа- граммы | Точечная с гладкими кривыми | ОК . На листе появ- ляется диаграмма с графиками (рис. 1.15). Проводим ее формати- рование. 1) Наводим указатель мыши на линию графика y=cos(x), щелкаем правой кнопкой мыши, выбираем Формат ряда данных | Тип линии | Тип штриха | Пунктир | Закрыть. Линия стано- вится пунктирной. 2) Наводим указатель мыши на оцифровку вертикальной оси, щелкаем правой кнопкой мыши, выбираем Формат оси | Число | Число десятичных знаков | 2 | Закрыть. Установлен показ 2-х знаков после запятой в числах вертикальной оси. Рис. 1.15 — График функций у = sin(x) и у = cos(x) (до форматирования) 36 3) Наводим указатель мыши на оцифровку горизонтальной оси, щелкаем правой кнопкой мыши, выбираем Добавить основ- ные линии сетки. Появились вертикальные линии сетки (рис. 1.14). 1.7.3 Построение графика функции двух переменных Графическое представление функции двух переменных — поверхность в трехмерном пространстве. Пример 1.5. Построим график производственной функции Кобба—Дугласа , Y A K L где , , A — константы, K — объем производственных фондов, L — объем трудовых ресурсов, Y — выпуск продукции пред- приятием или отраслью. Эти переменные могут выражаться либо в стоимостном выражении, либо в натуральном количестве. Пусть функция имеет вид: 0,5 0, 25 900 , Y K L где K 100 ..300 тыс. руб.; L 30...200 тыс. руб. Табулируем функцию, располагая значения K по горизон- тали с шагом 10, а L — по вертикали с шагом 5. На пересечении столбца со значением i K и строки со значением j L вводим зна- чение функции 0,5 0,25 , 900 i j i j Y K L (рис. 1.16). Верхнюю левую ячейку оставляем пустой. ри наборе формулы необходимо зафиксировать знаком $ номер строки переменной, изменяющейся по горизонтали (т. е. K ), и номер столбца переменной, изменяющейся по верти- кали (т. е. L ). 37 Рис. 1.16 — Элемент листа Excel с табулированием функции двух переменных Например, в ячейке С4 находится формула вида =900*C$2^0,5*$A4^0,25. Тогда достаточно один раз ввести формулу, например в ячейку В3, и затем скопировать ее на все ячейки таблицы. Про- изойдет корректная автоматическая смена адресов переменных (проверьте!). Порядок построения этой поверхности следующий. 1. Выделяем всю таблицу (и значения аргументов, и значе- ния функции), начиная с пустой левой верхней ячейки и заканчи- вая правой нижней (A2:V37). 2. Выбираем Вставка | группа Диаграммы | Поверхность (если отсутствует, то Другие | Поверхность |) ОК. 3. Проводим необходимое форматирование, элементы кото- рого описаны выше. Получили поверхность, представленную на рис. 1.16. Используя опцию Макет | Поворот объемной фигуры, можно двигать область диаграммы, рассматривая поверхность в различных ракурсах. 38 Задание 1.2. Построение гистограмм Построить гистограммы, иллюстрирующие табличные дан- ные из задания 1.1. Тип диаграммы выбрать исходя из степени наглядности представления информации. Задание 1.3. Построение графиков Построить графики функции одной переменной на отрезке [ 2; 2] x для одного из выбранных вариантов, приведенных ни- же, для разных шагов табуляции: 0,5; 0,2; 0,1. Сравнить вид гра- фиков, сделать выводы. Примечание. При записи формулы использовать функцию ЕСЛИ. Она возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Синтаксис этой функции (рис. 1.17): ЕСЛИ(условие;значение_если_истина;значение_если_ложь). Рис. 1.17 — Диалоговое окно функции ЕСЛИ. Значение ячейки А10 равно 1 39 В условии для сравнения двух значений используются опе- раторы сравнения: = (равно); > (больше); < (меньше); >= (не меньше); <= (не больше); <> (не равно). Например, формула =ЕСЛИ(А1>=0;КОРЕНЬ(A1);”число от- рицательное!”), записанная в какой-либо ячейке, помещает в нее значение квадратного корня из числа, находящегося в ячейке А1, при его неотрицательном значении, и выдает предупредительный текст в случае отрицательного значения. Сравнивать можно как арифметические, так и текстовые выражения. Если после набора формулы в одном из полей (рис. 1.17), она вдруг окажется охваченной двойными кавычками, т. е. ин- терпретирована как текст, — ищите ошибку. Это может быть несоответствие скобок, неверно записанное или использованное имя функции, адрес ячейки, содержащий русские буквы, пропу- щенный знак умножения и т. п. 1 0 , 3 ) ( sin 2 0 , 1 1 2 4 2 x x x x x x x y 2 0 , ) 5 ln( 1 3 0 ), ( cos ) sin( 3 2 2 x x x x x x y 3 0 , 3 ) ( sin 2 0 , ) ( cos 1 ) 2 ( sin 3 2 2 2 x x x x x x x y 4 0 , 2 1 0 , 1 3 2 5 , 0 2 2 x x e x x x x y x 5 0 ), ( cos 2 0 , 1 ) ( sin 3 2 2 2 2 x x x x x x y 6 0 , 2 2 0 , ) ( sin 2 1 3 1 , 0 2 2 x e x x x x y x 7 0 , 1 1 1 0 , 1 3 2 , 0 2 x e x x x y x 8 0 , 2 1 3 1 0 , 1 3 x x x x x y 9 1 , )) ( cos 1 ( 1 ), 1 ln( 2 5 3 2 2 x x x x y 10 0 , 1 1 ) ( sin 0 , 1 2 3 2 x e x x x x y x 40 11 1 , 3 ) ( cos 1 1 , 1 1 4 3 2 x x x x x x x y 12 0 , ) ( cos 2 1 0 , 2 1 3 x x x x x x y 13 0 , 2 0 , 1 1 2 3 2 x e x x x x y x 14 0 , ) cos( 2 0 , 1 3 2 2 x e x x x x y x 15 0 , ) cos( / 2 0 , 1 1 2 2 x x x x x y 16 0 , 3 2 0 , 3 1 x x x x x x y 17 0 , 3 ) cos( 1 0 , 1 1 2 x x x x x x y 18 0 , 1 ) sin( 2 1 0 , 1 1 2 2 2 x x x x x x x y 19 0 , )) sin( 1 ( 1 0 , 1 3 1 2 2 x x x x x y 20 0 , 1 ) ( sin 0 , 1 2 1 2 2 x x x x x x y |