ЛР 6 создаине таблиц. Методические указания. Основные параметры
 Скачать 1.32 Mb.
|
|
лаборатрная РАБОТА № 6 Создание таблиц и их форматирование. Формулы и функции в Excel. Методические указания. Основные параметры Excel – программа, которая предназначена для работы с таблицами. Каждая таблица состоит из некоторого количества строк и столбцов; на пересечении строки и столбца находится ячейка таблицы. Табличный процессор Excel дает возможность анализировать, совместно использовать и обрабатывать данные различного типа для принятия более обоснованных решений. Excel содержит ориентированный на результаты интерфейс, представления сводной таблицы, которые можно легко создавать и использовать средство составления формул, мощные возможности визуализации данных и обеспечивает создание профессионально оформленных диаграмм и таблиц. Главное преимущество программы Excel - возможность мгновенного автоматического пересчета всех данных, связанных формульными отношениями и, тем самым, к обновлению всей таблицы. Основные объекты электронных таблиц это рабочая книга, лист, строка, столбец, ячейка, диапазон ячеек. Итоговый документ Excel – это файл с произвольным именем и расширением *.xls(x), который и называется рабочей книгой. Excel, имеет панель меню, оформленную в виде закладок: Главная, Вставка, Разметка страницы и другие, на которых располагаются команды и инструменты. На каждой закладке размещаются группы инструментов, как и в программе Word, в которых присутствуют возможности вызова диалоговых функциональных окон. То есть, таблицы тоже можно форматировать, применять к ним стили, темы и т. д.  Более поздние версии Excel имеют немного отличающийся интерфейс, но функциональные возможности аналогичные.  Основным элементом электронной таблицы является ячейка (или клетка) таблицы. В каждую ячейку может быть помещена информация различного типа: – текстовая длиной до 32767 символов; – числовая; – дата; – время и пр. Для отображения в ячейках числовой информации, даты и времени могут быть заданы различные форматы. Для форматирования ячейки можно воспользоваться мини-панелью инструментов, которая появляется вместе с контекстным меню при щелчке правой кнопкой мыши на ячейке таблицы. Ячейка электронной таблицы по умолчанию имеет стиль Обычный. Стиль таблицы можно изменять с помощью Галереи стилей. Можно создать новый стиль, если для ячейки задать новые свойства. В ячейках можно разместить текст достаточно большого объема. Если текст не входит в ячейку, то можно выполнить следующие действия: – раздвинуть границы ячеек по горизонтали, встав курсором на границу между буквами столбцов (широкий крест курсора превращается в черный крестик со стрелками) и, удерживая нажатой левую клавишу мыши, сдвинуть границу на требуемое расстояние; – объединить несколько ячеек и в них записать текст. В работе с электронными таблицами можно выделить три основных представления данных: число, текст и формула. В зависимости от решаемой задачи возникает необходимость применять различные форматы (типы) представления числовых данных, например, числа, дата, процент, текст и т.д. Текстом в электронных таблицах является последовательность символов, состоящая из букв, цифр и пробелов. Формула начинается со знака «=» и включает в себя числа, имена ячеек, знаки арифметических операции, скобки, функции. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат, а сама формула отображается в строке формул. Формирование таблиц. Этапы формирования таблиц можно разделить на три шага: Создание «шапки» таблицы. Для этого применяются обычные подходы и приемы, используются инструменты объединения и разбиения ячеек, применяется форматирование текстовых заголовков, формирования границ таблицы.  Занесение данных в ячейки таблицы. На этом этапе производится настройка ячеек и устанавливается формат (тип) ячеек. Окно формата ячеек имеет ряд вкладок, в которых устанавливаются форматы данных и их оформление.   Применение расчетов и построения графиков. При этом применяются заполнения столбцов и строк с задействованием стандартных средств сортировка или обработки данных, используются «протяжки», формируются собственные расчётные формулы. Пример таблицы:  В программе Excel доступны все Мастера, как и в документах. Объекты, получаемые в результате применения Мастеров, размещаются в одной или нескольких ячейках.Таким образом, в таблицах можно разместить рисунки, графики, форматированные тексты, формулы, фигуры.К готовым таблицам можно променять стили, можно даже выбрать стиль конкретной ячейки  С другими возможностями и особенностями познакомимся в процессе выполнения заданий. Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака "=". Если в ячейке написать просто "1+1", Excel не будет вычислять это выражение. Однако, если написать "=1+1" и нажать Enter, в ячейке появится результат вычисления выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter. В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки. Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически. Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой-либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывает ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения Использование текста в формулах Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки. Если написать формулу «=мама», Excel выдаст ошибку, а если написать «="мама"» — все ок, корректная формула. Операторы ссылок : (двоеточие). Ставится между ссылками на первую и последнюю ячейку диапазона. Такое сочетание является ссылкой на диапазон (A1:A15); ; (точка с запятой). Объединяет несколько ссылок в одну ссылку (СУММ (A1:A15;B1:B15)); (пробел). Оператор пересечения множеств. Служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8). Выражения Выражения в Excel бывают арифметические и логические. Арифметическое выражение (например, «=2*(2+5)», результат — 14) в результате дает числовое значение (положительное, отрицательное, дробное число). Логическое выражение (например, «=3>5», результат — логическое значение «ЛОЖЬ»)в результате может дать лишь 2 значения: «ЛОЖЬ» или «ИСТИНА» (одно число либо больше другого, либо не больше, других вариантов нет). Функции в Microsoft Excel В формулах Microsoft Excel можно использовать функции. Сам термин «функция» здесь используется в том же значении, что и «функция» в программировании. Функция представляет собой готовый блок (кода), предназначенный для решения каких-то задач. Все функции в Excel характеризуются: Названием; Предназначением (что, собственно, она делает); Количеством аргументов (параметров); Типом аргументов (параметров); Типом возвращаемого значения. В качестве примера разберем функцию «СТЕПЕНЬ» Название: СТЕПЕНЬ; Предназначение: возводит указанное число в указанную степень; Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!); Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые; Тип возвращаемого значения: число — результат возведения в степень. Синтаксис записи функции Как вы видите, чтобы Excel не выдал ошибку, функция должна соответствовать определенному набору правил. Этот набор правил называется синтаксис записи функции. Общий синтаксис записи любой функции в Excel: имя_функции([аргумент_1; аргумент_2; … ; аргумент_N]) Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть. Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячейку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку! Некоторые функции принимают РОВНО ОДИН аргумент. Например, функции sin(число), cos(число) и т. п. Некоторые функции принимают больше, чем один аргумент. В таком случае аргументы разделяются между собой точкой с запятой «;». В общем случае, аргументами функции могут быть константы (числа, введенные вручную), ссылки на ячейки, ссылки на диапазон ячеек, именованные ссылки и другие функции (вложенные функции). Ввод функций вручную Для набора простейших формул, содержащий функции, можно не пользоваться специальными средствами, а просто писать их вручную (см. рис. выше). Однако, этот способ плохо подходит для набора длинных формул, таких, как на рис. ниже.  К недостаткам набора формул вручную можно отнести: Руками ставить «=»; набирать имя функции; открывать/закрывать круглые скобки; расставлять точки с запятой; следить за порядком вложенности заключать текст в двойные кавычки; не иметь возможность посмотреть промежуточные расчеты; и т. п. Слишком большая вероятность допустить ошибку, набирая вручную сложные и длинные формулы, и на это уходит много времени. Ввод функции с помощью кнопки  "сигма"Одно из средств облегчить и ускорить работы с функциями — кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:СУММ (минимум один, максимум 30 аргументов). Суммирует свои аргументы. Полезный совет: Чтобы быстро узнать сумму значений в диапазоне, выделяем его и смотрим на строку состояния — там должна отображаться сумма; СРЗНАЧ (минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов; СЧЁТ (минимум один, максимум 30 аргументов). Подсчитывает количество чисел в списке аргументов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются); МАКС (минимум один, максимум 30 аргументов). Возвращает максимальный аргумент; МИН (минимум один, максимум 30 аргументов). Возвращает минимальный аргумент.   Принцип работы: активизируем ячейку, где должен быть результат (просто щелкаем); Нажимаем на стрелочку справа от кнопки "сигма"; Выбираем нужную функцию. После выбора Excel сам вставит знак «=», имя функции, круглые скобки, и даже попытается угадать диапазон, который мы ходим выделить (хотя он редко угадывает); Выделяем связный диапазон ячеек. Excel вставит в круглые скобки адрес выделенного диапазона; Если нужно, например, просуммировать числа из несвязных диапазонов, зажимаем Ctrl, и выделяем нужное количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на другой диапазон; Когда выделили все нужные диапазоны, для завершения нажимаем Enter; В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок. Задания к практической работе Формирование таблиц и форматирование. Задание 1: Создание списка клиентов Создайте рабочую книгу и сохраните ее в своей папке под именем Заказы_(Фамилия). Первому листу присвойте имя Клиенты. Для этого 2 раза щелкните на ярлык Лист1 и переименуйте его в Клиенты. Введите в ячейку А1 - заголовок первого столбца – Название фирмы. Затем в ячейки В1-I1 ввести следующие заголовки: B1– Код- (любое четырехзначное число, у каждой фирмы свой код, т.е. нет двух одинаковых кодов) C1– Контактная персона-(представитель фирмы, ответственный за контакты с клиентами: фамилия И.О.) D1– Индекс E1– Город F1– Улица G1– Телефакс H1– Телефон I1– Скидка (%)-(указывается скидка в числовом выражении, т.е. 0,15 или 0,3 и т.д. )  Отформатируйте ячейки заголовков, чтобы поместилось название с помощью переноса в ячейке через окно Формат ячеек. Ввод записей. Для этого находясь в следующей строке заполненной области выбрать в Панели быстрого доступа кнопку Форма. Если её нет, добавьте с помощью настройка параметров. Нажмите ОК. Откроется диалоговое окно формы данных Клиенты.  Введите список 14 фирм. Фирмы распределите по 5 городам. Набрав первую запись нажмите на кнопку Добавить. Форматирование таблицы. Для ячеек I2-I14 задайте процентный стиль (для этого выделите данный диапазон и нажмите на кнопку Процентный формат на панели инструментов Форматирование). Применив стиль к таблице получите следующий вид:  Сортировка данных. Необходимо выбрать на вкладке Данные Сортировка. В диалоговом окне выбрать первый критерий сортировки Код и второй критерий Город и ОК. Фильтрация данных. Выбрать на вкладке Данные Фильтр/Автофильтр. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появиться кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите название одного из городов в Город. Кроме значений полей, каждый список содержит еще три элемента: (Все), (Первые 10…) и (Условие…). Элемент (Все) предназначен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10…) обеспечивает автоматическое представление на экране десяти первых записей списка. Если вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой функцией. Последний элемент - используется для формирования более сложного критерия отбора, в котором можно применить условные операторы И и ИЛИ. Установите курсор в любую заполненную ячейку и выполните следующие действия: в меню Формат Автоформат Список 2. Задание 2: Создание списка товаров Второй список будет содержать данные о предлагаемых нами товарах. Перейдите на второй лист рабочей книги, щелкнув на ярлычке Лист2, и присвойте ему имя Товары. Список, создаваемы на рабочем листе Товары, должен состоять из трех столбцов: Номер, Наименование товара и Цена. Введите указанные имена в ячейки А1-С1. Теперь приступаем к вводу данных. Желательно, чтобы номера товаров были расположены по возрастанию.  Цены должны вводиться в таком виде: 1150, 200 или 3000. Затем диапазон С2-С12 форматируем следующим образом на кладке Главная Число Денежный Обозначение ($Английский(США); число десятичных знаков-0) ОК. Приведите таблицу к следующему виду:  Задание 3. Создайте таблицу для расчета стоимости проката товара  Таблица расчета стоимости проката товара Задание 4. Пользуясь расписанием движения поездов, рассчитайте продолжительность поездки на каждом составе.  Таблица расписания движения поездов Контрольные вопросы для практической работы № 6 Как называется документ в программе Excel? Из чего он состоит? Данные каких типов могут быть записаны в ячейку? Какие значения может принимать содержимое ячейки? Как изменить размер ячейки? Для чего служат команды форматирования ячеек? Как установить необходимый числовой формат ячейки? Как задать фон для ячейки? |