Форматирование положительных, отрицательных, нулевых и текстовых значений

Пользовательские форматы могут содержать до четырех секций кодов, отделенных точками с запятой:

 

Положительный формат;Отрицательный формат;Нулевой формат; Текстовый формат

 

Если пользовательский формат содержит только одну секцию, то EXCEL применяет этот формат к положительным, отрицательным и нулевым значениям. Если пользовательский формат содержит две секции, то первая применяется к положительным и нулевым, а вторая - только к отрицательным.

Например, задан формат:

"К получению:"# ##0,0;"К выдаче" # ##0,0;"Нет движения";"Обратите внимание: "@

Таблица показывает, как действует формат на различные значения в ячейках:

Введенное значение	Отображаемое значение
12,89	К получению: 12,89
- 12,89	К выдаче: 12,89
	Нет движения
Это не счет	Обратите внимание: Это не счет

Включение цвета в форматы

Можно назначить различный цвет значений ячеек в зависимости от знака числа

Например, задан формат: [Синий] 0 000,0; 0 000,0

Когда вы примените этот формат к листу, положительные и нулевые значения будут выделены синим цветом, а текстовые и отрицательные останутся по умолчанию черными.

Можно управлять выводом чисел в зависимости от сравниваемых значений.

Например, задан формат: [Синий][>5000]0 000,0; [Красный][<0]0 000,0;0 000,0.

Применив данный формат к листу, числа, значения которых больше 5000, будут выводиться синим цветом, отрицательные — красным цветом, все другие значения будут выведены по умолчанию черным цветом.

 

 

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ 2

 

1. Запустить табличный процессор EXCEL.

2. Открыть таблицу, созданную при выполнении лабораторной работы №1.

3. Переименовать Лист1 рабочей книги в лист “Баланс”.

4. Перейти на Лист2 рабочей книги, дать ему имя “Ведомость”. Создать таблицу по образцу (рис.2.1).

5. С помощью режима Настройка панели быстрого доступа (контекстное меню в области панели быстрого доступа) настроить панель, включив в нее кнопки: Уменьшение/Увеличение шрифта, На одну страницу вверх, Макросы.

6. Отформатировать шапку таблицы, используя пункт меню Формат / Ячейка... и кнопки созданной панели инструментов.

 

 

Рисунок 2.1 - Лист рабочей книги “Ведомость”.

 

7. Для форматирования данных таблицы создать пользовательские числовые форматы (Формат ячеек/Число/Числовые форматы/Дополнительно(все форматы) в соответствии со следующими требованиями:

- 1-й столбец. При вводе числа меньше 10 перед числом должен выводиться 0;

- 2-й столбец. При вводе кода материала (например, А) в ячейке выводится текст “Материал А”;

- 3-й столбец. Заполнить ячейки с помощью операции автозаполнения данных;

- 4-й столбец. При вводе числа в ячейку должен происходить анализ количества его разрядов (если число больше 999999, то оно 7-и разрядное и более; если число больше 999 – 4-х разрядное и более) и, в зависимости от результата анализа, число должно округляться до миллиона, тысячи, либо не округляться. Например, число 123456 больше 999 и поэтому округляется до тысяч (результат в ячейке - 123,456 тыс.); число 1000000 больше 999999 и округляется до миллионов (результат в ячейке -1 млн.);

- 5-й столбец. При отображении введенного в ячейку числа должен вставляться пробел - разделитель перед каждой тройкой цифр. Числа, которые больше или меньше заданного интервала значений, должны выделяться разными цветами (выбор интервала значений произвольный);

- 6-й столбец. При вводе числа, кроме пробела-разделителя, должен выводиться знак $. Предусмотреть цветовые различия выводимых чисел.

- 7-й столбец. Если вводимое в ячейку данного столбца число положительно, то ему должно предшествовать сообщение «Приход», если оно отрицательно, – то «Расход».

Для выполнения пункта 7 лабораторной работы использовать справочный материал.

8. Скопировать первые четыре столбца таблицы на свободную часть рабочего листа.

9. Удалить вторую и третью строку скопированного фрагмента, а затем отредактировать полученную таблицу так, чтобы в ней остались данные о материалах, цена за единицу которых превышает 1 миллион гривен (Контекстное меню/Удалить).

10. Скопировать с помощью мыши заголовок основной таблицы на место заголовка полученного фрагмента. Отредактировать его, добавив информацию о цене выбранных материалов (более 1 млн.)

11. После столбца «Цена за единицу» вставить столбец «Количество товара», заполнив его произвольными данными.

12. Отредактировать данные столбца «Сумма в гривнах», рассчитав их значения по формуле «Сумма в гривнах»= «Цена за единицу»* «Количество товара».

13. Рассчитать значение «Остаток на 1.10»=«Остаток на 1.09» + «Обороты за месяц», добавив в ранее созданную таблицу соответствующий столбец.

14. На этом же листе рассчитать сумму “Итого” по столбцам 5, 6, 7 и среднее значение по столбцу 4.

15. Изучить ПараметрыExcel.

16. Сохранить файл.

 

контрольные вопросы к защите лабораторной работы 2

 

1. Дайте определение рабочей книги EXCEL, опишите ее структуру.

2. Как в рабочей книге осуществляется переход с листа на лист?

3. Определите назначение и порядок переименования листов рабочей книги.

4. Охарактеризуйте созданные Вами пользовательские форматы по всем столбцам таблицы.

5. Перечислите назначение основных символов, используемых при создании пользовательских форматов.

6. Опишите состав и назначение основных секций кодов числовых форматов.

7. Определите порядок включения в пользовательский числовой формат текста, цветовых различий, анализа значения числа, вводимого в ячейку.

8. Укажите назначение и отличие операций перемещения и копирования (внутри листа и с листа на лист рабочей книги).

9. Укажите порядок выполнения операции копирования/перемещения (с помощью пунктов меню, кнопок панели инструментов и мыши).

10. Опишите процедуру создания и настройки панелей инструментов.

11. Укажите порядок удаления строк, столбцов, ячеек таблицы.

12. Перечислите способы редактирования содержимого ячейки.

13. Определите порядок действий и исходное местоположение курсора при добавлении строк и столбцов в таблицу.

14. Укажите возможности выполнения следующих действий:

a) включить/выключить строку формул, строку состояния, сетку, заголовки строк/столбцов, горизонтальную /вертикальную полосу прокрутки, ярлычки листов; показать в ячейках формулы (или их значения);

b) включить/выключить режим автоматического пересчета.

 

ЛабораторнаЯ рАбота №3

 

Тема РАБОТЫ: Использование абсолютных, относительных и смешанных ссылок. Организация связей ячеек рабочей книги

ЦЕЛЬ РАБОТЫ: Получение практических навыков работы с абсолютными, относительными и смешанными адресами ячеек в формулах. Ссылки на другие рабочие листы

ПРОДОЛЖИТЕЛЬНОСТЬ: 2 часа.

 

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

 

Формулы в Excel всегда начинаются со знака равенства (=) и могут включать числовые и буквенные величины (константы), знаки арифметических операций, операций сравнения, операций с текстом, скобки, данные ячеек и имена, а также встроенные формулы, называемые функциями. Например, чтобы получить результат перемножения содержимого ячеек D4 и N16, следует ввести: = D4*N16.

Можно показать на рабочем листе формулы, а не их результаты, если выполнить щелчок по кнопке «OFFICE» (в верхнем левом углу окна Microsoft Excel ) и выбрать Параметры Excel\ вкладка Дополнительно. В группе Показать параметры для следующего листа включить Показывать формулы, а не их значения(или выполнить комбинацию клавишCtrl+~, где второй знак – это клавиша с изображением ~ «тильды» и ` «тупого ударения», вверху слева).

Копирование формул осуществляется:

– с применением кнопок Копировать - Вставить на панели инструментов;

– с использованием комбинации клавиш <Ctrl+C> и <Ctrl+V>;

– с помощью команд контекстного и верхнего меню;

– с использованием маркера заполнения (выделить ячейку, содержащую формулу, перетащить маркер заполнения, находящийся в нижнем правом углу ячейки, на примыкающий диапазон);

– с использованием на панели быстрого доступа команд Главная \ Копировать и Вставить \ Специальная вставка\ переключатель Вставить установить в положение формулы.

По умолчанию для указания адресов ячеек в Excel применяются относительные ссылки вида D5, C7, M9 и др. Это означает, что ссылки на ячейки изменяются при копировании формулы на новое место. После копирования формул относительные ссылки будут указывать на другие ячейки так, чтобы сохранить прежние отношения в соответствии с новым местоположением.

Иногда изменения ссылок на ячейки при копировании формул является нежелательным. Чтобы избежать изменения ссылок при копировании формулы необходимо применять абсолютные ссылки, которые обозначаются с использованием знака доллара перед заголовком столбца и перед заголовком строки: $ D$5, $C$7, $M$9. Абсолютная ссылка – это точный адрес ячейки в формуле, ссылающийся на данную ячейку независимо от положения ячейки с формулой.

Для того, чтобы ссылку представить как абсолютную, следует:

1. В режиме ввода формулы указать ячейку. Появится относительная ссылка на эту ячейку. Курсор не переустанавливать на ссылку.

2. Нажимать клавишу F4 до тех пор, пока не появится правильная комбинация знаков доллара и заголовков столбцов и строк.

Можно воспользоваться клавишей F4 и для редактирования ссылок в существующей формуле.

При копировании формулы абсолютные ссылки не изменяются.

В случаях, когда нужно, чтобы при копировании изменялась только строка или только столбец, используют смешанную ссылку, которая содержит как абсолютные, так и относительные ссылки. Например, ссылка D$5, а ссылка $D5 указывает на то, что при копировании формулы не изменяется столбец. Создаются смешанные ссылки тем же способом, что и абсолютные, т.е. клавиша F4 нажимается необходимое число раз.

В формуле можно сослаться на другие листы рабочей книги путем включения в формулу ссылки на лист. Например, чтобы сослаться на ячейку D7 листа Баланс, нужно ввести в формулу ссылку вида Баланс!D7. Если имя листа содержит пробелы, нужно заключить ссылку на лист в одиночные кавычки.

Можно воспользоваться мышью для ввода ссылки на другие листы рабочей книги. Для этого следует:

– начать вводить формулу в ту ячейку, где должен появиться результат;

– щелкнуть по ярлычку листа, содержащего ячейку или диапазон, на который нужно сослаться;

– выделить нужную ячейку или диапазон. В строке формул появится полная ссылка, включая ссылку на лист. Если имя листа содержит пробелы, Excel заключит ссылку на лист в одиночные кавычки

– закончить ввод формулы и нажать Enter.

С помощью связей можно передавать от одного рабочего листа другому или от одной рабочей книги другой любые данные, числа и текст, которые затем могут быть использованы в формулах. Можно установить связь с одной ячейкой, диапазоном ячеек, диапазоном рабочих листов. Связанные данные могут быть отформатированы так же, как и любые другие данные в других ячейках рабочего листа.

Чтобы связать ячейку или диапазон ячеек вручную, необходимо:

1) активизировать рабочий лист, в котором устанавливается связь с данными;

2) выделить ячейку, в которую требуется установить связь;

3) поставить в ней знак равенства;

4) перейти на лист, с ячейками которого устанавливается связь (на лист-источник);

5) выделить связываемую ячейку. Она активизируется бегущей рамкой.

6) нажать клавишу Enter. В выделенной ячейке листа-приемника появится ссылка, имеющая относительный адрес.

Процедура защиты рабочего листа и его содержимого включает две команды. Первая команда форматирует ячейки или объекты, с которых нужно снять защиту. Вторая команда включает защиту листа или всей рабочей книги.

Форматы по умолчанию для всех ячеек – защищаемые и показываемые. Следуя приведенным ниже инструкциям, можно отформатировать часть ячеек так, чтобы они оставались доступны для редактирования при включенной защите листа или рабочей книги. Защита листа или рабочей книги от редактирования, а также скрытие формул от просмотра, включается и выключается с помощью команды Рецензирование\ группа Изменения\ Защитить лист.

Порядок включения защиты (лаб.раб.4, пункт 7):