Лекция по информатике на тему: Автоматизированная обработка числовой информации. ЭТ

Лекция  № 8. Тема   занятия:  Тема   3.1.   Автоматизированная   обработка   числовой   информации. Электронные таблицы. Типы данных. Цели   занятия:   Обучающая:  Рассказать   студентам   о   различных   программах   для   обработки числовой   информации.   Дать   основные   понятия:   электронная   таблица,   ячейка,   типы   данных, абсолютные   и   относительные   ссылки.  Развивающая:  Развивать   знания   студентов   в   области информационных   технологий.  Воспитательная:  Показать   важность   знаний   и   умений   в   сфере информационных   компьютерных   технологий,   а   именно   технологий   обработки   текстовой информации. Информация ­ (от латинского слова Informatio разъяснение, изложение). Первоначальные – сведения,   передаваемые   одними   людьми   другим   людям   устным,   письменным   или   каким­либо другим способом (например, с помощью условных сигналов, с использованием технических средств и т. д.), а также сам процесс передачи или получения этих сведений. Вся информация, поступающая к человеку, состоит из сигналов. Человек эти сигналы получает, обрабатывает и либо исполняет, либо запоминает.   Воздействие средств информации на органы чувств. Человек так устроен, что он защищается от ненужной, непонятной и неприятной  информации. Она проходит мимо него. В этом случае человек не обрабатывает её, а значит, не  может запомнить и превратить в знание. Та информация, которая не может быть понята и усвоена, называется ­  информационным шумом. Вывод: 1. Человеку трудно потреблять информацию. Он может делать это только очень маленькими порциями. Любая перегрузка превращается в информационный шум, и. она становиться  бесполезной, то есть не превращается в знания. 2. Человеку трудно обработать информацию. От этого он устает. 3. Человек можем, ошибиться. Из­за информационного шума он можем неправильно  обработать информацию и превратить её ложное знание. 4. Человек необъективен (т.е. воспринимает информацию не такой, какой она есть, а такой,  какой она ему кажется). Если информация совпадает с его личным мнением, он принимает,  обрабатывает и усваиваем её очень легко. Если информация ему неприятна, он усваивает ее с  большим трудом и многое остается без внимания. 5. Человек не может долго хранить информацию. Если не закреплять знания постоянными  упражнениями, информация очень быстро забывается. Что же такое компьютер? Компьютер ­ это электронная машина, которая может:     Как было ранее сказано, этими функциями обладает и человек. Однако делает он это  Принимать информацию; Обрабатывать информацию; Хранить информацию; Выдавать информацию. медленно, иногда с ошибками и не всегда охотно. Компьютер освобождает нас от необходимости  обрабатывать горы информации, но делает он быстро, безотказно, выдает в том виде, в котором  удобно человеку, и хранит сколь угодно долго. Выполнения всех выше изложенных функций рассмотрим на примере компьютерной игры:   Загрузка программы игры в память ­ функция хранения: Диалог   компьютерной   программы   с   человеком   побуждающей   к   нажатию   клавиши клавиатуры, джойстика или мыши ­ функция приема информации: Анализ положения курсора относительно объекта на картинке монитора и принятие   решения к действию ­ функция обработки информации Вывод события на экран монитора ­ функция выдача информации Вывод: С точки зрения компьютера нет никакой разницы, чем заниматься. Он может составлять  расписание школьных занятий, или расписания движения поездов. Может управлять работой  большого цеха, а может управлять движением какого либо объекта в игре. Во всех случаях  компьютер ­ это машина, которая делает одно и то же дело: получает, обрабатывает, сохраняет и  выдает информацию. Виды компьютерной информации Как ранее было сказано, человек имеет дело со многими видами информации. Рассмотрим,  какую информацию компьютер, по сравнения с человеком, не может принять, поэтому, обработать,  хранить и выдавать. ­ Так, ввести в компьютер запах розы, вкус яблока или мягкость плюшевой игрушки ­ нельзя  никак. Ранее говорилось, что компьютер это электронная машина, а значит, он работает с  сигналами. Поэтому компьютер может работать только с той информацией, которую можно  представить в виде сигнала. Если бы можно было представить вкус, запах в виде сигнала, то  компьютер ног бы работать и с такой информацией, но делать этого пока не научились. Надо отметить, что хорошо превращается в сигналы то, что мы видим. Для этой цели  используют специальные электронные устройства: видеокамеры, цифровые фотоаппараты, сканеры. Давно научились превращать в сигналы то, что мы слышим. Делают это с помощью  микрофона. Очень трудно превращать в сигналы то, что человек чувствует с помощью обоняния,  осязания и вкуса. Ученые ещё не нашли таких способов. Значит, компьютеры с такой информацией  работать, пока, не могут. Вывод:  Компьютер может, работать только с той информацией, которую мы видим и слышим. Пять видов компьютерной информации Современные компьютеры могут работать с пятью видами информации: 1. 2. 3. 4. 5. Все эти пять видов информации вместе называют одним словом: ­ мультимедиа. Если компьютер может работать со всеми этими пятью видами информации, то его  Числовой информацией (числа); Текстовой информацией (буквы, слова, предложения, тексты); Графической информацией (картинки, рисунки, чертежи); Звуковой информацией (музыка, речь, звуки); Видеоинформацией (видеофильмы, мультфильмы, кинофильмы). называют мультимедийным. Если компьютерная программа использует все эти виды информации, то её называют  мультимедийной. Числовая информация Для передачи информации на большое расстояние по проводам сто лет начал человек  изобрел телеграф. Нашелся способ превращения чисел и букв в сигналы ­ специальная телеграфная  азбука (Азбука Морзе). Короткий сигнал «точка». Длинный сигнал ­ «тире». Для компьютеров азбука Морзе не пригодна, так как очень неудобно разбираться с тем,  какой сигнал длинный, а какой короткий. Придумали более простые сигналы: если есть сигнал, то  это единица. Если нет ­ нолик. Осталось научиться представлять числа в виде единиц и ноликов.  Компьютер делает гак: 0 – 0 1 – 1 2 – 10 3 – 11 4 – 100 5 – 101 6 – 110 (ноль) (один) (ноль ­ один) (один ­ один) (один ­ ноль ­ ноль) (один ­ ноль ­ один) (один ­ один ­ ноль) 2 7 – 111 8 – 1000 9 – 1001 10 – 1010 Если необходимо перевести число 1999 в сигналы (двоичный код) то компьютер сам  (один ­ один ­ один) (один ­ ноль ­ ноль ­ ноль) (один ­ ноль ­ ноль ­ один) (один ­ ноль ­ один ­ ноль) способен перевести его. 1998­11111001110 1999­11111001111 2000­ 11111010000 Минимальное число представления информации ­ (ноль и один) – называют битами. Группа  из восьми битов ­ байтами. Их четырех ­ полубайт. В один байт можно записать число от 0 до 255. Для записи числа 1998 необходимо  воспользоваться вторым байтом. В двух байтах можно записать число ­ от 0 до 65535. В трех ­ от 0 до 16 миллионов. Текстовая информация Каждой букве присваивается числовой номер. Например ­ букве «А» число 1, а букве «Б» ­  2. Надо сказать, что прописные и заглавные буквы имеют разное число. В том числе, русский  алфавит и латинский имеют свою кодировку. Для того чтобы различные компьютеры понимали  друг ­ друга ученые выработали единый стандарт представления букв числами и назвали его  «Кодировкой символов» «КОИ» (Рис.  0 .1). Рис. 0.1. Кодировка символов Превратив буквы в числа, компьютер превращает числа в сигналы, и записывает их битами,  из которых собираются байты: А ­ 192­ 11000000 Б ­ 193 ­ 11000001 В ­ 194­ 11000010 Г­ 195­ 11000011 Д ­ 196 – 11000100 и так далее. Графическая информация Компьютеры могут работать с графической информацией. Это могут быть рисунки или  фотографии. Для того чтобы картинка могла храниться и обрабатываться в компьютере, ей  превращают в сигналы. Такое превращение называют оцифровкой (Рис.  0 .2). Для оцифровки графической информации служат специальные цифровые фотокамеры или  специальные устройства – сканеры. 3 Рис. 0.2 Пример оцифровки рисунка Цифровая камера работает, как обычный фотоаппарат, только изображение не попадает на  фотопленку, а «запоминается» в электронной памяти такого «фотоаппарата». Потом такой аппарат подключают к компьютеру и по проводу передают сигналы, которыми зашифровано изображение. Если картинка сделана на бумаге, то для того, чтобы превратить её в сигналы, используют  сканеры. Картинку кладут в сканер. Сканер просматривает каждую точку этой картинки и  передает в компьютер числа (байты), которыми зашифрован цвет каждой точки. Например:  Черная точка: 0, 0, 0; Белая точка: 255, 255, 255; Коричневая точка:153, 102, 51; Светло­серая точка: 160, 160, 160; Темно­серая точка: 80, 80, 80. У каждого цвета свой шифр (его называют цветовым кодом). Если каждый цвет передавать тремя байтами, то можно зашифровать более 16 миллионов  цветов. Это гораздо больше, чем может различить человеческий глаз, но для компьютера это не  предел. Звуковая информация Звук, музыка и человеческая речь поступает в компьютер в виде сигналов и тоже  оцифровывается (Рис.  0 .3. Рис.  0 .4.), то есть превращается в числа, а потом ­ в байты и биты.  Компьютер их хранит, обрабатывает и может воспроизвести (проиграть музыку или произнести  слово). Рис. 0.3 4 Для того чтобы ввести звуковую информацию в компьютер, к нему подключают микрофон  или соединяют с другими электронными музыкальными устройствами, например, с магнитофоном  или проигрывателем. Если в компьютере есть специальная, звуковая плата, то он может  обрабатывать звуковую информации и воспроизводить человеческую речь, музыку и звуки. Рис. 0.4 Видеоинформация Современные компьютеры могут работать с видеоинформацией. Они могут записывать и  воспроизводить видеофильмы, мультфильмы и кинофильмы. Как и все прочие виды информации,  видеоинформация тоже превращается в сигналы и записывается в виде битов и байтов. Происходит это точно так же, как и с картинками ­ разница лишь в том, что таких «картинок» надо  обрабатывать очень много. Фильмы состоят из кадров. Каждый кадр ­ эго как бы отдельная картинка. Чтобы  изображение на экране, выглядело «живой» и двигалось, кадры должны сменять друг друга с  большой скоростью ­ 25 кадров в секунду. Если компьютер мощный и быстрый, то он может 25 раз  в секунду обрабатывать в своей памяти новую картинку и показывать её на экране. Сигналы для записи видеоизображений компьютер получает от видеокамеры. Как и все  другие виды информации, он преобразует эти сигналы в биты и байты и записывает их в свою  память. Выводится видеоизображение на экран компьютерного монитора. При этом вместе с  изображением может выводиться и звук. Вопросы для повторения 1. Понятие: информация и информатика. 2. Воздействие средств информации на органы чувств человека. 3. Виды компьютерной информации. Дать их понятие и способы представления в ПК. Современные   технологии   обработки   информации   часто   приводят   к   тому,   что   возникает необходимость   представления   данных   в   виде   таблиц.   В   языках   программирования   для   такого представления служат двухмерные массивы. Для табличных расчетов характерны относительно простые формулы, по которым производятся вычисления, и большие объемы исходных данных. Такого рода расчеты принято относить к разряду рутинных работ, для их выполнения следует использовать   компьютер.   Для   этих   целей   созданы электронные   таблицы   (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме. 5 Электронная   таблица   (ЭТ) позволяет   хранить   в   табличной   форме   большое   количество исходных   данных,   результатов,   а   также   связей   (алгебраических   или   логических   соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся  в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным   средством   моделирования   различных   вариантов   и   ситуаций.   Меняя   значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый. При работе с табличными процессорами создаются документы, которые также называют электронными таблицами. Такие таблицы можно просматривать, изменять, записывать на носители внешней памяти для хранения, распечатывать на принтере. Таблица – форма организации данных по строкам и столбцам.  Электронная таблица – компьютерный эквивалент обычной таблицы.  Табличный процессор – комплекс программ, предназначенных для создания и обработки  электронных таблиц.  Электронная   таблица –   самая   распространенная   и   мощная   технология   для профессиональной работы с данными. В ячейках таблицы могут быть записаны данные различных типов: текст, даты, числа, формулы и др. Главное достоинство электронной таблицы – возможность мгновенного автоматического пересчета всех данных, связанных формульными зависимостями, при изменении значения любого компонента таблицы. Табличный процессор MS Excel позволяет:  1. Решать математические задачи: выполнять разнообразные табличные вычисления, вычислять значения функций, строить графики и диаграммы и т.п.; 2. Осуществлять численное исследование (Что будет, если? Как сделать, чтобы?); 3. Проводить статистический анализ; 4. Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных; 5. Устанавливать защиту на отдельные фрагменты таблицы, делать их невидимыми; 6. Наглядно представлять данные в виде диаграмм и графиков; 7. Вводить и редактировать тексты; 8. Осуществлять обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях; 9. Осуществлять многотабличные связи. 6 Основные объекты табличного процессора MS Excel:  Ячейка – минимальный объект табличного процессора;  Строка – горизонтальный набор ячеек, заголовки столбцов – A, B, C,…,IV;  Столбец – вертикальны набор ячеек, заголовки строк – 1, 2, 3,…65536;  Адрес ячейки – определяется пересечением столбца и строки (A1, F123, AC72);  Указатель ячейки – рамка;  Активная ячейка – выделенная рамкой, с ней можно производить какие­либо операции;  Смежные ячейки – ячейки расположенные последовательно;  Диапазон (блок) ячеек – выделенные смежные ячейки, образующие прямоугольный участок таблицы;  Адрес диапазона (блока) ячеек ­ определяется адресом верхней левой и нижней   B2, B3, B4, B5, B6, B7, C2, C3, C4, C5,  → правой ячейки, разделенных двоеточием (:), B2: C7  C6, C7.  Книга – документ электронной таблицы, состоящий из листов, объединенных одним именем и являющихся файлом;  Лист – рабочее поле, состоящее из ячеек. При   работе   с   табличными   процессорами   создаются   документы,   которые   можно просматривать, изменять, записывать на носители внешней памяти для хранения, распечатывать на принтере. Режим формирования электронных таблиц предполагает заполнение и редактирование документа.   При   этом   используются   команды,   изменяющие   содержимое   клеток   (очистить, 7 редактировать,   копировать),   и   команды,   изменяющие   структуру   таблицы   (удалить,   вставить, переместить). Режим управления вычислениями. Все вычисления начинаются с ячейки, расположенной на пересечении первой строки и первого столбца электронной таблицы. Вычисления проводятся в естественном порядке, т.е. если в очередной ячейке находится формула, включающая адрес еще не вычисленной ячейки, то вычисления по этой формуле откладываются до тех пор, пока значение в ячейке, от которого зависит формула, не будет определено. При каждом вводе нового значения в ячейку   документ   пересчитывается   заново,   —   выполняется   автоматический   пересчет.   В большинстве табличных процессоров существует возможность установки ручного пересчета, т.е. таблица пересчитывается заново только при подаче специальной команды. Режим отображения формул задает индикацию содержимого клеток на экране. Обычно этот   режим   выключен,   и   на   экране   отображаются   значения,   вычисленные   на   основании содержимого клеток. Графический режим дает возможность отображать числовую информацию в графическом виде: диаграммы и графики. Это позволяет считать электронные таблицы полезным инструментом автоматизации инженерной, административной и научной деятельности. В современных табличных процессорах, например, в Microsoft Excel, в качестве базы данных  можно использовать список (набор строк таблицы, содержащий связанные данные). При  выполнении обычных операций с данными, например, при поиске, сортировке или обработке  данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы  списков учитываются при организации данных: •  столбцы списков становятся полями базы данных;  •  заголовки столбцов становятся именами полей базы данных;  •  каждая строка списка преобразуется в запись данных. «Типы данных в ячейках электронной таблицы.  Правила записи арифметических операций» Ячейки рабочего листа электронной таблицы могут содержать:   исходные или первичные данные – константы; производные данные, которые рассчитываются с помощью формул или функций. Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, даты, формулы и функции. Текст – последовательность букв, иногда цифр или некоторых специальных символов. Числа могут включать цифры и различные символы: знак процента, знак мантиссы, круглые скобки, денежные обозначения, разделители и др. Например: 5; 3,14. Дата   и   время вводятся   в   ячейки   электронной   таблицы   как   числа   и   выравниваются   по правому краю. Формулой в   электронной   таблице   называют   алгебраические   и   логические   выражения. Формулы   всегда   начинаются   со   знака   равенства   (=)   и   вводятся   в   латинском   регистре. Например: =А5*2/В1 Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов. Функции могут вводиться в таблицу в составе формул либо отдельно. Например, функция суммирования имеет вид =СУММ(А1:А2) Аргументами функции могут быть: числа; ссылки на ячейки и диапазоны ячеек; имена; текст; другие функции; логические значения и др. MS   Excel   содержит   более   400   встроенных   функций.   Имена   функций   можно   набирать   в любом   регистре   –   верхнем   или   нижнем.   Для   облегчения   работы   с   встроенными   функциями используется Мастер функций. 8 В   формулах   используются   следующие   знаки арифметических операций: сложение + вычитание – умножение * деление / возведение в степень ^ Для   изменения   порядка   арифметических   действий используются круглые скобки. «Форматирование элементов таблицы. Формат числа» Форматированием называется   изменение   внешнего   оформления   таблиц   и   данных   в   них. Важно! Excel различает форматирование всей ячейки и форматирование содержимого ячейки. К форматированию   ячеек относится:   изменение   шрифта   содержимого   ячеек,   выравнивание данных в ячейках, представление чисел в разных форматах, оформление границ ячеек, и т.д. Для того чтобы изменить формат ячейки необходимо щелкнуть на ней и выполнить команду Формат —Ячейки.   Появившееся   диалоговое   окно Формат   Ячеек, позволит   изменить   формат   всей ячейки. Для выравнивания данных следует:  щелкнуть на форматируемой ячейке или выделить диапазон ячеек, содержащих  выравниваемый текст;  выполнить команду Формат—Ячейки. На закладке Выравнивание установить  опцию Переносить по словам, которая позволяет располагать текст одной ячейки в  несколько строк. Для оформления предварительно выделенных ячеек с помощью рамок следует выполнить команду Формат—Ячейки. На закладке Границаследует выбрать тип линии для рамки, а затем щелкнуть на кнопке с нужным видом рамки. (Возможно поочередное назначение нескольких видов рамок).  Для   назначения   цветного   фона предварительно   выделенным   ячейкам   необходимо выполнить   команду Формат—Ячейки. На   закладке Видвыбрать   цвет   фона   ячеек   и   нажать кнопку ОК.  Для   изменения   шрифта   предварительно   выделенного текста   или   числа, 9 находящихся   в   нескольких   ячейках,   необходимо   выполнить   команду Формат—Ячейки. На закладке Шрифт следует   указать   тип   шрифта   (Arial,   Times   New   Roman   и   т.   д.),   начертание (жирный, курсив, подчеркнутый), размер шрифта, цвет и т.д.  Для изменения формата чисел, находящихся   в   выделенном   диапазоне   ячеек   необходимо   выполнить   команду Формат— Ячейки. С   помощью   закладки Число можно   установить   один   из   следующих   форматов   чисел: денежный, процентный, дата, текстовый и т.д. Представление числа 100 в разных форматах Пояснения Общий 100 Без   использования   специальных средств. Текст выравнивается по левому краю, число по правому. Числовой с двумя знаками после запятой 100,00 Денежный (в рублях) Денежный (в долларах) 100,00 р. $100,00 Дата 09.04.1900 Процентный Экспоненциальный 10000,0% 1,00Е+02   строке Числовой   формат   позволяет отображать   любое   количество знаков   после   запятой   с соблюдением правил округления Знак  денежной единицы («р.» и «) отображается только в ячейке, в   редактирования (формул) он отсутствует. MS   Excel   хранит   даты   в   виде последовательных   чисел.   По умолчанию   дате   1   января   1900 года   соответствует   порядковый номер 1, а 1 января 2008 года – 39448. В   процентном   формате   число умножается на 100% E+02   означает   10   во   2­ой степени 10 Текстовый 100 Текстовый формат используется для ввода чисел начинающихся с 0. «Ошибки в формулах в Microsoft Excel» При проведении расчетов в электронной таблице Microsoft Excel иногда в ячейках вместо ожидаемых   значений   выводятся   различные   «непонятные»   результаты.   Сегодня   мы   попытаемся сделать их для нас понятными. Ячейки заполнены знаками # # # # # # # # # # Ячейка может заполняться набором символов «решетки» (#) по одной из следующих причин: 1. ширина столбца недостаточна для размещения численного значения, для которого установлен   формат   Числовой,   Денежный   или   Финансовый.   Такая   ситуация   встречается, например,   в   ячейке,   в  которую  скопирована   формула   из   другой   ячейки,   для   которой   был установлен   один   из   перечисленных   форматов.   Для   исправления   ошибки   необходимо   либо расширить ячейку, либо изменить формат представления данных в ней; 2. ячейка содержит формулу, возвращающую  некорректную дату или время. Excel, например,   не   поддерживает   даты   до   1900   года   или   отрицательные   значения   времени. Увеличение ширины ячейки данную ошибку не исправляет. Ошибка #ДЕЛ/0! О том, что делить на ноль нельзя, знают даже учащиеся начальных классов. Но тем не менее такая   ситуация   в   Excel   встречается   довольно   часто.   Например,   на   листе,   подготовленном   для решения линейного уравнения вида ax + b = 0: — формула в ячейке B4, имеющая вид =–B3/B2, вернет значение ошибки #ДЕЛ/0! в ситуации,  когда значение коэффициента а в ячейке B2 еще не задано. Для исключения вывода ошибки можно  воспользоваться функцией ЕСЛИ, которая проверяет наличие «неправильного» значения в ячейке  B2: =ЕСЛИ(B2=0; ««; –B3/B2)  Данная формула выводит «пустое» значение (««), если ячейка пустая или содержит 0; в противном  случае выводится вычисленное значение корня уравнения. Другое решение заключается в использовании функции ЕСЛИ для проверки  существования любой ошибки. Следующая формула также отображает «пустое» значение в случае  получения ошибки любого типа: =ЕСЛИ(ЕОШИБКА(–B3/B2); ««; –B3/B2)  С особенностями функции ЕОШИБКА ознакомьтесь самостоятельно. Ошибка Имя? Как известно, в Excel можно использовать в формулах не только адреса отдельных ячеек или диапазонов, но и их имена. Это облегчает понимание формул. Например, вы согласитесь,   что   формула   нахождения   корня   линейного   уравнения,   оформленная   в   виде: =ЕСЛИ(a=0; ««; –b/a) — где a и b — имена ячеек, в которых задаются значения коэффициентов уравнения a и b соответственно, выглядит гораздо понятнее приведенного ранее варианта. Ошибка Имя? возникает в следующих случаях: 1. формула содержит неопределенное имя ячейки или диапазона. Здесь же следует отметить одну особенность Excel. Если удалить имя какой­то ячейки или какого­то диапазона, используемое   в   формуле,   то   в   самой   формуле   оно   останется   (казалось   бы,   Excel   мог   бы преобразовать   имена   в   ссылки   на   соответствующие   ячейки,   однако   этого   почему­то   не происходит); 11 2. формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?. Ошибка #Знач! Ошибка весьма распространена и встречается, как правило, в следующих ситуациях. 1. Формула пытается провести операцию, используя некорректные данные (например, пытается сложить число и текстовое значение). Если на листе в ячейке B7 (см. фрагмент листа ниже)   была   записана   формула   =B3+B4+B5+B6,   которая   затем   была   распространена (скопирована)   на   ячейки   C7   и   D7,   то   для   приведенных   исходных   данных   в   ячейке   будет выведен результат #Знач! (ясно, что складывать числа и текст нельзя). Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7. 2. В   качестве   аргументов   функции   использованы   данные   несоответствующего   типа (например, в функции ЕСЛИ вместо условия использовано число или текст). 3. В   качестве   аргумента   функции   используется   диапазон,   тогда   как   аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6). Ошибка #Число! Ошибка #Число! возникает в одном из двух случаев: 1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);  2)   формула   содержит   слишком   большое   или   слишком   маленькое   значение.   Excel поддерживает величины в пределах от 1Е­307 до 1Е307 (от 10­307 до 10307). Ошибка #Ссылка! Ошибка   #Ссылка!   возникает   в   случае   использования   формулой   ошибочной   ссылки   на ячейку. Данная ошибка может встречаться в следующих ситуациях. 1. 1. Ячейка, на которую ссылалась формула, была удалена. Например, следующая формула  отображает ошибку #Число!, если  удалена  строка 20, столбец A или столбец  B: =A20/B20 2. Формула   скопирована   в   новое   место,   где   относительные   ссылки   на   ячейки становятся недействительными. Например, если формулу =A1–1 в ячейке A2 скопировать в ячейку A1, формула вернет ошибку #Число!, так как в ней будет присутствовать ссылка на несуществующую ячейку. 3. Содержимое ячейки с формулой было вырезано (командой Правка — Вырезать) и затем вставлено в ячейку, на которую ссылается формула. В заключение заметим, что в случаях, когда результатом формулы является ошибка любого из   рассмотренных   типов,   а   ширина   столбца   недостаточна   для   размещения   соответствующего сообщения, ячейка заполняется знаками «#». Основные типы данных: ∙ числа, как в обычном, так и экспоненциальном формате, ∙ текст – последовательность символов, состоящая из букв, цифр и пробелов, 12 ∙ формулы. Формулы должны начинаться со знака равенства, и могут включать в себя числа,  имена ячеек, функции (математические, статистические, финансовые, текстовые, дата и  время и т.д.) и знаки математических операций. Электронные таблицы просты в обращении, быстро осваиваются непрофессиональными  пользователями компьютера и во много раз упрощают и ускоряют работу бухгалтеров,  экономистов, ученых. 13 Практическое занятие  № 8. Тема занятия:  Форматирование ячеек и условное форматирование. Стандартные функции. Построение диаграмм. Цели   занятия:   Обучающая:  познакомить   учащихся   с   методикой   построения   деловой графики в ЭТ; научить правильно указывать все необходимые параметры при построении графиков функций   и   диаграмм,.  Развивающая:  использовать   графические   интерпретации   для   анализа данных; развивать умения и навыки обработки табличных данных, логическое мышление, память и аналитические   способности;   воспитывать   чувство   меры   и   такта,   проводить   профориентацию. Воспитательная: воспитывать добросовестное отношение к труду, инициативность, уверенность в своих силах. Вариант 1 1. Запустить на выполнение программу Microsoft Excel Задание 1 2. На Листе 1 составить таблицу значений функции у = х +2, для значений аргумента от ­5 до 5 Х ­4 ­3 ­5 ­2 ­1 0 1 2 3 4 5 У   Составить формулы для вычисления значения функции  Построить график данной функции, поместив график на отдельный лист.  Переименовать Лист 1  ­ График 1  Переименовать лист Диаграмма – График 1 Задание 2 3. Перейти на Лист 2  4. Создать таблицу значений функции    для значений аргумента от ­5 до 5  õ ó 12  3 5. Составить формулы для вычисления значения функции 6. Построить график данной функции, выбрав тип – лепестковая. 7. Поместить график на отдельный лист. 8. Форматирование области построения диаграммы выполнить на свое усмотрение 9. Переименовать Лист 2  ­ График 2 10. Переименовать лист Диаграмма – График 2 11. Сохранить работу в папке своего класса на рабочем столе, указав в имени документа свою фамилию. Задание 3 12. Перейти на Лист 3  13. Создать таблицу значений функции   , для значений аргумента от 0 до 10 ó 2 õ 14. Составить формулы для вычисления значения функции 15. Построить график данной функции, выбрав тип – лепестковая 14 16. Поместить график на отдельный лист. 17. Форматирование области построения диаграммы выполнить на свое усмотрение 18. Переименовать Лист 3  ­ График 3 19. Переименовать лист Диаграмма – График 3.1 20. Построить график этой же функции, выбрав тип – график 21. Поместить график на отдельный лист 22. Форматирование области построения диаграммы выполнить на свое усмотрение 23. Переименовать лист Диаграмма – График 3.2 24. Сохранить работу в папке своего класса на рабочем столе, указав в имени документа свою фамилию. Вариант 2 1. Запустить на выполнение программу Microsoft Excel Задание 1 2. На Листе 1 составить таблицу значений функции  ó  2 2 õ  3 õ  4 , для значений аргумента от ­6 до 6  Составить формулы для вычисления значения функции  Построить график данной функции, поместив график на отдельный лист.  Переименовать Лист 1  ­ График 1  Переименовать лист Диаграмма – График 1 Задание 2 3. Перейти на Лист 2  4. Создать таблицу значений функции   , для значений аргумента от ­10 до 10 ó  ( x Sin xCos (  )2  )1 5. Составить формулы для вычисления значения функции 6. Построить график данной функции, выбрав тип – гладкие графики 7. Поместить график на отдельный лист. 8. Форматирование области построения диаграммы выполнить на свое усмотрение 9. Переименовать Лист 2  ­ График 2 10. Переименовать лист Диаграмма – График 2 Задание 3 11. Перейти на Лист 2  12. Создать таблицу значений функции   , для значений аргумента от ­10 до 10 ó   )2 x ( Sin  Cos ( x )1 13. Составить формулы для вычисления значения функции 14. Построить график данной функции, выбрав тип – гладкие графики 15. Поместить график на отдельный лист. 16. Форматирование области построения диаграммы выполнить на свое усмотрение 17. Переименовать Лист 2  ­ График 2 18. Переименовать лист Диаграмма – График 2 19. Сохранить работу в папке своего класса на рабочем столе, указав в имени документа свою фамилию. 15 Вариант 3 1.   Запустить на выполнение программу Microsoft Excel Задание 1 2. Составить ЭТ, отражающую динамику общих, средних и предельных издержек фирмы в краткосрочном периоде. Исходные данные (дол).  Общее кол ­ во продукции (Q) Сумма постоянных Сумма переменных издержек (ТFС) издержек (ТVС) 1 2 3 4 5 6 7 8 9 10 100,0 120,25 100,36 30,5 25,45 87,25 102,23 65,24 236,12 0,25 80,0 0,25 12,25 147,25 32,25 12,65 45,3 87,20 95,14 254,2 3. Рассчитываются следующие величины:   сумма общих издержек (ТС=ТFС+ТVС);  средние постоянные издержки (АFС=ТFС/Q);  средние переменные издержки (АVС=ТVС/Q);  средние общие издержки (АТС=ТС/Q).  Рассчитывается  величина предельных издержек по формуле: МС = изменения в ТС / изменения в Q. 4.   Построить   график,   отражающий   зависимость   средних   общих   издержек   и   предельных издержек от выпуска продукции. 5. Поместить график на отдельный лист. 6. Форматирование области построения диаграммы выполнить на свое усмотрение 7. Переименовать Лист 1  ­ График 1 Задание 2 8. Перейти на Лист 2  9.   Построить полусферу изометрической проекции по формуле х2+y2+z2= 25 10. Поместить график на отдельный лист. 11. Форматирование области построения диаграммы выполнить на свое усмотрение 12. Переименовать Лист 2  ­ График 2 16 13. Переименовать лист Диаграмма – Сфера 14. Сохранить работу в папке своего класса на рабочем столе, указав в имени документа свою фамилию. 17 Методические указания: 1. В нижней части экрана горизонтальная полоса прокрутки состоит из двух частей. Левая часть служит для перемещения по таблице (вправо, влево), а правая часть, содержащая ярлычки листов, позволяет перемещаться между листами.  2.  По  умолчанию  рабочая  книга  открывается  с  3­мя  листами,   имена   которых   Лист1,…, Лист3. Имена листов выведены на ярлычках в нижней части окна рабочей книги.  3. Щелкая по ярлычкам, можно переходить от листа к листу внутри рабочей книги.  4. Ярлычок активного листа выделяется цветом, к надписи на нем применен полужирный стиль.  5. Для выбора конкретного листа достаточно щелкнуть по его ярлычку мышью. Технология работы: Составим   ведомость   на   выдачу   заработной   платы.   Для   выполнения   упражнения   нам понадобятся только четыре листа:  ∙ на первом разместим сведения о начислениях;  100 ∙ на втором – диаграмму;  ∙ на третьем – ведомость на выдачу заработной платы;  ∙ на четвертом ­ ведомость на выдачу компенсаций на детей.  В книге всего 3 книги, поэтому четвертый лист нужно добавить. Для этого:  ∙ Щелкнуть правой кнопкой мыши по ярлычку Лист3, в появившемся контекстном меню выбрать   команду   Добавить   ­   Лист,   или   воспользуйтесь   командой   Вставка   ­   Лист.   Теперь выглядывают ярлычки четырех листов. Перейти на лист 1, щелкнув по его ярлычку. На нем начнем создавать таблицу.  Задание 1. Создание таблицы (проверочная работа)  1. Создайте заготовки таблицы самостоятельно, применяя следующие операции:  ∙ запуск Excel;  ∙ форматирование строк заголовка. Заголовок размещен в двух строках таблицы, применен полужирный стиль начертания шрифта, весь текст выровнен по центру, а «Налоги» – по центру выделения; ∙    изменение ширины столбца (в зависимости от объема вводимой информации);  ∙ обрамление  таблицы.  В данном случае  использовано  сложное  обрамление,  когда  снята часть рамок. Важно по предложенному образцу определить реальное положение ячеек и выполнить соответствующее обрамление, выделяя различные блоки ячеек;  ∙ задание формата числа «денежный» для ячеек, содержащих суммы. Можно сделать это до ввода данных в таблицу (выделить соответствующие ячейки и установить для них формат числа «денежный»);  ∙ заполнение ячеек столбца последовательностью чисел 1, 2, …;  101 ∙ ввод формулы в верхнюю ячейку столбца;  ∙ распространение формулы вниз по столбцу и в некоторых случаях вправо по ряду;  ∙ заполнение таблицы текстовой и фиксированной числовой информацией (столбцы «ФИО», «Оклад», «Число детей»);  ∙ сортировка строк (сначала отсортировать по фамилиям по алфавиту, затем отсортировать по суммам).  2. Для форматирования формул вам понадобится дополнительная информация.  18 ∙ Примем профсоюзный и пенсионный налоги, составляющими по 1 % от оклада. Нужно ввести   формулу   в   одну   ячейку,   а   затем   распространить   ее   на   оба   столбца.   Не   забудьте   про использование абсолютных ссылок, т.к. профсоюзный и пенсионный налоги нужно брать от оклада, т.е. ссылаться только на столбец «Оклад».  Примерный вид формулы: =$С5*1% или =$С5*0,01 или =$С5*1/100. После ввода формулы в ячейку D5 ее нужно распространить вниз (протянув за маркер выделения), а затем вправо на один столбец.  ∙ Подоходный налог подсчитаем по формуле: 13 % от Оклада за вычетом минимальной заработной платы1  и пенсионного налога.  Примерный вид формулы: =(С5­Е5­Мин_зарплата)*13% или =(С5­Е5­Мин_зарплата)*13/100 или =(С5­Е5­Мин_зарплата)*0,13. После ввода формулы в ячейку F5 ее нужно распространить вниз.  ∙   Для   подсчета   Суммы   к   выдаче   примените   формулу,   вычисляющую   разность   оклада   и налогов. Примерный вид формулы: =C5­D5­E5­F5, размещенной в ячейке G5 и распространенной вниз.  3. Заполняйте столбцы «Фамилия, имя, отчество», «Оклад» и «Число детей» после того, как введены все формулы. результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы.  4.   После   ввода   всех   данных   желательно   выполнить   их   сортировку   (не   забудьте   перед сортировкой выделить все строки от фамилий до сведений о детях).  В окончательном виде таблица будет соответствовать образцу: 5. Можно ввести строку для подсчета общей суммы начислений.  6. Переименуем активный в данный момент лист. Для этого выполните команду Формат – Лист   –   Переименовать   и   в   поле   ввода   Имя   листа   введите   новое   название   листа,   например, «Начисления».  Задание 2. Построение диаграммы на основе готовой таблицы и размещение ее на новом листе рабочей книги  1. Построим диаграмму, отражающую начисления каждого сотрудника, и расположим ее на втором листе.  ∙ Выделить два столбца таблицы: «Фамилия, имя, отчество» и «Сумма к выдаче». (При нажатой клавише Ctrl можно одновременно выделять ячейки в разных местах таблицы).  ∙ Запустите Мастер диаграмм одним из двух способов: выбрав кнопку Мастер диаграмм на панели инструментов, либо команду меню Вставка­ Диаграмма.  ∙   Передвигаясь   по   шагам   с   мастером   диаграмм,   выберите   тип   диаграммы   –   разрезная круговая   (Отображает   вклад   каждого   значения   в   общую   сумму,   выделяя   отдельные   значения). Нажмите кнопку Далее.  ∙ На 4 шаге диаграммы выбрать место для размещения диаграммы – На имеющемся, из списка   выбрать   Лист2.   Если   выбрать   команду   На   отдельном,   то   для   диаграммы   в   момент   ее создания будет вставлен новый лист рабочей книги с названием Диаграмма1.  2. Переименуйте лист, содержащий диаграмму из «Лист 2» в «Диаграмму».  19 3.   Для   того,   чтобы   проверить,   какая   существует   связь   между   таблицей   начислений   и диаграммой,   перейдите   на   лист   «Начисления»,   в   середину   таблицы   вставьте   новую   строку (выделите  строку таблицы  и выполните команду Вставка  – Строки). Распространите на новую строку формулы, заполните данные на нового сотрудника, теперь перейдите на лист «Диаграмма» и проверьте,   как   новые   данные   отразились   на   диаграмме   –   новый   сотрудник   сразу   же   внесен   в диаграмму.  Задание 3. Создание ведомости на получение компенсации на детей на основе таблицы начислений.  Ссылки на ячейки другого листа рабочей книги  1. Перейдите к Листу 3. Переименуйте его в «Детские».  104 2. Ведомость будет содержать три столбца: «ФИО», «Сумма» и «Подпись». Сформатируйте заголовок таблицы.  3.   В   графу   «ФИО»   нужно   поместить   список   сотрудников,   который   мы   имеем   на   листе «Начисления». Чтобы установить связь между листами, на листе «Детские» поместим формулу, по которой данные будут вставляться из листа «Начисления»,  ∙ Выделите ячейку А2 листа «Детские» и введите формулу: =Начисления!В5, где имя листа определяется восклицательным знаком, а В5 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе «Начисления». Можно набрать формулу с клавиатуры, а можно после набора знака равенства перейти на лист «Начисления», выделить ячейку, содержащую первую фамилию и нажать Enter, не возвращаясь к листу «Детские».  ∙ Перейдите на лист «Детские», проверьте полученную формулу и распространите ее вниз. Список фамилий сотрудников теперь есть и на листе «Детские». Более того, если внести новые данные   в   таблицу   начислений,   они   отразятся   и   на   листе   «Детские».   (Нужно   будет   только распространить формулу ниже в случае необходимости).  4. В графе «Сумма» аналогичным образом нужно разместить формулу: =Начисления!Н5*98, где Н5 – адрес первой ячейки на листе «Начисления», содержащей число детей. Заполните эту формулу вниз и примените денежный формат числа. 5. Выполните обрамление таблицы.  6.  Для   того,  чтобы   список   состоял   только   из   сотрудников,   имеющих   детей,   установите фильтр по наличию детей (Данные – Фильтр – Автофильтр), в раскрывающемся списке «Сумма» выберите «Настройка…» и установите критерий >0).  7.   Осталось   поместить   выше   таблицы   заголовок   ведомости   (при   помощи   вставки дополнительных строк) и можно распечатывать.  Задание 4. Создание ведомости на выдачу заработной платы. (самостоятельная работа)  Четвертый лист должен содержать ведомость на выдачу заработной платы. таблица должна содержать следующие столбцы: «ФИО», «Сумма к выдаче» и «Подпись».  1. Переименуйте лист из «Лист 4» в «К выдаче».  2. Сформатируйте заголовок таблицы.  3.   Вставьте   фамилии   сотрудников   со   ссылкой   на   лист   «Начисления»   (как   на   листе «Детские»). Заполните формулу вниз.  4. Вставьте суммы аналогичным образом (ссылка на лист «Начисления»).  5. Выполните обрамление таблицы.  20 Вопросы к практикуму: 1. Сколько листов используется в книге по умолчанию?  2. Как перемещаются по листам внутри книги?  3. Чем отличается активный лист в книге от всех остальных?  4. Как добавить в книгу новый лист?  5. Если после ввода в ячейку вы наблюдаете ###### вместо результата, в чем причина такой ситуации?  6. Как исправить ошибку, если после ввода в ячейку числа вы наблюдаете ######?  7. Какими способами можно изменить ширину столбца?  8. Что такое автозаполнение, когда оно применяется?  9. Как произвести автосуммирование?  10. Как присвоить ячейке имя?  11. Как выделить одновременно 2 столбца?  12. Назвать 2 способа вставки диаграммы?  13. Как связать данные, расположенные на разных листах книги?  21 Практические занятия  № 9­12. Тема занятия: Электронные таблицы. Расчетные задачи в технической механике. Цели   занятия:   Обучающая:  знакомство   с   технологией   обработки   числовых   данных; умение   выделять   главное   в   электронных   таблицах   Excel;   умение   различать   типы   данных   и применять   их   на   практическом   занятии;   формирование   знаний   о   способах   применения   и оформления   электронных   таблиц;   умение   правильно   записывать   формулы   в   таблицах   с использованием ссылок; формирование способности установить связь электронных таблиц Excel с другими   изучаемыми   предметами.  Развивающая:  развивать   логическое   мышление,   умение обобщать,   конкретизировать;   формирование   внимания,   умения   наблюдать   и   закреплять   знания; формирование   способности   сопоставлять,   находить   отличия   и   сходства.  Воспитательная: воспитывать самостоятельность, уважительное отношение друг к друг; способствовать развитию активности, инициативности. 22 Практическая работа №       9: Электронные таблицы. "АГЕНТСТВО ПО ПРОДАЖЕ НЕДВИЖИМОСТИ" Основная таблица A B C D E F G H I Стоимость 1 кв.м., $ Площадь квартиры, кв.м. Коэффициент престижности Стоимость квартиры, $ Налог на приватизацию, $ Плата агентству по продаже недвижимости Стоимость перевода и регистрации недвижимости Общая стоимость квартиры и услуг, $ 740 584 750 695 740 560 42 25 39 27 35 22 3,2 1,5 2,7 1,9 2,9 1,2                                         Название столбца Стоимость квартиры, $ (вычисляется с учетом коэффициента престижности) Налог на приватизацию, $ (1% от стоимости квартиры) Плата агентству по продаже недвижимости, $ (5% от стоимости квартиры) Стоимость перевода и регистрации недвижимости, $ (5% от стоимости квартиры) Общая стоимость квартиры и услуг, $ 1 Район 2 Ленинский 3 Кировский 4 Центральный 5 Советский 6 Свердловский 7 Октябрьский Выходные данные Имя столбца E F G H I Уровень C. Уровень B. 1) Стоимость квартиры находится путем произведения стоимости площади квартиры на коэффициент престижности: =B2*C2*D2 2) Налог на приватизацию вычисляется по формуле: =0,01*E2 1) Вычислите самостоятельно плату агентству по продаже недвижимости (столбец G) и стоимость перевода и регистрации недвижимости (столбец H). 2) Общая стоимость квартиры и услуг находится путем сложения соответствующих значений в столбцах E, F, G, H. Уровень A.  1) В ячейку A8 занесите курс доллара (1$ = 133 тенге). 23 2) Столбце J вычислите общую стоимость квартиры и услуг в тенге, на основе курса доллара в ячейке A8. 24 Практическая работа №       10: Электронные таблицы. Успеваемость учащихся  за 1 полугодие № п/п Фамилия, Имя  Ахундова Саида Безверхая Ирина Бурнашев Вячеслав Вербицкий Максим 1 2 3 4 5 Жирков Илья Захожева Ксения 6 Кашапов Руслан 7 Кинжибаева Альбина 8 9 Климов Владимир 10 Маркина Оксана Рахимов Марат 11 12 Рябов Артем 13 Фаизова Гульнара 14 Филькина Александра к ы з я   й и к с с у р а р у т а р е т и л а р б е г л а я и р т е м о е г я и р о т с и 3 5 4 3 3 4 5 4 3 5 5 4 5 5 3 5 4 3 3 5 5 4 4 5 4 4 4 5 3 5 4 3 3 4 5 3 3 4 4 4 4 4 3 5 4 3 4 5 5 3 3 5 4 4 4 5 3 5 4 3 3 5 5 4 5 5 5 4 5 5 е и е а н з о в т с е щ б о 3 5 5 3 4 5 5 4 5 5 5 4 5 5 я и ф а р г о е г я и г о л о и б а к и з и ф я и м и х О А М Х   я и р о т с и Ж Б О 3 5 4 3 4 5 5 4 4 5 5 4 5 5 3 5 3 3 4 5 5 4 4 4 5 4 4 4 3 5 4 3 3 5 5 4 3 5 5 4 4 4 3 5 4 3 4 5 5 4 3 5 4 4 5 5 3 5 5 3 4 5 5 5 5 5 5 4 5 5 5 5 5 4 4 5 5 5 5 5 5 4 5 5 Итого:    "5" "4" "3" "2"                                                                                                 а р у т ь л у к   я а к с е ч и з и ф 5 5 5 5 5 5 5 4 4 5 5 5 5 5         к ы з я   й ы н н а р т с о н и 4 5 5 3 4 5 5 4 5 5 5 3 5 5                 а к и т а м р о ф н и к о т е м т о о г е с в   " 5 "   о в ­ к " 4 "   о в ­ к " 3 "   о в ­ к " 2 "   о в ­ к 3   5   5   3   4   5   5   3   4   4   4   4   5   5                                                                                                                                                             л л а б й и н д е р с                               25 Практическая работа №       11: Электронные таблицы. Приложение 6. Заполнить таблицу всеми числами месяца, серым цветом помечены выходные дни. № п/п Фамилия, и., о., 1   2   3   4   5   6   7   8   9   10   д я р з а р числа месяца профессия                                         1 2 3 4                                                                                 5                       6                       7                       8                                                                                                                                                                                 к о в я   и н д                     дни неявок к с у п т о ь н з е л о б л у г о р п                                                             о н а т о б а р т о в о с а ч                     26 Практическая работа №       12: Электронные таблицы. Вставка функций.  Использование встроенных функций. 1. Откройте созданный вами шаблон Табель.xls 2. Введите название текущего месяца (февраль) в заголовок табеля 3. Сразу же выделите цветом столбцы, соответствующие нерабочим дням недели. 4. Заполните поле Фамилия, и., о. по своему усмотрению (не менее 10 фамилий) 5. Проставьте для каждого сотрудника: о, если он находится в отпуске, или б, если в этот день сотрудник болеет, или  Количество часов, отработанных за день (4, 8 или 11) или    п, если прогуливает о, б, п – русские буквы, проставляются без кавычек 5. Самостоятельно вставьте формулу суммирования соответствующих ячеек строки для  подсчета отработанных часов. Заполните формулу вниз. 6. Для подсчета дней явок необходимо в каждой строке (для каждого сотрудника)  подсчитать количество ячеек, содержащих числа (не суммируя эти числа – функция  СЧЁТ).  7. Подсчитайте для каждого сотрудника  количества дней проведенных в отпуске, на  больничном и прогулов, если таковые имеются. 8. Сохраните файл в личной папке под именем Табель_февраль 27