Правила присвоения имен блокам

· имя должно начинаться с буквы;

· в имени блока могут использоваться только буквы, цифры, обратная косая черта и символ подчеркивания ( _ ). Заменяйте пробелы символом подчеркивания;

· нельзя использовать имена, которые могут трактоваться как ссылки на ячейки;

· в качестве имен могут использоваться одиночные буквы за исключением R и C.

3. Присвоить имена блокам ячеек: Код_зак (блок ячеек А3:А9), Наим_зак (блок ячеек В3:В9), Адрес (блок ячеек С3:С9) и Заказчики (блок ячеек А2:С9).

Выделить блок ячеек А3:А9, которому задать имя Код_зак.

Для этого выполнить команду меню Вставка /Имя / Присвоить. В появившемся окне ввести имя выделенного блока ячеек в строке Имя: Код_зак

В строке Формула выводится ссылка на диапазон выделенных ячеек. Нажать кнопку Добавить, а затем ОК. Аналогично зададим остальные имена Наим_зак, Адрес, Заказчики.

Создание и ведение списков.

Список - это один из способов организации данных на рабочем листе. Прежде чем создать список, надо тщательно продумать его структуру и определить, какие данные включить в него. Создание списка нужно начинать с формирования заголовков столбцов. Символы шрифта, используемые в заголовках должны иметь характерное начертание, отличные от тех, которые использовались для значений данных внутри списка. Если заголовки оформлены по-другому, программа автоматически определяет, что вводится список. При создании списка на рабочем листе EXCEL необходимо выполнить следующие правила:

· списки рекомендуется помещать на одном рабочем листе;

· следует отделять список от других данных рабочего листа хотя бы одной строкой и одним столбцом;

· имена столбцов должны располагаться в первой строке списка.

· каждый столбец в списке должен содержать во всех строках однотипные данные (текстовые, числовые и т.д.);

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

· чтобы отсортировать весь список, достаточно выделить одну ячейку в списке и выбрать в меню Данные команду Сортировка. EXCEL автоматически выделит весь список. Если в первой строке списка находятся имена столбцов, то они не будут включены в сортировку. Необходимо помнить, что в этом случае итоговая строка, если она рассчитана, также будет включена в сортировку. Поэтому лучше самостоятельно выделять область исходного списка для сортировки;

· имя списку задается так же, как именованному блоку;

· в качестве списка могут выступать как целые таблицы, так и ее части - отдельные столбцы, блоки, ячейки.

Поле со списком

Из заполненного данными списка можно вводить эти данные в другие списки (таблицы). Для этого выделить ячейки столбца, которые хотим заполнить данными из другой таблицы (списка). Выполнить команду Данные / Проверка. В качестве Типа данных выбрать Список, Источник – имя исходного списка, где расположены данные. Это имя можно выбрать с помощью функциональной клавиши F3. В результате в выделенном столбце в первой ячейке появится знакстрелка (признак поля со списком). Знак стрелка позволит раскрыть список и выбрать из него значение. Переходя последовательно в следующие ячейки столбца, можно заполнить данными весь выделенный столбец выборкой из списка. Значения для поля со списком можно также задать сразу с помощью команды Данные/Проверка. В окно Источник ввести значения списка, разделяя их знаком «; » (точка с запятой). Такой способ задания значений списка лучше использовать в том случае, когда значений в списке мало.

4. На новом листе Ведомость создать таблицу следующего вида:

· Обеспечить ввод данных в поле Код заказчика для ячеек А6:А16 через список, созданный на основе блока ячеек Код_зак.

Для этого выполнить действия:

· выделить ячейки А6:А16;

· выполнить команду Данные/Проверка;

· в открывшемся окне установить Тип данных – Список. Установить Источник – Код_зак. Для этого нажать функциональную клавишу F3 и выбрать из списка Код_зак. Нажать ОК.

· В таблице Ведомость в столбце Код заказчика справа от ячейки А6 появится стрелка (поле со списком). С помощью стрелки можно раскрыть список и выбрать значение Кода заказчика. Перейти в ячейку А7 и заполнить её. Аналогично з аполняют другие ячейки столбца. Значения заполнить в соответствии с исходной таблицей.

 

Функции ПРОСМОТР и ВПР

Для автоматизации заполнения таблицы используют функции ПРОСМОТР или ВПР.

5. Отобразить значения полей Наименование заказчика и Адрес согласно коду заказчика из таблицы Список заказчиков с помощью функции ВПР или ПРОСМОТР.

Наименование заказчика и Адрес находятся в прямой зависимости от Кода заказчика, то для заполнения значений этих полей можно применить функции ПРОСМОТР и ВПР. Рассмотрим применение функции ПРОСМОТР:

После вызова функции ПРОСМОТР в следующем окне надо установить все параметры: в окно

Искомое_значение

поместим А6 (щелчком по ячейке А6 ).

В окно Просматриваемый_вектор с помощью функциональной клавиши F3 вызываем имя блока ячеек Код_зак.

В окно Вектор_результатов с помощью функциональной клавиши F3 вызываем имя блока ячеек Наим_зак.

Нажать ОК в главном окне функции ПРОСМОТР.

· Скопировать полученную формулу в ячейки В7:В16. В результате столбец Наименование заказчика будет заполнен.

· Аналогично заполним столбец Адрес. Установим курсор в ячейку С6, вызвать функцию ПРОСМОТР.

· Главное окно функции ПРОСМОТР имеет вид:

· Нажать ОК в главном окне функции ПРОСМОТР.

Скопировать полученную формулу в ячейки С7:С16. В результате столбец Адрес будет заполнен.

6. Вместо функции ПРОСМОТР можно использовать функцию ВПР.

· Для заполнения столбца Наименование заказчика установим курсор в ячейку В6 и вызовем функцию ВПР.

· Главное окно функции ВПР имеет вид:

· Для заполнения окна Искомое _значение щёлкнуть по ячейке А6.

· Для заполнения окна Таблица нажать функциональную клавишу F3 и выбрать из списка таблицу Заказчики.

· Для заполнения окна Номер_столбца ввести цифру 2, (это номер столбца списка Заказчики, значениями из которого надо заполнить такой же столбец новой таблицы).

· При заполнении окна Интервальный_просмотр надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то в окно следует ввести ноль. Нажать ОК в главном окне функции ВПР.

· Скопировать полученную формулу в ячейки В7:В16. В результате столбец Наименование заказчика будет заполнен.

· Аналогично заполнить столбец Адрес.

7. Ограничить ввод данных в поле Период списком фиксированных значений: 1 кв; 2 кв; 3 кв. Выделить столбец Период. Выполнить команду Данные/Проверка. В диалоговом окне Проверка вводимых значений заполнить источник.

Т.к. список состоит из 3-х значений, то их можно ввести в окне источник, разделяя знаком; (точка с запятой). Введём данные в поле Период согласно таблице.

8. Заполнить данными поля Сумма к выплате и Оплачено в соответствии с исходной таблицей.

9. Рассчитать поле Разница=Сумма к выплате - Оплачено. Активизировать ячейку G6. Ввести знак = (равно), затем щёлкнуть ячейку Е6, затем знак – (минус), щёлкнуть ячейку F6. В ячейке получится выражение = Е6 - F6.. Нажать Enter. Затем протащить маркёр по всем ячейкам столбца, в которых надо получить результат (ячейки G6: G16).

10. Рассчитать значения поля Долг. При значении поля Разница больше нуля Долг равен Разнице, в противном случае Долг равен нулю.

 

Логические функции

Логические функции ЕСЛИ, И, ИЛИ и НЕ используют логические выражения для определения истинности заданного условия. Например, каждая из приведенных формул является логическим выражением:

= А1> A2; = СРЗНАЧ(В1:В6); =СУММ(6;7;8); = С2=”Среднее’.

Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения. Например, в логическом выражении А1>А2 оператор больше (>). В качестве операторов сравнения могут быть: =, >, <, >=, <=, <> (не равно). Результатом логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.

Функция ЕСЛИ

Активизируем ячейку Н6. Вызовем функцию ЕСЛИ и заполним значения параметров в окне функции:

Функция ЕСЛИ имеет следующий синтаксис: