Использование относительных и абсолютных адресов ячеек в формулах

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

Приступая к работе, откройте программу Microsoft Office Excel 2007, создайте новый документ – Рабочую книгу, первый лист которой назовите Счет товарный, переименовав Лист1 с помощью контекстного меню, вызываемого к ярлычку Листа1, как показано на рисунках 1, 2. Переименование осуществляется после выбора пункта Переименовать в контекстном меню, заменой текста Лист1 путем стирания его клавишей Backspace и впечатыванием нового текст Счет товарный.

Задавая границы будущей таблицы, выделите диапазон ячеек A1: G10, образованный 7 столбцами и 10 строками, вызовите контекстное меню с целью выбора пункта Формат ячеек (рисунок 3). В отображенном на экране диалоговом окне «Формат ячеек» последовательно осуществите выбор типа линии (рисунок 4), цвета линии (рисунок 5), укажите на внешние и внутренние границы (рисунок 6).

Рисунок 1. Вызов контекстного меню к ярлычку листа

Рисунок 2. Результат переименования листа

Рисунок 3. Вызов контекстного меню с целью выбора пункта «Формат ячеек»

После задания границ таблицы, отрегулируйте ширину строк и высоту столбцов в соответствии с образцом задания, объедините ячейки А1, В1, С1, D1 (или диапазон ячеек А1: D1 превратите в одну ячейку путем объединения и размещения информации по центру), воспользовавшись командой Объединить и поместить в центре, расположенной в группе команд Выравнивание на вкладке Главная. Впечатайте в полученную ячейку текст Счет товарный от 13.09.2014, отформатируйте его в соответствии с образцом.

Рисунок 4. Выбор типа линии на вкладке «Граница»
диалогового окна «Формат ячеек»

Рисунок 5. Выбор цвета линии на вкладке «Граница»
диалогового окна «Формат ячеек»

Рисунок 6. Выбор внешних и внутренних границ на вкладке «Граница»
диалогового окна «Формат ячеек»

Аналогично объедините ячейки Е1 и F1, внесите текст во все остальные ячейки, используя автозаполнение для создания нумерации. Размещая текст в заголовках таблицы, установите перенос по словам и выравнивание по ширине, задаваемые во вкладке Выравнивание диалогового окна Формат ячеек, вызываемого с помощью контекстного меню. Перенос слов вручную на другую строку в пределах одной ячейки можно также выполнить нажатием клавиши Enter (Ввод), удерживая нажатой клавишу Alt.

На следующем этапе необходимо задать форматы для тех групп ячеек, в которых будут в перспективе размещены числовые значения. Так, выделив диапазон С3: С9, в диалогом окне Формат ячеек на вкладке Число выберите числовой формат Денежный, число десятичных знаков, равное 0, обозначение: р., на вкладке Выравнивание установите по горизонтали и по вертикали – по центру, на вкладке Шрифт: Cambria, начертание – обычный, размер – 14. После выполнения предварительных установок для диапазона ячеек столбца С, самостоятельно впечатайте значения цены каждого вида товара, результат сравните с изображением рисунка 7. Обратите внимание на то, что при внесении числовых значений автоматически появляются обозначения рублей (р.).

Далее выделите диапазон D3: D9, в диалогом окне Формат ячеек на вкладке Число выберите числовой формат Числовой, число десятичных знаков, равное 0, на вкладке Выравнивание установите по горизонтали и по вертикали – по центру, на вкладке Шрифт: Cambria, начертание – обычный, размер – 14. После выполнения предварительных установок для диапазона ячеек столбца С, самостоятельно впечатайте значения количества для каждого вида товара.

Рисунок 7. Промежуточный результат заполнения таблицы

Теперь необходимо для диапазона ячеек Е3: Е9 задать расчетные формулы определения стоимостей каждого вида товара путем умножения цена на количество. Следует отметить, что достаточно создать формулу только для ячейки Е3, а затем скопировать ее вниз, распространив тем самым на диапазон ячеек Е4: Е9. В данном случае нет необходимости прибегать к поиску и вставке подходящей стандартной функции, так как можно воспользоваться обычной процедурой умножения значения одной ячейки на значение другой ячейки. Начните создание формулы в ячейке Е3 с ввода знака =, затем щелкните один раз левой кнопкой мышки по ячейке С3 (адрес ячейки отобразится и в строке формул и в ячейке с создаваемой формулой), добавьте знак умножения *, удерживая клавишу Shift нажатой и щелкая по клавише с изображением *, затем щелкните один раз левой кнопкой мышки по ячейке D3, рисунок 8.

Рисунок 8. Процесс создания формулы вычисления стоимости

Для завершения процесса внесения в формулу в ячейку Е3, нажмите клавишу Enter на клавиатуре, в результате в ячейке отобразится полученное путем умножения целое значение стоимости. Скопируйте формулу вниз, распространив ее на диапазон Е4: Е9, перемещая расположенный в правом нижнем углу указатель в форме маленького черного крестика в состоянии нажатой левой кнопки мышки в направлении вниз. Обратите внимание на то, что отображенные полученные в результате применения формул значения ячеек содержат не только целые числа, но и обозначения рубли (р.), рисунок 9.

Рисунок 9. Процесс создания формулы вычисления стоимости и копирования ее

Теперь попробуйте изменить некоторые значения ячеек для количества и цены, просмотрите результаты почти мгновенного пересчета получаемых значений стоимости программой Microsort Office Excel 2007.

В ячейке G1 в соответствии с заданием, должно содержаться значение ставки НДС (налога на добавленную стоимость), выраженное в процентах. Вызовите контекстное меню к ячейке G1, остановитесь на пункте списка Формат ячеек, в появившемся диалоговом окне Формат ячеек на вкладке Число выберите Числовой формат – процентный с числом десятичных знаков, равным 0, на вкладке Выравнивание установите по горизонтали и по вертикали – по центру, на вкладке Шрифт: Cambria, начертание – обычный, размер – 14. Внесите в ячейку значение 18%. Для вычисления значения НДС, выраженного в рублях, необходимо вычислить 18% от значения стоимости, выраженной в рублях. В программе Microsort Office Excel 2007 вычисление значения процента от числа можно рассчитать путем умножения числа на значение процента. В качестве расчетной формулы для ячейки F3 используем следующую: =E3*G1. Создайте формулу по образцу, результат сравните с рисунками 10, 11. Теперь, как и в ранее рассмотренных, казалось бы, аналогичных случаях, воспользуйтесь способом копирования формулы вниз, распространения ее на диапазон ячеек F4: F9. Обратите внимание на возникающие ошибки, появившиеся неправильные результаты, нули, хотя значения должны быть ненулевыми.

Рисунок 10. Процесс создания формулы вычисления значения НДС

Рисунок 11. Результат создания формулы вычисления значения НДС

Рисунок 12. Результат копирования формулы нахождения НДС вниз,
содержащий ошибки

На рисунке 12 изображен результат копирования формулы нахождения НДС. Для определения причины неправильности полученных значения и устранения ошибок рассмотрим формулы для каждой строки и проанализируем их, сопоставляя Ваши ошибочные результаты с таблицей, приведенной далее. Во втором столбце таблицы расположены те формулы, которые дают правильные результаты, а в третьем столбце – полученные нами в процессе копирования формулы.

Ошибки в получаемых результатах возникли в связи с тем, что при копировании формулы вниз адреса ячеек менялись соответственно: названия столбцов оставались прежними, так как формулы копировались вниз, а номера строк увеличивались на единицу, поэтому ссылки на ячейки G2, …, G7 оказались неверными. При копировании формулы в данном примере необходимо, чтобы в каждой следующей строке в формуле номер строки в адресе ячейки для стоимости увеличивался на единицу, а адрес ячейки со значением ставки НДС оставался неизменным (зафиксированным) или абсолютным. Такой вариант формул приведен во втором столбце выше расположенной таблицы. Но, использование ранее известного нам способа копирования формулы дает результаты, в адресах которых изменяются все номера всех строк, что нас не совсем устраивает по причине возникающих ошибок и невозможности в некоторых случаях как бы зафиксировать ссылку на определенную ячейку, обеспечив неизменность этого адреса (ссылки на одну и туже ячейку) в процессе копирования. Поэтому имеет смысл познакомиться с такими понятиями, как абсолютные и относительные адреса ячеек.