Файл: Методические указания к лабораторным работам Составитель Сухарев В. В. Москва ргу им. А. Н. Косыгина 2018 удк 003. 023.docx

Создание выпадающих списков

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

Внешний вид ячеек, заполняемых с использованием выпадающих списков, приведет на рис. 11



Рисунок 11

При активации ячейки, к которой подключен выпадающий список, справа от ячейки появляется кнопка выбора из списка, при нажатии на которую происходит открытие списка заранее определенных значений. Кроме этого, возможно заполнение ячеек по первым символам, т.е. при нажатии первых символов того значения, которое определено в списке, система автоматически предлагает заполнить этим значением ячейку. Для этого нужно после появления значения нажать клавишу ENTER.

При попытке ввода в ячейку значения, которого нет в списке, будет выдано сообщение об ошибке (рис. 12)



Рисунок 12

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

1. 
   На определенном месте рабочего листа, не попадающего в область рабочей таблицы (или иного листа) ввести значения списка (см. рис 13). Значения списка обязательно должны быть введены в один столбец либо одну строку, иначе в дальнейшем будет выдано сообщение об ошибке.
   

Иванов

Петров

Сидоров

Абрамов

Манукян

Рисунок 13

2. 
   В таблице выделить область, для которой требуется ввести режим заполнения из списка.
   
3. 
   В меню последовательно выбрать пункты «Данные» - «Проверка данных» - «Проверка данных…».
   

---

4. 
   В появившемся окне в пункте «Тип данных» выбрать пункт «Список» и в поле «источник» указать диапазон исходных данных, заполненный в пункте 1. (см. рис 14).
   

Рисунок 14

Для отмены заполнения ячеек значениями из списка нужно повторно вызвать окно настройки вводимых значений и в поле «Тип данных» выбрать пункт «Любые значения».

Работа с данными с других листов и книг

В Excel возможна обработка данных и обращение не только к тем данным, которые расположены на текущем листе, с которым работает пользователь, но и к тем, которые расположены на других листах и даже в других файлах (книгах).

Чтобы прочитать значение ячейки, расположенной на другом листе, перед именем ячейки нужно указать имя листа, поставить знак «!», а затем указать имя ячейки, например Лист1!A1

Если имя листа содержит пробелы, его слудет указывать в апострофах: 'Другой лист'!A1

Возможно указание ячейки, расположенной на другом листе, с использованием мышки. Для этого установите курсор в ту ячейку (позицию формулы), куда требуется подставить значение ячейки, выберите нужный лист, затем требуемую ячейку и нажмите клавишу ENTER.

При переименовании листов Excel автоматически в текущей книге найдет все ссылки на переименованный лист и корректно изменит их с учетом нового имени листа.

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

В том случае, если имя другой книги или ее местоположение меняется, Excel не может самостоятельно изменить некорректные ссылки. В этом случае задача по исправлению таких ссылок лежит на пользователе.

Имя файла указывается как полное имя файла, т.е. имя, начинающееся с имени диска либо сетевого имени. При этом все имя указывается в апострофах, а непосредственно имя файла – в квадратных скобках.

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

Фиксация строк и столбцов

---

Данные таблиц, представленных на экране, не всегда могут быть размещены на одном экране монитора. В этом случае для того, чтобы увидеть столбцы и строки, которые не уместились на экране, осуществляется прокрутка (скроллинг) экрана в нужном направлении (вверх-вниз, вправо-влево).

При этом те ячейки, которые были на экране, исчезают, а те, которых не было – появляются.

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

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

Фиксировать можно только верхние строки таблицы, а также только левые колонки.

Чтобы зафиксировать строки, нужно установить курсор на ту ячейку, строки выше которой должны быть зафиксированы, а при необходимости выбрать тот столбец, перед которым столбцы должны быть зафиксированы и выбрать меню «Вид» - «Закрепить области» - «Закрепить области» (рис 15).



Рисунок 15

Снятие закрепления осуществляется повторным выбором данного пункта

Условное форматирование

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

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

Чтобы задать условное форматирование, нужно выделить диапазон ячеек, для которых задается такое форматирование и на закладке «Главное» выбрать пункт «Условное форматирование» (см. рис 16).



Рисунок 16

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

---

Мы рассмотрим только один пункт данного меню, самый универсальный, «Управление правилами…».

При его выборе откроется меню, представленное на рис. 17.



Рисунок 17

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

В качестве примера мы рассмотрим выделение ячеек таблицы (рис. 18), в которой значения суммы, которые больше 45, будут выделяться зеленым полукурсивом, а те, сумма продаж которых менее 30 тысяч – будут иметь красный фон.

	Товарный отчет
	Наименование	Цена	Количество	Сумма продажи
1	Шуба	40000	1	40000
2	Брюки	2500	15	37500
3	Костюм	6000	4	24000
4	Джинсы	1500	35	52500
5	Футболка	500	60	30000
6	Кардиган	3600	13	46800
7	Парка	8900	8	71200
8	Свитер	1900	10	19000
9	Пуловер	5600	7	39200
10	Жилет	2000	17	34000

---

Рисунок 18

1. 
   Выделим столбец значений «Сумма» (шапка и итоговые значения не выделяются)!
   
2. 
   В окне «Создание правила» выберем пункт «Форматировать только ячейки, которые содержат»
   

3. 
   В строке «Форматировать только ячейки…» укажем условие « больше 45000»
   
4. 
   По кнопке «Формат» выберем те параметры форматирования, которые определены в задании – укажем цвет и начертание шрифта;
   
5. 
   Нажмем кнопку ОК.
   

Окно правил примет следующий вид



Также добавим следующее ограничение – меньше 30000.

После задания параметров второго условия и применения выбранных параметров наша таблица примет вид, представленный на рисунке 19

	Товарный отчет
	Наименование	Цена	Количество	Сумма продажи
1	Шуба	40000	1	40000
2	Брюки	2500	15	37500
3	Костюм	6000	4	24000
4	Джинсы	1500	35	52500
5	Футболка	500	60	30000
6	Кардиган	3600	13	46800
7	Парка	8900	8	71200
8	Свитер	1900	10	19000
9	Пуловер	5600	7	39200
10	Жилет	2000	17	34000

---