Мастер-класс электронные таблицы

муниципальное бюджетное учреждение дополнительного образования   муниципального района Иглинский район Республики Башкортостан «Станция юных техников» КОНСПЕКТ  Мастер­класса  ЭЛЕКТРОННЫЕ ТАБЛИЦЫ Составила и провела Педагог дополнительного образования Козлова Любовь Владимировна Иглино, 2018 г. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ Занятие 1.  Знакомство с программой «Электронные таблицы».  Ввод и редактирование данных. Формулы.  Автозаполнение. Сохранение документов. Очень   часто   обработка   числовой   информации   связана   с   применением   таблиц.   С   такими   задачами сталкиваются завучи школ при составлении расписаний, диспетчеры при составлении графиков движения транспорта, экспериментаторы при проведении серии опытов, бухгалтера при расчете заработной платы и др. Во всех приведенных примерах изменение значения какого­то одного параметра требует пересчета большого числа результатов. Чтобы облегчить себе жизнь, человек поручил все рутинные вычисления компьютеру. Для этого и были созданы программы Электронные таблицы. Первые электронные таблицы –  Calc  и  SuperCalc, затем появились и с успехом использовались программы  Lotus. В настоящее время на компьютерах  IBM  и  IBM­ совместимых  используется   пакет  Microsoft  Office.  С  какой  из  программ   этого   пакета вы  уже  знакомы? (Microsoft  Word  –  текстовый  редактор,  или иногда,  чтобы подчеркнуть огромные возможности,  его еще называют текстовый процессор). В этот же пакет входит программа электронные таблицы  Microsoft  Excel (табличный процессор). Ввиду   того,   что   продукция   фирмы  Microsoft  требует   покупки   лицензий,   используется   свободно распространяемый   пакет  OpenOffice.org.   В   его   состав   входит   программа   электронные   таблицы OpenOffice.org Calc. Запишите в тетрадь: ЭЛЕКТРОННЫЕ ТАБЛИЦЫ ­ программы для оперативного выполнения на ЭВМ  различных расчетов, представленные в табличной форме. ОСНОВНЫЕ ВОЗМОЖНОСТИ ЭЛЕКТРОННЫХ ТАБЛИЦ:  Ввод, редактирование и форматирование данных в таблицах;  Мгновенный пересчет калькуляций;  Хранение и поиск калькуляций на диске ЭВМ;  Вывод калькуляций на печать; и другие. Запустите программу Электронные таблицы (ЭТ) Microsoft Excel или OpenOffice.org Calc Вы видите, что окно программы – это стандартное окно, состоящее из строки заголовка (что там написано?) с кнопками «Свернуть», «Восстановить» и «Закрыть». Обратите внимание, что такие же кнопки есть и в строке меню, но эти   кнопки   относятся   к   документу.   Рекомендуется,   чтобы   окно   программы   и   окно   документа   были распахнуты на весь экран. В отличие от текстового редактора, документ ЭТ – это  Книга, состоящая из нескольких листов (по умолчанию – 3), причем листы можно добавлять и удалять. Далее, ниже строки меню, вы видите панели инструментов «Стандартная» и «Форматирование». Если их  нет,  то  включите  (Вид  – Панели  инструментов),   если  есть  лишние,   то  их   лучше  убрать,  чтобы   не загромождали экран. Между панелями инструментов и таблицей находится строка формул, а слева от нее – поле адреса (поле имени). Внизу – строка состояния. Таблица   состоит   из   ячеек.   Каждая   ячейка   имеет   свой   адрес,   обозначаемый  латинскими (английскими) буквами и цифрами, например, А1, DZ255,  IV65536. Буквы обозначают номер столбца, а цифры – номер строки. Переход к ячейкам осуществляется: мышью клавишами управления курсором задавая адрес ячейки в поле адреса. (Попробуйте) ­ ­ ­ Ячейка, в которой стоит курсор, называется выделенной или активной. Можно выделить  смежные группы ячеек  прямоугольной формы (блоки), «проводя» мышью по ячейкам, или клавишами перемещения курсора с нажатой  клавишей  Shift. Выделите блок  В2:В8. Потом  В2:Е8.  При этом  первая  ячейка  блока является активной. Можно выделять несмежные блоки ячеек, выделяя каждый блок, удерживая нажатой клавишу Ctrl. Можно выделить весь столбец, щелкнув мышью по названию столбца, всю строку или весь лист. Снять выделение – щелкнуть по любому месту таблицы. Теперь будем вводить информацию в ячейки.     заголовками столбцов «С» и «D». Выделите ячейку С3 и введите туда свое имя. Закончив ввод, нажмите клавишу Enter или щелкните по  зеленой галочке рядом со строкой формул. Чем отличаются эти два способа?. Теперь введите в эту же  ячейку свою фамилию: поставьте курсор на нужную клетку и нажмите первую букву. Имя пропало! Все  равно закончите ввод фамилии.  Обратите внимание на то, что как только вы нажали буквенную клавишу, в ячейке появляется текстовый  курсор, и многие кнопки на панели инструментов и команды становятся недоступными. Но как только вы  закончите ввод, кнопки и команды снова станут доступными.  Теперь после фамилии нужно добавить имя. Редактировать содержимое ячейки можно либо в самой  ячейке, дважды щелкнув по ней мышью, либо в строке формул.  Фамилия и имя в ячейке не умещаются. Но поскольку в соседней ячейке ничего нет, то информация как  бы занимает и ее. Поставьте курсор в клетку D3. В строке формул ничего нет – ячейка пустая. Введите в  эту ячейку номер своего класса, например, 9 «А». Обратите внимание, что «хвост» информации  из  ячейки С3 «пропал». Посмотрите в строке формул – все на месте. Просто весь текст не помещается в  ячейке, и его «хвост как бы заворачивается». Ширину ячейки можно увеличить, схватившись мышью  между   В ячейку С4 введите цифру 5. Что произошло? (цифра прижалась к правому краю ячейки).  В ячейку С5 введите букву А. (буква прижалась к левому краю ячейки).  Сделаем вывод, что числовая информация по умолчанию выравнивается по правому краю ячейки, а  текстовая – по левому. А чем нам грозит то, что какое­то, как мы думаем, число ЭТ восприняла как  текст? Можно ли с текстом производить какие­либо арифметические действия? (Нет!) Вот вам и  подсказка: следите, к какому краю будет прижата введенная вами информация, чтобы определить ее тип.  А отформатировать так, как нравится, вы сможете позже.  В ячейку С6 введите число 50,45. К какому краю прижалась введенная информация число? Если к  правому – то информация воспринялась как число. А если к левому – то как текст. Почему? Какой знак  Вы поставили между целой и дробной частью числа – точку или запятую? В операционной системе есть  возможность самим задавать, какой символ использовать как знак­разделитель целой и дробной частей  числа (В Windows: Пуск – Настройка – Панель управления – Язык и стандарты. По умолчанию  используется запятая). Поэтому рекомендуется набирать цифры, знак разделителя, знаки  арифметических действий на цифровой клавиатуре. Это намного удобнее (цифровую клавиатуру нужно  включить клавишей Num Lock). Итак, в клетку С6 мы ввели число 50,45. В клетку С7 введите «50 кг». Какого типа эта информация? (текстовая). Почему? (кроме цифр  содержатся буквенные символы). Можно ли будет с этой информацией проводить какие­либо  арифметические операции? (нет). Запишите в тетрадь: В ячейках таблицы может размещаться информация следующих типов: текст (символы); 1. 2. числа целые и дробные. Числа не могут содержать никаких других символов кроме цифр, знака   разделителя   целой   и   дробной   частей,   знаков   +   или   –,  (Е   при   использовании экспоненциальной формы записи чисел); 3. формулы для расчета результатов. Задание: в ЭТ составить накладную. Накладная – это таблица, содержащая следующие графы: А № 1 1 2 В наименование помидоры С цена D количество Е стоимость 48,55 123 Откройте новый лист (а не книгу!), щелкнув на ярлычке «Лист2» и заполняйте таблицу (форматировать пока  не надо).  Графа Е ("стоимость") должна содержать формулу для расчета: "цена"*"кол­во". Как это записать? Считать в уме или на калькуляторе не нужно, ведь мы работаем на компьютере с программой «Электронные таблицы».   Вспомним   определение:  «ЭЛЕКТРОННЫЕ   ТАБЛИЦЫ   –   программы   для   оперативного выполнения на ЭВМ различного рода расчетов…», т.е. мы должны заставить компьютер, чтобы он сам рассчитывал стоимость товаров! Наверное, можно записать формулу типа "48,55*123". Но тогда, если у нас изменится цена или количество товара, формулу придется переписывать?!  В формуле мы должны отразить следующую информацию: ЧИСЛО, НАХОДЯЩЕЕСЯ В ЯЧЕЙКЕ C2,   НУЖНО   УМНОЖИТЬ   НА   ЧИСЛО,   РАСПОЛОЖЕННОЕ   В   ЯЧЕЙКЕ  D2,   а   чтобы   сказать компьютеру, что он должен потрудиться и выполнить вычисления, любая ФОРМУЛА НАЧИНАЕТСЯ СО ЗНАКА РАВЕНСТВА, т.е. =C2*D2. Запишите в тетрадь: Формулы в ЭТ Excel начинаются со знака равенства!!! Обратите внимание, что адреса ячеек состоят из латинских (английских) букв. С буквой  D  обычно проблем не возникает, а вот с С – сложнее, тем более, что и русская [эс] и английская [си] находятся на одной клавише. Поэтому ввод формулы удобно осуществить так: нажать клавишу «=»; щелкнуть мышью по ячейке С2; нажать клавишу «*» (лучше на цифровой клавиатуре); щелкнуть мышью по ячейке  D2; нажать Enter. В строке формул мы увидим формулу “=c2*d2”, а в самой ячейке – результат расчета по этой формуле (число 5971,65).   Ячейка имеет несколько уровней:  Изображение, которое мы видим на экране (отформатированные значения, ­ текст, числа) Формула Попробуем изменить цену или количество товара. Произошел пересчет значения. Если бы мы не  использовали формулу, то пересчета бы не получилось. Продолжите ввод таблицы самостоятельно. Но заполняйте только столбцы «наименование», «цена»,  «количество». Столбцы «№» и «стоимость» пока не трогайте. Следите за типом информации! Введите  еще 4 наименования товаров, их цены и количество. А № 1 1 2 3 4 5 6 В наименование С цена D количество помидоры огурцы лук картофель капуста 48,55 25,38 7,22 5,50 7,20 123 58 64 200 155 Е стоимость =c2*d2 Подумайте, какая формула должна стоять в ячейке Е3? (=С3*D3), а в ячейке Е4? (=С4*D4) В   формулах   номера   столбцов   одинаковые,   а   вот   номера   строк   отличаются.   Конечно,   можно скопировать, но вспомните графический редактор Paint и текстовый редакторWord – там при копировании мы получали точно такие же копии объектов без всяких изменений!   Но все же попробуем скопировать формулу, а потом будем думать, как же изменять номера строк.  Вначале скопируем через буфер обмена: 1. Поставить курсор в клетку, ОТКУДА будем копировать (Е2). 2. Нажать кнопку «копировать» на панели инструментов (Ctrl+C). 3. Поставить курсор в клетку, КУДА будем копировать (Е3). 4. Нажать кнопку «вставить» на панели инструментов (Ctrl+V). Посмотрите, какая формула была в клетке Е2, откуда мы копировали? (=С2*D2). А какая формула оказалась в клетке Е3, куда мы ее скопировали? (=С3*D3). Т.е. Запишите в тетрадь: При копировании (размножении) формул предусмотрена их АВТОМАТИЧЕСКАЯ  НАСТРОЙКА. Это означает, что один раз введенную формулу, например, в верхнюю  ячейку столбца, можно скопировать в другие клетки, расположенные ниже, причем  обозначения клеток, используемые в формуле (ссылки), автоматически меняются:   при  копировании по вертикали изменяются номера строк, а при копировании по горизонтали  изменяются номера столбцов.  Этот сервис ­ одна из важнейших черт всех систем электронных таблиц. Такие ссылки на адрес ячейки называются ОТНОСИТЕЛЬНЫМИ.  Кроме копирования через буфер обмена, в ЭТ есть возможность  АВТОЗАПОЛНЕНИЯ     ЯЧЕЕК. Если выделить некоторую ячейку, то в правом нижнем углу рамки есть маленький черный квадратик (Маркер заполнения). Курсор,  попав на него, принимает форму маленького черного крестика. Схватившись мышкой за этот крестик и протягивая мышь вниз («тянуть за хвостик»), мы копируем формулы, а в ячейках таблицы появляются результаты расчетов.   Попробуйте применить  операцию  автозаполнения  к цифре 1 (графа  «№»).  Что произошло? (везде скопировались «1»). Нам такое заполнения не нужно. Отмените последнюю операцию (кнопка «отменить» на панели инструментов) А теперь в ячейку А3 введите цифру 2, выделите обе ячейки (при выделении ячеек курсор   имеет   форму   большого   белого   креста).   Теперь   протащите   общий   маркер   заполнения   вниз. Компьютер определяет закон, по которому происходит заполнение следующих ячеек.  А что произойдет, если в ячейке А1 – число 1, а в А3 ввести число 5?  Для   заполнения   последовательного   ряда   чисел   (1,   2,   3,   …)   достаточно   ввести   первое   число   и протащить маркер автозаполнения, удерживая нажатой клавишу Ctrl.  Функция автозаполнения позволяет также создавать списки. Введите в ячейку В10 «март» и протяните маркер вниз, вправо или влево. В свободную ячейку введите «среда» и снова выполните автозаполнение. Отмените последние действия.     А теперь нам нужно подсчитать итоговую сумму в ячейке Е7. Как это сделать? Конечно, можно записать формулу =Е2+Е3+Е4+Е5+Е6. А если строк много? Неужели так придется писать до 65536 строки?! Нам нужно просуммировать все числа в интервале Е2:Е6. Воспользуемся функцией СУММА. На панели инструментов есть кнопка «Автосумма» (греческая буква «сигма» –    Σ  ). Нажав на нее, в ячейке Е7 мы получим формулу =СУММ(Е2:Е6).    1 2 3 4 5 6 7 А № 1 2 3 4 5 В наименование С цена D количество помидоры огурцы лук картофель капуста ИТОГО: 48,55 25,38 7,22 5,50 7,20 123 58 64 200 155 Е Стоимость 5971,65 1472,04 462,08 1100 1116 =СУММ(Е2:Е6) Скопируйте формулу влево (из Е7 в клетки С7 и D7, воспользовавшись автозаполнением). Обратите внимание,   что   при   копировании   ПО   ГОРИЗОНТАЛИ   настройка   формул   заключается   в   изменении ЗАГОЛОВКА СТОЛБЦА, а при копировании ПО ВЕРТИКАЛИ – в изменении НОМЕРА СТРОКИ. В   конце   работы   созданную   нами   таблицу   необходимо   сохранить   на   диске   для   последующего использования. Сделать это можно, либо воспользовавшись кнопкой «Сохранить» на панели инструментов, либо с помощью команд «Сохранить» или «Сохранить как…» из меню «Файл». При этом необходимо указать, КУДА сохранять документ и с каким именем.  Сохраните документ в своей папке с именем «Накладная_Фамилия». Закройте документ (Рабочую книгу), щелкнув по кнопке  в строке меню! А теперь откройте этот файл. Что в ЭТ является файлом, рабочий лист или вся рабочая книга?  Если осталось время, используйте его для форматирования таблицы. Эта процедура Вам знакома после изучения текстового редактора. Обратите внимание, что по умолчанию границ ячеек на бумаге нет, хотя мы их видим серыми. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ Занятие 2.  Отработка и закрепление темы «Расчет по формулам» Абсолютные ссылки. Имя ячейки Самостоятельная работа. Создайте в Excel таблицу начисления зарплаты:  А 1 № 2 1 3 2 6 5 7 В Ф.И.О. … ИТОГО: С Тариф  … D Отраб. час. … Е Сумма … =СУММ(Е2:Е6) Графы   «Ф.И.О.»,   «Тариф»   (сколько   рублей   работник   получает   за   отработанный   им   час),   и «Отработано часов» заполнить произвольными данными. «№» – автозаполнение. Сумма рассчитывается по формуле =«Тариф»* «Отраб.час.» Покажите работу учителю. Если есть время, отформатируйте таблицу красиво. Добавьте столбцы «Подоходный налог» = 13% от «Суммы» и «Сумма на руки» = «Сумма» – «Подоходный налог». Теперь изменим условие: пусть Ваше предприятие – совместное с зарубежными фирмами и начисляет зарплату своим работникам в долларах (в у.е.). Исправьте заголовок на «Тариф (у.е.)». Однако в России согласно законодательству расчеты с физическими лицами в иностранной валюте запрещены. Вы обязаны выплачивать зарплату в рублях (по текущему курсу). Поэтому в таблице нужно добавить столбец «Тариф (руб.)». Добавление столбца в Excel осуществляется ПЕРЕД курсором, т.е. ставим курсор в любую ячейку столбца  D  («Отраб.час»):  Вставка  –  Столбцы  (в  Exel­2007   на   панели  Главная:   Вставить  –  Вставить столбцы на лист или через контекстное меню).  Как же рассчитать «Тариф в рублях»? Можно предложить формулу =С2*29,95 (где 29,95 – текущий курс доллара по курсу ММВБ). Действительно, введя эту формулу и скопировав ее до нужной строки, можно получить требуемые значения. Однако, это не лучший вариант, т.к. при изменении курса придется исправлять формулы во   ВСЕХ  строках. А если их 65536, то когда вы закончите исправление формул, курс доллара снова изменится. К тому же, из самой таблицы не видно, исходя из какого курса проводится расчет.  Поэтому лучше значение курса доллара вынести и поместить в одну из ячеек таблицы и в формулах ссылаться на нее. Добавим   строку  перед  шапкой  таблицы:   поставить   курсор  в  любую   ячейку  строки  1;  Вставка  – Строка.   В   ячейку   А1   напишите   слово   «курс».   Куда   ввести   значение   29,95,   чтобы   с   ним  можно  было производить арифметические действия? (в ячейку В1. Можно ли в ячейке А1 написать «курс 29,95»? В этом случае   информация   будет   восприниматься   как   текстовая,   с   которой   нельзя   производить   никаких арифметических операций. Это неправильно.  В ячейку  D3 вводим формулу =С3*В1. Скопируем формулу вниз! (Выполните автозаполнение) Что получается? В следующих ячейках значения не вычисляются, а появляется #ЗНАЧ. Почему это происходит? Какая формула была в ячейке D3, откуда мы копировали? (=С3*В1). А какая формула оказалась в ячейке D4, куда мы скопировали? (=С4*В2).  А 1 Курс 2 № 1 2 5 3 4 7 8 В 29,95 С D E Ф.И.О. Тариф ($) =С3*В1 … … … ИТОГО : Тариф (руб.) Отраб. час. … … =СУММ(F2:F6) F Сумма ($) Вспомните, что при копировании формул выполняется их АВТОМАТИЧЕСКАЯ НАСТРОЙКА. При   копировании   по   вертикали   изменяются   номера   строк,   а   при   копировании   по   горизонтали изменяются номера столбцов. Такая ссылка на адрес ячейки называется ОТНОСИТЕЛЬНОЙ. Когда автоматическая настройка происходила при копировании формулы расчета суммы, мы радовались:  какой умный компьютер, понимает, что мы от него хотим. А в данном случае, когда он делает то же  самое, мы огорчаемся: компьютер не понимает, что же от него требуется. А чего же, собственно, мы  хотим? Нам нужно, чтобы при копировании формулы (=С3*В1) первый сомножитель С3 («Тариф в  долларах») изменялся, а второй сомножитель В1 («курс») оставался без изменений. Чтобы компьютер не  изменял какую­либо ссылку при копировании формул, ему нужно дать какой­то сигнал, поставить какой­ то значок. Это значок «$» (не в смысле «доллар», а просто значок). Поскольку при копировании формулы  вниз у нас не должен меняться  номер строки, то знак «$» нужно поставить перед 1, а если нам придется  копировать формулу по горизонтали, номер столбца также не должен меняться, поэтому мы поставим  знак $ и перед В. Таким образом  получим формулу =C3*$B$1. Исправьте и скопируйте вниз. Теперь  получилось?! Запишите в тетрадь: Если при копировании формулы не должен изменяться номер строки и (или) номер столбца, то  перед соответствующим номером в адресе ячейки нужно поставить значок «$». Такие ссылки  называются АБСОЛЮТНЫМИ. Часто применяют не чисто относительные или абсолютные ссылки, а смешанные, например,  B$1 или $B1. Номер, перед которым не стоит знак «$», будет изменяться при копировании, а тот  номер, перед которым знак «$» стоит, останется без изменения. (Чтобы в строке формулы оказалась ссылка $B$1, или $B1, или  B$1 выделить мышью адрес ячейки В1 в строке формулы и нажимать клавишу F4) Измените значение курса доллара в ячейке В1. Убедитесь, что информация в таблице пересчиталась. Ячейкам можно присваивать имена. Это удобно при работе с большими таблицами, когда ячейка, на которую   делается   ссылка,   находится   далеко   от   остальных   ячеек   (например   на   другом   листе   книги). Например,  на кабинет директора школы  можно указать  как  «кабинет  № 15»  или «кабинет с табличкой «Директор»).  Чтобы присвоить ячейке имя, нужно ее выделить и выполнить команды Вставка ­ Имя ­ Присвоить (в Exel­2007   на   панели  Формулы:   Присвоить   имя  или   через   контекстное   меню  Имя   диапазона).   В открывшемся окне компьютер предлагает вам ввести имя ячейки. По умолчанию –  текст из соседней ячейки. Вы можете согласиться или изменить имя. Имя отображается в адресном поле (поле имени). Имя   не   должно   содержать   пробелов   и   знаков   пунктуации,   а   также   начинаться   с   цифры. (Например, имя «курс доллара» недопустимо, можно использовать имя «курс» или «курс_доллара»). Присвойте ячейке В1 имя «курс», исправьте формулу в ячейке D3: =С3*курс и скопируйте ее вниз. Использование имени ячейки соответствует действию абсолютной ссылки. Добавьте в таблицу еще одну графу «Сумма (руб.)» и вычислите значения в ней как «Сумма ($)*Курс. А В Курс 29,95 № Ф.И.О. 1 2 5 … ИТОГО 1 2 3 4 7 8 С D E F H Тариф ($) … Тариф (руб.) =С3*Курс … Отраб. час. … Сумма ($) … Сумма (руб.) =F3*Курс … Отформатируйте   значения   в   таблице   так,   чтобы   соответствующие   числа   имели   обозначения   либо рубли (р.), либо доллары ($): Формат – Ячейки – вкладка Число – формат Денежный – число десятичных знаков 2 – обозначение соответствующее. Не забудьте сохранить таблицу в вашей папке. Мы будем ее использовать на следующем уроке. Задание. Создайте новую книгу. Подготовьте таблицу для расчета стоимости подписки в зависимости  Использование смешанных ссылок от количества месяцев. Стоимость подписки на 1 месяц задается (это цена издания). Стоимость на несколько месяцев должна рассчитываться. Постарайтесь записать эту формулу так, чтобы ее можно было скопировать вниз и вправо. Какие ссылки нужно применить? В D G H А С Е F 1 2 3 4 5 6 7 № Наименование 1 «АиФ» «Огонек» «7 дней» 2 3 4 … … … ИТОГО: СТОИМОСТЬ ПОДПИСКИ Количество месяцев 3 6 9 12 2 =… … … 1 20 50 40 … Отформатируйте таблицу. Объедините ячейки А1:Н1 (заголовок). Объедините ячейки А2:А3, а также В2:В3. Примените вертикальное выравнивание по центру. (Формат – Ячейки… – вкладка Выравнивание). Задания для самостоятельной работы:  1. На Листе 2 той же рабочей книги, где вы рассчитывали стоимость подписки, подготовьте таблицу умножения (таблицу Пифагора), где на пересечении столбца и строки стоит произведение соответствующих чисел. 2. На Листе 3 той же рабочей книги подготовьте таблицу квадратов двузначных чисел.              Ед. Дес. 0 1 2 3 4 5 6 7 8 9 таблица квадратов 1 2 3 4 5 6 7 8 9 169 529 1089 1849 2809 3969 5329 6889 8649 144 484 1024 1764 2704 3844 5184 6724 8464 121 441 961 1681 2601 3721 5041 6561 8281 100 400 900 1600 2500 3600 4900 6400 8100 289 729 1369 2209 3249 4489 5929 7569 9409  Для столбцов от В до К задайте ширину 5 (Формат – Столбец – Ширина…)   Значения единиц и десятков введите, используя автозаполнение.   Ячейку А2 отформатируйте следующим образом: (Формат – Ячейки…) на вкладке Границы включите соответствующую   границу.   На   вкладке  Выравнивание  включите   флажок   «переносить   по   словам». Введите   «   Ед.    Дес.». Подберите необходимое количество пробелов перед и между словами.  196 576 1156 1936 2916 4096 5476 7056 8836 225 625 1225 2025 3025 4225 5625 7225 9025 256 676 1296 2116 3136 4356 5776 7396 9216 324 784 1444 2304 3364 4624 6084 7744 9604 361 841 1521 2401 3481 4761 6241 7921 9801   текст  В   ячейке   В3   должна   быть   формула   для   возведения   в   квадрат   числа,   составленного   из   количества десятков, указанных в столбце А и единиц из строки 2.  Возвести в квадрат можно несколькими способами:  1) – умножить число на него же. 2) – используя операцию возведения в степень  ( ^2 ).  3) –   используя   встроенную   функцию   =СТЕПЕНЬ(…;2).   (Вставка  –  Функция.   В   категории «Математические» функция «Степень»).  (в  Exel­2007 на панели  Формулы: Вставить функцию  или в строке формул значок fx)..     Наиболее сложным в этом задании является запись самого числа. Сохраните выполненные задания в своей папке с именем «Абсолютные ссылки». Обратите внимание,  что в одном файле сохраняется вся рабочая книга (все листы).  ЭЛЕКТРОННЫЕ ТАБЛИЦЫ Занятие 3. Использование встроенных функций Построение диаграмм На прошлом уроке мы начисляли зарплату работникам. Откройте сохраненный вами файл. Задание 1. Дополним нашу таблицу некоторыми статистическими данными: вычислим Минимальную, Максимальную и Среднюю заработную плату.   D А В С E F H Тариф (руб.) =С3*Курс … Отраб. час. … Сумма ($) … Сумма (руб.) =F3*Курс 1 2 3 4 7 8 9 10 11 12 13 Курс № 29,95 Ф.И.О. Тариф ($) … 1 2 5 … ИТОГО: Статистика: Мин. з/пл Макс. з/пл Средняя з/пл Для вычисления соответствующих значений воспользуемся Мастером функций.  Выделить ячейку, в которой должно находиться значение минимальной зарплаты наших работников (С11). Вставка – Функция или кнопка  fx  панели инструментов «Стандартная» или же кнопка  fx  в строке формул. Открывается диалоговое окно Мастера функций. На 1 шаге необходимо выбрать нужную функцию. Excel  имеет огромное количество функций, которые подразделяются на категории:  Финансовые, Дата и время,   Математические,   Статистические  и   т.д.   Необходимые   нам   функция   находятся   в   категории Статистические.   Функция  МИН  возвращает   минимальное   значение   из   списка   аргументов.   Логические значения и текст игнорируются). На втором шаге в открывшемся окне мы должны указать список аргументов (среди каких чисел искать минимальное).  Т.к. мы вводим формулу в ячейку С11, то по умолчанию компьютер предлагает нам диапазон С3:С10. А нам необходимо искать минимальное значение в столбце Н. Для этого достаточно выделить эти ячейки. Но диалоговое окно загораживает нужную часть экрана. Его можно переместить, но это не всегда может спасти положение, особенно если таблица большая. В таком случае можно щелкнуть по кнопке   (с маленькой красной стрелочкой, указывающей на выделенную ячейку) и все окно свернется в строку. Теперь выделяем значения в столбце Н (итоговое значение не берем!!!). В З/пл<  Ср. 100 0 строке появляется Н3:Н7. Щелкаем по кнопке  . Возвращается наше окно. У функции МИН может быть различное число аргументов, их можно вводить в следующие поля. Но т.к. нам аргументов больше задавать не надо, нажимаем кнопку ОК. Максимальное и Среднее значения найдите самостоятельно. Задание   2.  Мы   будем   выплачивать   дотацию   (материальную   помощь)   низкооплачиваемым сотрудникам. Низкооплачиваемыми будем считать тех, у кого заработная плата ниже средней. Им мы будем доплачивать   100   рублей.   Попытаемся   сформулировать   критерии   выплаты   материальной   помощи:   ЕСЛИ з/плата меньше средней, то выплачиваем дотацию в размере 100 рублей, в противном случае платим 0 рублей. На языке блок­схем и на школьном алгоритмическом языке это записывается так: если З/пл<Ср    то 100    иначе 0 все Теперь запишем это в таблице. Добавим столбец  I  «Дотация» и для первого нашего сотрудника в клетке I3 запишем формулу, содержащую функцию ЕСЛИ из категории «Логические». Копируем формулу вниз. Обратите внимание, что никто из наших сотрудников не получает дотацию (в  крайнем случае – только первый)? Неужели у всех зарплата выше средней (такого не бывает)!  Догадались, что к ячейке С13 (Средняя з/плата) нужно применить абсолютные ссылки?! Исправьте  формулу: =ЕСЛИ(Н3<$C$13;100;0) и скопируйте ее. Попробуйте одному из сотрудников, который не  получает материальную помощь резко уменьшить тарифную ставку. Обратите внимание, что изменилось   среднее значение и, возможно, значения выплачиваемых дотаций. Задание 3: будем платить материальную помощь в размере 1% от размера заработной платы. Введите  новую формулу самостоятельно.  Если бросить беглый взгляд на нашу таблицу, очень трудно определить, кто из сотрудников получает  зарплату больше, а кто меньше. Для наглядного отображения данных, для облегчения восприятия, анализа и сравнения числовых данных используются диаграммы. Построение диаграмм Запишите в тетрадь: Диаграмма – это графическое представление данных. В зависимости от области применения  используют различные типы диаграмм. Типы диаграмм: ­ Линейчатая   и   столбчатая   диаграмма   (гистограмма)  –   показывают   изменение   в   течение некоторого периода времени или отражают соотношение величин. ­ Круговая,  кольцевая  – отражают соотношение частей и целого. Можно показать только один ряд значений. ­ График, с областями, поверхность – показывают изменение общего количества в течение периода времени, отображая сумму введенных значений. Диаграмма может быть внедренной на том же рабочем листе, или же ее можно расположить на  отдельном листе. Диаграммы связаны с исходными данными и будут обновляться при обновлении данных на  рабочем листе. Диаграммы могут быть созданы с помощью Мастера диаграмм: Вставка­Диаграмма или кнопка  . Построим диаграмму, отражающую заработную плату наших сотрудников.  Для этого сначала выделим данные, которые нужно отразить в диаграмме. Это графы «Ф.И.О.» и «Сумма  (руб.)» (данные вместе с заголовками). Для выделения несмежных областей удерживайте нажатой  клавишу Ctrl.   Вызовем Мастер диаграмм. Вставка­Диаграмма или кнопка  .  На   первом   шаге   нужно   выбрать   тип   диаграммы.  Какой   тип   диаграммы   нам   больше   подходит? (Гистограмма   или   линейчатая   диаграмма,   т.к.   нам   нужно   отразить   соотношение   величин).  Нажмите Далее.  На   втором   шаге   (источник   данных)   убедитесь,   что   первый   выделенный   столбец   (Ф.И.О.)   считается меткой столбцов, а данные берутся из столбца Н (Сумма (руб.)). Нажмите Далее.  На третьем шаге (параметры диаграммы) обратите внимание на заголовок диаграммы (Сумма (руб.)), а на вкладке «Подписи данных» выберите «категория». Нажмите Далее.  На четвертом шаге (размещение диаграммы) выберите «на имеющемся листе» и нажмите Готово.  Диаграмма построена. На экране одновременно должны быть видны и таблица и диаграмма. В случае необходимости   диаграмму   можно   перенести   или   изменить   ее   пропорции,   т.к.   диаграмма   –   это графический объект.  Диаграмму можно редактировать, изменять ее внешний вид и т.п.  Поработайте над этим, чтобы привести диаграмму к красивому и удобному виду. Добиться этого можно,  щелкая по любому из элементов диаграммы (сами значения, подписи данных, линии сетки, оси, область  построения диаграммы и проч.). ВОЗМОЖНЫЕ ВАРИАНТЫ ЗАДАНИЙ. 1. Составить таблицу расчета с клиентами в комиссионном магазине по следующей форме: A Наименование B Цена C D Кол­во Стоимость E 20% комиссионных (магазину) F Сумма к выдаче (клиенту) Таблица должна содержать 5­10 наименований товаров, а также итоговые суммы по графам D,E,F. 2. Составить таблицу расчета заработной платы бригады рабочих из 10 человек по следующей форме: A B Ф.И.О. Тариф ставка C Отраб. время D Начис­ лено E Подохо дный налог F Профс оюз. взносы G Сумма к выдаче В конце таблицы должны быть итоговые суммы по графам D, E, F, G. Подоходный   налог   рассчитывается   –   13%,   Профсоюзные   взносы   –   10%   от начисленной з/платы  4. С помощью электронных таблиц найти: а) корень третьей степени из 5, б) корень пятой степени из 18 с точностью 0.01. (т.е. нам надо найти методом подбора такое X, что X^3 = 5). 3.   Составить   "шпаргалку"   для   продавца   мороженного,   если   у   него   в   продаже имеются: эскимо по цене ..., пломбир ... и т.д.