Основы работы в Microsoft Excel

 

Упражнение. Для создания списков «Наименование товара» и «Цена в у.е.» выполнить следующие действия:

• Перейти на лист «Прайс-лист».
• Выделить курсором блок ячеек А2:В10.
• Выбрать Формулы|Создать из выделенного фрагмента установить флажок в опции В строке выше, ненужные флажки (в опциях “в столбце слева” и других, если они есть) удалить и нажать ОК.

• Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока.

В результате будет создан список (блок) “Наименование_товара”, содержащий наименование всех товаров, и список (блок) “Цена_ в_ у.е.” (ячейки B3 –B10), содержащий цену всех товаров.

 

Упражнение. Для ввода наименований товаров из списка в ячейки A3 – A10 (рис.5) следует:

• Перейти на Лист 2.
• Выделить блок ячеек А3:А10.
• Выбрать Данные|Работа с данными|Проверка данных.
• На вкладке Параметры в опции Тип данных выбрать Список.
• Установить курсор в опцию Источник и нажать функциональную клавишу F3.
• В окне Вставка имени выбрать «Наименование товара» и нажать ОК. Затем нажать ОК внизу окна Проверка вводимых значений. Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока ячеек.
• Установить курсор в ячейку А3 и нажать кнопку со стрелкой.
• Из открывшегося списка выбрать «Видеомагнитофон».
• В ячейки А4:А10 ввести данные по образцу (рис.5)

 

 

Рис.5

• В ячейку А11 ввести с клавиатуры «Итого».

Примечание: если случайно в блок попали ячейки, не требующие проверки данных, то необходимо выделить эти ячейки и выбрать Данные|Работа с данными|Проверка данных, а затем в окне Проверка вводимых данных на вкладке Параметры нажать кнопку Очистить все.

 

Данные в графу «Цена» будут вводиться с использованием функции ПРОСМОТР.

Функция ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения, и возвращает значение из другого столбца или строки.

 В рассматриваемом примере по известному наименованию товара функция ПРОСМОТР автоматически подставит в ячейку графы «Цена» новой таблицы цену товара из «Прайс-листа».

 

Синтаксис функции ПРОСМОТР:

ПРОСМОТР(искомое_значение; просматриваемый_вектор; вектор_результатов).

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

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

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

Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.

Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

 

Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.

 

Упражнение Для ввода данных в графу «Цена» с помощью данной функции необходимо:

• Открыть «Прайс-лист».
• Выделить блок ячеек А2:В10 и нажать кнопку Сортировка и фильтр в группе Редактирование на вкладке Главная. В открывшемся окне выбрать команду Сортировка от А до Я, в результате чего данные таблицы будут упорядочены по первому столбцу в выделенном блоке ячеек.
• Открыть Лист 2.
• Установить курсор в ячейку В3.
• В строке формул нажать кнопку Вставка функции (или выбрать Формулы|Библиотека функций|Вставить функцию…).
• В открывшемся окне в области Категория выбрать Ссылки и массивы, в области Выберите функцию – ПРОСМОТР и нажать ОК.
• В следующем окне выбрать искомое_значение; просматриваемый_вектор;вектор_результов и нажать ОК.
• Передвинуть открывшееся окно так, чтобы просматривались данные таблицы.
• Установив курсор в окне Искомое_значение, щелкнуть левой кнопкой мыши в ячейке А3 текущей таблицы.
• Установить курсор в окно Просматриваемый_вектор и нажать функциональную клавишу F3 на клавиатуре.
• В открывшемся окне выбрать имя блока Наименование_товара и нажать ОК.
• Установить курсор в окно Вектор_результатов и нажать функциональную клавишу F3.
• В открывшемся окне выбрать имя блока Цена_в_у.е. и нажать ОК.
• Нажать ОК в главном окне функции ПРОСМОТР.

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

=ПРОСМОТР(A3;Наименование_товара;Цена_в_у.е.).

Копирование формул в электронных таблицах

Экономические таблицы  содержат в пределах одного столбца, как правило, однородные данные, то есть данные одного типа и структуры.

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

Для этого  существует несколько приемов копирования:

• Использование кнопок Копировать и Вставить в группе Буфер обмена на вкладке Главная
• Использование правой кнопки мыши, то есть вызов контекстного меню, и выбор соответствующих пунктов: Копировать и Вставить.
• Протаскивание «маркера заполнения» через требуемые смежные ячейки. Маркером заполнения при этом называется черный квадратик, расположенный в правом нижнем углу активной ячейки.
• Другие способы копирования в среде Windows (например, сочетание «горячих клавиш» Ctrl+C , Ctrl+V и т.п.).

 

Упражнение. Скопировать содержимое ячейки В3 в блок ячеек В4:В10. При приеме копирования с использованием «протаскивания» маркера заполнения необходимо выполнить следующие действия:

• Поместить курсор мыши на маркер ячейки В3 в правом нижнем углу (при этом курсор должен приобрести конфигурацию знака «плюс» (+).
• Удерживая нажатой левую кнопку мыши, «протащить» маркер через ячейки В4:В10.
• Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока В3:В10.

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

Ввод  формул и функций для табличных расчетов

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

1. В первую очередь вычисляются выражения внутри круглых скобок.
2. Умножение и деление выполняются раньше сложения и вычитания.
3. Операторы с одинаковым приоритетом выполняются слева направо.
4. Для изменения порядка выполнения операторов используют круглые скобки.
5. Если в формуле количество закрывающих и открывающих круглых скобок не совпадает, выводится сообщение «Несоответствие скобок» и выделяется ошибочная часть формулы.
6. Формула, содержащая ссылки на адреса ячеек, связана с ячейками рабочей книги, а значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек.

 

Поскольку значение показателя «Наценка» зависит от цены товара, целесообразно применить  логическую функцию ЕСЛИ для расчета соответствующей графы таблицы.

Функция ЕСЛИ определяет условие (условия), при котором возможны два варианта действий, первое из которых свершится при выполнении заданного условия, второе – при его невыполнении.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис функции ЕСЛИ:

ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложь)

В данном примере  величина наценки будет рассчитываться по следующему алгоритму: если цена товара по прайс-листу меньше 500 у.е., то наценка составит 50% от цены, в противном случае 30%.

Упражнение. Для расчета данных в графе «Наценка» необходимо выполнить следующие действия:

• Активизировать ячейку С3.
• Щелкнуть левой кнопкой мыши по кнопке Вставить функцию (f_x) в строке формул.
• В появившемся окне Мастер функций выбрать категорию функций логические.
• В списке функций выбрать ЕСЛИ.
• Нажать ОК.
• Подвинуть появившееся на экране окно за пределы таблицы, чтобы не мешать указателю мыши работать с ячейками таблицы.
• Установить курсор в окно Логическое выражение.
• Щелкнуть левой кнопкой мыши в ячейке В3, в результате чего адрес данной ячейки отражен в окне Логическое выражение. С клавиатуры набрать <500.
• Перевести курсор в окно Значение_если_истина.
• Щелкнуть левой кнопкой мыши в ячейки В3 и далее ввести формулу В3*50%.
• Перевести курсор в окно Значение_если ложь.
• Щелкнуть левой кнопкой мыши в ячейки В3 и далее ввести формулу В3*30%.
• Нажать ОК.

Обратить  внимание на формулу, которая отразилась в строке формул. Данную формулу опытный пользователь мог сразу ввести с клавиатуры. Словами это логическое выражение было сформулировано в описании алгоритма расчета наценки, который был приведен выше. Выражение примет следующий вид: =ЕСЛИ(В3<500;В3*50%;В3*30%).

 

Упражнение. Необходимо скопировать полученную функцию в ячейки С4:С10, для этого:

• Установить курсор в ячейку С3 и щелкнуть по правой кнопке мыши для вызова контекстного меню.
• Выбрать команду Копировать (при этом вокруг ячейки С3 появится «бегущая» рамка).
• Выделить блок ячеек С4:С10 и нажать правую кнопку мыши.
• В контекстном меню  выбрать команду Вставить.
• Щелкнуть левой кнопкой мыши в любой свободной ячейке листа, чтобы снять выделение с блока С4:С10.
• Нажать Esc, чтобы убрать «бегущую» рамку вокруг ячейки С3.

 

<p class