Составить и оформить документ Excel согласно индивидуальному варианту.
Лабораторная работа №5.
Пользовательские форматы ячеек
Цели работы:
1. Изучить возможности создания формата пользователя.
2. Научиться применять формат пользователя для оформления табличных документов.
Краткие теоретические сведения
 |
Чаще всего для указания вида числового значения в ячейке с целью ее последующего вывода используется один формат для всех числовых значений, который устанавливается из диалогового окна Формат ячейки. Реже используются форматы для диапазонов числовых значений однойячейки, например в формате можно указать выделение красным цветом шрифта отрицательных значений. Такой формат называется пользовательским. Образцы этого формата находятся в списке Числовые форматы (Все форматы) диалогового окна Формат ячейки. Иначе формат может быть набран пользователем в строке ввода Тип этого окна (см.рис.1).
Рис.1. Вид окна «Формат ячейки» при задании формата пользователя.
Ввод пользовательского формата производится с помощью окна Формат ячейки, вкладки «Числовой формат», в которой из списка «Числовые форматы» выбирается строка «Все форматы». В поле ввода числового формата вводится строка, задающая формат (рис.1).
Пользовательский формат может содержать до четырех частей, разделенных точкой с запятой.
Одна часть: она используется для оформления любого числового значения ячейки (самый часто используемый вариант).
|
|
|
Две части: первая - для вывода положительных и нулевых значений, вторая - для вывода отрицательных, например # ##0_р_.;[Красный]-# ##0_р_. (отрицательные - красным шрифтом).
Три части: первая - для положительных, вторая – для нулевых, третья – для отрицательных, например .# ##0_р_.;[Красный]-# ##0_р_.;[Синий]0(отрицательные - красным шрифтом, ноль - синим).
Четыре части: последняя – для текстового значения, остальные по аналогии с предыдущим. Такой формат используется для фиксации ошибочно набранного текста вместо числа.
| Часть 1 | Часть 2 | Часть 3 | Часть 4 |
| Положительное; | Отрицательное; | Нулевое; | Текстовое |
Указанное деление числовых значений на интервалы заданы по умолчанию. При необходимости в пользовательском формате можно менять границы интервала, указывая их неравенством, но нельзя менять количество интервалов.
Особенности ввода формата: если отсутствует промежуточная часть, то вместо нее вводится точка с запятой.
Правила формирования формата пользователя.
Десятичная запятая и значащие цифры. Если дробная часть числа содержит цифр больше, чем формат содержит шаблонов, число округляется так, чтобы количество разрядов соответствовало количеству шаблонов. Если же целая часть числа содержит цифр больше, чем формат содержит шаблонов, отображаются все значащие разряды. Если в целой части числа содержатся только знаки #, то числа, меньшие 1, начинаются с десятичной запятой. Шаблоны:
|
|
|
# -выводятся только значащие цифры числа, незначащие нули не отображаются. Количество значащих цифр в дробной части равно количеству # после занятой, в целой части одна # позволяет отображать все значащие цифры.
0 – незначащие нули отображаются, если количество разрядов числа меньше количества нулей в формате.
? – вместо незначащих нулей отображаются пробелы. Этим в числах достигается выравнивание положения десятичной запятой при форматировании или выравнивание символа деления в дробях с различным количеством цифр. При использовании ? в формате нельзя использовать шаблоны 0 и #.
, - десятичная запятая (разделитель целой и дробной частей ) внутри числа.
“” – используются для включения в формат любых символов – символьной компоненты, например единицы изменения вводимой величины. При этом единица измерения выводится в ячейку, а сами кавычки нет. По краям формата кавычки можно не использовать.
|
|
|
| Формат | Введенное значение | |||
| Отображаемое в ячейке по формату | ||||
| ####,# | 1234,58 | 0,631 | 12 | 1234,568 |
| 1234,6 | ,6 | 12, | 1234,6 | |
| #,000 | 8,9 | 8 | 0,5 | 45,1235 |
| 8,900 | 8,000 | ,500 | 45,124 | |
| 0,# | 0,456 | 89 | ,39 | 789,236 |
| 0,5 | 89, | 0,4 | 789,2 | |
| #,0# | 12 | 1234,568 | 12,50 | 10,00 |
| 12,0 | 1234,57 | 12,5 | 10,0 | |
| ???,??? | 2,44 | 12,5 | 141 | 1245,2346 |
| _ _2,44_ | _12,5_ _ | 141,_ _ _ | 1245,235 | |
| # ???/??? | 5,25 | 5,3 | ||
| 5 1/4 | 5 3/10 | |||
Разделитель разрядов. Чтобы разделить пробелами разряды числа по три, а также представлять число в масштабе тысяч, в формат включаются пробелы, например 12000 в виде 12 000 – формат # ###; 12000 в виде 12 – формат # (пробел).
Для задания цвета шрифта нужно включить в формат название цвета в квадратных скобках: Черный, Синий, Голубой, Зеленый, Розовый, Красный, Белый, Желтый.
Например:[Черный];[Зеленый];[Голубой];[Красный]
Тогда положительное значение будет выведено черным цветом, отрицательное – зеленым, нулевое – голубым, текстовое – красным (см выше значения частей формата пользователя).
Для изменения числовых интервалов в формате используется условия в квадратных скобках (см.примеры).
Примеры форматов чисел:
|
|
|
I. Нужно, чтобы:
- положительные значения ячейки выводились со словом Приход,
- отрицательные значения - красным цветом со словом Расход,
- нулевые отображались словом Ничего.
- символьные – словом Ошибка синего цвета.
Тогда желаемый формат будет таким:
“Приход:”#,0р;[Красный]”Расход:”#,0р;”Ничего”;[Синий] ”Ошибка”.
Результаты применения такого формата при вводе различных данных (чисел 5,-3,0 и буквы Х) изображены на рис.1.
| Вводимое значение | -3 | 0 | Х | 5 | y | -75 |
| Вид значения в ячейке | Расход:3,0р | Ничего | Ошибка | Приход: 5,0р | Ошибка | Расход:75,0р |
II. В банке на вклады до 10 тыс. начисляется один процент, на вклады с 10 тыс. до 50 тыс. - другой и от 50 тыс. и выше - третий. Для того, чтобы сразу было заметно, на какую сумму какой процент необходимо начислить, требуется выделять сумму до 10 тыс. черным цветом, с 10 тыс. до 50 тыс.– синим, от 50 тыс. – красным. (Интервалы числовых значений: <10 тыс, с 10 до 50 тыс, >= 50 тыс. Третье ограничение (т.е.>=50) является альтернативным к первым двум и может не указываться.) Поэтому желаемый формат:
[Черный][<10];[Синий][<50];[Красный]
III. Требуется отображать денежные данные в следующем виде:
- сумма менее 1 тыс. руб. выводится с подписью “р” (например, число 34 будет отображено как 34р),
- сумма менее 1 млн. руб. – с подписью “тыс. р”, округленной до тысяч с одним дробным разрядом (число 34567 будет отображено как 34,6 тыс. р),
- другая сумма (т.е. если сумма не менее миллиона) – с подписью “млн. р”, округленной до миллионов с дробным разрядом (число 3456789 будет отображено как 3,5 млн. р).
Тогда желаемый формат будет таким:
[<1000]#”p”;[<1000000]#,0 ”тыс.р”;#,0 ”млн. р”
Особенность! Округление до тысяч осуществляется вводом одного пробела после шаблона формата числа <1000000, до миллионов – двух пробелов после формата числа.
Шаблоны для отображения дат
Д – отображает число даты без ведущего нуля (1 - 31).
ДД – отображает число даты с ведущим нулем (01 - 31).
М – отображает цифры месяца без ведущих нулей (1 - 12).
ММ – отображает цифры месяца с ведущим нулем (01 - 12).
МММ– трехбуквенное название месяца (янв, фев, мар и т.д.)
ММММ – полное название месяца.
ГГ – две младшие цифры года.
ГГГГ – все четыре цифры года.
ДДД – двухбуквенное название дня недели (пн, вт, ср и т.д.)
ДДДД – полное название дня недели.
чч – часы с ведущим нулем (00 – 23)
мм – минуты с ведущим нулем (00 – 59)
сс – секунды с веду3щим нулем (00 – 59)
Примеры форматов дат:
ДД.МММ.ГГГ ч:мм – дата 13.03.03 отображается как 13.мар.2003 0:00.
ДДДД ДД.МММ - дата 14.03.03 отображается как пятница 14.мар.
Пример задания.
Составить таблицу расчета оплаты за квартиру для жильцов четырех квартир. Величины оплаты должны выводиться в денежном формате с разделением на группы разрядов по 3 в различном оформлении: c 200 до 1000 – синим, c 1000 р. – красным, другие – черным с 1 разрядом после запятой.
Исходные данные: площадь каждой квартиры и количество проживающих в ней. Известны также тарифы оплаты за газ и отопление.
Правила расчета:
Оплата = количество жильцов * тариф за газ + площадь квартиры * тариф за отопление.
Планируемый результат работы приведен на рис.1.
Рис.2. Таблица расчета квартплаты с использованием формата пользователя.
Порядок работы
Этап №1. Открыть файл «Квартплата», созданный ранее при выполнении работы №2 (или создать таблицу заново).
Этап №2. Задание формата ячеек.
èРазработать пользовательский формат на основе анализа задания. Диапазон числовых значений разделяется на 3 интервала: >=1000 р., <200р. и остальные. Поэтому формат будет содержать 3 части. Для оформления значения из каждого интервала нужно указать: цвет, сам интервал, вид числа и подпись. Для задания вида числа используем символы # и пробел для отделения разрядов тысяч. Желаемый формат :
| [Красный][>=1000]# ###р.;[Синий][<200]# ###р.;[Черный]0,0р. | |||||||
| Цвет | Диа-пазон | Вид числа | Цвет | Диа-пазон | Вид числа | Цвет | Вид числа |
| Часть 1 | Часть 2 | Часть 3 | |||||
èВыделить диапазон ячеек D6:D9.
èВыбрать пункт меню Формат/Ячейки, вкладку «Число», пункт «Все форматы». Вид окна «Формат ячейки» представлен на рис.1.
èПерейти в поле ввода нового формата, ввести разработанный на этапе два формат, нажать ОК. Вид рабочего листа должен совпадать с рис.2
Этап №3. Сохранить документ под новым именем «Пример лаб4+фамилия».
Индивидуальные задания.
Составить и оформить документ Excel согласно индивидуальному варианту.
2. Сохранить таблицу в созданной ранее папке под названием «Лаб5+Фамилия».
Вариант 1.
Задание: пользуясь возможностями Excel создать таблицу для расчета оплаты проката автомобилей.
Исходные данные: коэффициенты возврата и доплаты, цены часа проката каждого вида автомобиля, даты начала и окончания проката, дата оплаты.
Правила расчета:
Оплачено:
Часов = (оплачено по – взято с)*24
Сумма = оплачено часов * цена часа
Фактически:
Часов = (дата возврата – взято с)*24
Сумма = фактически часы * цена часа
Доплата = (фактически сумма – оплачено сумма)*коэффициент доплаты
Возврат = (оплачено сумма – фактически сумма)*коэффициент возврата
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
 |
Порядок работы
1. Составить таблицу по образцу для расчета проката 10 автомобилей, для которых придумать марки, цену проката часа, даты проката.
2. Заполнить первую строку формулами для расчета согласно заданным правилам. Формула должны использовать абсолютную и относительную адресацию.
3. Проверить правильность расчета для первой машины.
4. Размножить формулу по ячейкам остальных машин.
5. Задать пользовательский формат ячеек столбцов «Доплата» и «Возврат» так, чтобы при положительном значении сумма выводилась с подписью «р.», округленная до целого, при остальных значениях – выводилось слово «Ничего».
6. Задать пользовательский формат ячеек столбцов «Взято с:», «Оплачено по», «Дата возврата» так чтобы в ячейках выводилась дата (без указания года) и время.
7. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.
Вариант 2.
Задание: пользуясь возможностями Excel создать таблицу для расчета оплаты за проживание в гостинице.
Исходные данные: цены мест в номерах, даты заезда и выезда, дата оплаты.
Правила расчета:
Оплата:
Сумма = цена места * (оплата По – дата заезда)
Доплата:
Дней = дата выезда – оплата По
Сумма = цена места * доплата дней
Возврат:
Дней = дата выезда – оплата По
Сумма = цена места * возврат дней
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
Порядок работы
1. Составить таблицу по образцу для расчета оплаты проживания 8 человек, для которых придумать фамилии, номера, цену места, даты.
2. Заполнить первую строку формулами для расчета согласно заданным правилам.
3. Проверить правильность расчета для первого человека.
4. Размножить формулу по ячейкам остальных машин.
5. Задать пользовательский формат ячеек столбцов «Доплата» так, чтобы:
a.В столбце «дней» при получении положительной величины значение выводилось красным цветом с подписью «Доплата за …дн.». При получении других значение – не выводится ничего (пустые кавычки в формате).
b.В столбце «сумма» при получении положительной величины значение выводилось красным цветом с подписью «р.». При получении других значение – выводился пробел (см.внешний вид листа).
6. Задать пользовательский формат ячеек столбцов «Возврат» так, чтобы:
a.В столбце «дней» при получении отрицательной величины значение выводилось синим цветом с подписью «Возврат за …дн.». При получении других значение – выводился пробел (см.внешний вид листа).
b.В столбце «сумма» при получении отрицательной величины значение выводилось синим цветом с подписью «р.». При получении других значение – выводился пробел (см.внешний вид листа).
7. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.
Вариант 3.
Задание: пользуясь возможностями Excel создать таблицу для расчета товарных запасов на складе для 10 товаров. Товары могут быть 1,2,3-го сорта и просроченные. Цена товара 2 и 3 сорта меньше цены товара 1 сорта на определенный процент. Просроченные товары имеют цену 10% от цены 1 сорта.
Исходные данные: цены за 1 кг первого сорта каждого товара, скидки на 2 и 3 сорт от цены 1-го сорта, вес товаров 1,2,3-го сортов и просроченных.
Правила расчета:
Стоимость всего = СУММ (единиц 1 сорта * цена 1 сорта; единиц 2 сорта * цена 2 сорта; единиц 3 сорта * цена 3 сорта; просрочено * цена просроченного товара)
Состояние запасов = стоимость всего
В формуле Стоимость всего:
Цена 2 сорта = Цена 1 сорта – Цена 1 сорта * скидка 2 сорт
Цена 3 сорта = Цена 1 сорта – Цена 1 сорта * скидка 3 сорт
Цена просроченного товара = Цена 1 сорта * 10%
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
Порядок работы
1. Составить таблицы по образцу для расчета товарных запасов на складе для 10 товаров, для которых придумать названия, цену 1 сорта, число единиц 1,2,3 сортов и просрочено.
2. Заполнить первую строку столбцов «Стоимость всего» и «Состояние запасов» формулами согласно заданным правилам, в которых использовать относительную и абсолютную адресацию.
3. Проверить правильность расчета для первого товара и затем размножить формулу по ячейкам остальных товаров.
4. Вычислить сумму столбца «Стоимость всего» с помощью автосуммы.
5. Задать пользовательский формат ячеек столбцов «Число единиц» как показано на рис.
6. Задать пользовательский формат ячеек столбца «Стоимость всего» так, чтобы:
a.Величина больше 100 000 выводилась синим цветом с разделением разрядов числа на группы по 3.
b.Величина больше 0 – черным цветом с разделением разрядов числа на группы по 3.
c. В остальных случаях – 0 красным цветом.
7. Задать пользовательский формат ячеек столбца «Состояние запасов» так, чтобы:
a.При получении величины >100 000 выводилось слово «Избыток» синим цветом.
b.При получении величины > 0 – фраза «В наличии» черным цветом.
c. В остальных случаях – слово «Нет» красным цветом.
8. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.
Вариант 4.
Задание: пользуясь возможностями Excel, создать таблицу для расчета оплаты за доставку 10 видов товара при наличной (нал) и безналичной (безнал) формах оплаты. При доставке существуют льготы: при цене партии больше 50 000р. бесплатная доставка по Московской области, при цене больше 10 000р., но меньше 50 000р. бесплатная доставка по Моске, иначе доставка без льгот.
Исходные данные: количество товара, цена за штуку, наценка за безналичный расчет, сумма оплаты наличными.
Правила расчета:
Цена партии = кол-во * цена
Состояние запасов = стоимость всего
Оплата безнал = цена партии – оплата нал + (цена партии – оплата нал) * наценка за без/нал
Доставка = оплата нал + оплата безнал
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
Порядок работы
1. Составить таблицы по образцу для расчета оплаты доставки для 10 товаров, для которых придумать названия, кол-во, цену за штуку, оплату наличными.
2. Заполнить первую строку формулами согласно заданным правилам, в которых использовать относительную и абсолютную адресацию.
3. Проверить правильность расчета для первого товара и затем размножить формулу по ячейкам остальных товаров.
4. Вычислить данные строки «ВСЕГО:» как суммы соответствующих столбцов.
5. Задать пользовательский формат ячеек столбца «Цена партии» так, чтобы
a.Величина больше 50 000 выводилась красным цветом с одним разрядом после запятой и подписью «р.».
b.Величина больше 10 000, но меньше 50 000 – синим цветом с одним разрядом после запятой и подписью «р.».
c. В других случаях – черным цветом с одним разрядом после запятой и подписью «р.».
6. Задать пользовательский формат ячеек столбца «Доставка» так, чтобы:
a.При получении величины >50 000 выводилась красным цветом фраза «Моск.обл:», затем сумма с одним разрядом после запятой и подписью «р.».
b.Иначе, при получении величины > 10 000 – синим цветом слово «Москва:» и сумма с одним разрядом после запятой и подписью «р.».
c. В остальных случаях – черным цветом фраза «Без льгот:» и сумма с одним разрядом после запятой и подписью «р.».
7. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.
Вариант 5.
Задание: пользуясь возможностями Excel, создать таблицу для расчета с клиентами за купленный им товар. Оплата может производиться 2 частями: обязательная часть при покупке и вторая - позднее. За каждый день отсрочки начисляется наценка.
Исходные данные: стоимость партии товара, наценка за день, процент обязательной предоплаты, предоплата фактическая.
Правила расчета:
Предоплата обязательная = стоимость партии * процент обязательной предоплаты
Остаток = (стоимость партии – фактическая предоплата) + (стоимость партии – фактическая предоплата) * дни * наценка за день
Столбец ВСЕГО = оплата остаток + фактическая предоплата
ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА
Порядок работы
1. Составить таблицы по образцу для расчета оплаты 10 партий товаров, для которых придумать названия, стоимость, фактическую предоплату, дни отсрочки.
2. Заполнить первую строку формулами согласно заданным правилам, в которых использовать относительную и абсолютную адресацию.
3. Проверить правильность расчета для первого товара и затем размножить формулу по ячейкам остальных товаров.
4. Вычислить данные строки «ВСЕГО:» как суммы соответствующих столбцов.
5. Задать пользовательский формат ячеек столбцов «ВСЕГО:», «Обязат», «Фактич» так, чтобы:
a.сумма менее 1 тыс. руб. выводится с подписью “р” (например, 500 будет отображено как 500р).
b.сумма менее 1 млн. руб. – с подписью “тыс. р”, округленной до тысяч с одним дробным разрядом (число 34567 - как 34,6 тыс. р),
c. другая сумма (т.е. если сумма не менее миллиона) – с подписью “млн. р”, округленной до миллионов с дробным разрядом (число 3456789 - как 3,5 млн. р).
6. Задать пользовательский формат ячеек столбца «Дни» так, чтобы величина больше 10 дней выводилась красным, меньше 0 – не выводилось ничего («»), другие – синим.
7. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.
Дата добавления: 2018-06-27; просмотров: 1822; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!