2020-05-25
301
При записи формул может быть использовано два вида адресации ячеек
- абсолютная
- относительная.
При использовании относительной адресации в формулу подставляются не значения указанных ячеек, а их местоположение относительно той ячейки, в которой указана формула. Использование относительной адресации позволяет копировать формулы в таблице без ручного заполнения (корректировки) формул в каждой ячейке.
При копировании формулы с использованием относительной адресации вместо указанных в первичной формуле адресов ячеек автоматически подставляются адреса ячеек, полученные путем соответствующего смещения для скопированной ячейки.
Наглядно относительная адресация видна при использовании стиля ссылок R1C1.
Рассмотрим таблицу на рисунке 4
| A | B | C | D | |
| 1 | 3 | 4 |
| 7 |
| 2 | 5 | 6 |
| 11 |
Рисунок 4
В ячейке D1 записана формула сложения двух ячеек: = A1 + B1.
При копировании в ячейку D2 формула примет вид: = A2 + B2
Если переключить стиль ссылок на R1C1, формула примет вид: = RC[-3] + RC[-2]. При копировании формулы в нашем случае вид формулы останется без изменений. В самом деле, в полученной формуле запись R означает, что идет обращение к ячейке в той же строке, поэтому смещение отсутствует. А запись С[-3] означает, что осуществляется смещение на три столбца влево.
|
|
|
При использовании абсолютной адресации в формулу подставляется не ссылка на значение относительно текущей ячейки, а непосредственно значение из указанной ячейки.
При использовании абсолютной адресации таблица, представленная на рис. 4, примет вид, указанный на рисунке 5.
| A | B | C | D | |
| 1 | 3 | 4 |
| 7 |
| 2 | 5 | 6 |
| 7 |
Рисунок 5
Для относительной адресации формула из ячейки D4 будет иметь вид: = $A$1+$B$1, а для абсолютной адресации = R1C1+R1C2. При копировании во вторую строку формулы не изменятся.
Строго говоря, фиксирование столбца в нашем случае является избыточным, символ «$» указывает, что осуществляется фиксация только того измерения, которое указано непосредственно за ним. В нашем примере изменение столбца не осуществляется и формула может иметь вид: = A$1+B$1. Результат при этом не изменится.
При изменении стиля ссылок в настройках система автоматически переведет все формулы в новый выбранный стиль ссылок.
Поиск в подмножестве
Поиск в подмножестве целесообразно использовать в том случае если в таблице несколько раз используются значения, относящиеся к одному и тому же показателю. При изменении значений или добавлении нового значения приходится корректировать и все показатели, относящиеся к добавляемому (изменяемому) значению. Например, при составлении отчета о продажах товара по дням среди показателей за каждый день продаж фигурируют одни и те же позиции, с одной и той же ценой и одинаковым наименованием. Для одной и той же позиции оператор должен указать
|
|
|
- артикул (для однозначной идентификации товара)
- наименование товара
- цена товара
Автоподстановка значений наименования и цен значительно сократит время работы оператора, а также существенно снизит вероятность ошибок при заполнении значений реквизитов.
Рассмотрим таблицу на рис. 6
| A | B | C | D | E | F | |
| 1 | Отчет о продажах | |||||
| 2 | Дата | Артикул | Наименование | Цена | Количество | Сумма |
| 3 | 01.10.2017 | 1234 | Кофта | 100 | 3 | 300 |
| 4 | 01.10.2017 | 2222 | Жилет | 200 | 2 | 400 |
| 5 | 01.10.2017 | 3333 | Брюки | 300 | 5 | 1500 |
| 6 | 02.10.2017 | 1234 | Кофта | 100 | 2 | 200 |
| 7 | 02.10.2017 | 2222 | Жилет | 200 | 3 | 600 |
| 8 | 02.10.2017 | 3333 | Брюки | 300 | 6 | 1800 |
| 9 | 02.10.2017 | 4444 | Свитер | 700 | 1 | 700 |
Рисунок 6
Видно, что три позиции повторяются в таблице дважды, это «Кофта», «Жилет» и «Брюки».
Цены продажи для этих позиций также одинаковы.
Для того, чтобы исключить ручной труд при заполнении подобных таблиц, можно использовать функцию ВПР().
Функция ВПР() имеет следующие параметры
- искомое значение
- массив ячеек, в котором осуществляется поиск искомого значения и подставляемых данных
- номер столбца, из которого необходимо вернуть значение
- признак точного соответствия
При использовании функции ВПР() нужно помнить следующие моменты:
1. Таблица, в которой осуществляется поиск, должна быть упорядочена по возрастанию значений первого столбца выделенной области;
2. Поиск искомого значения осуществляется только по первому столбцу;
3. Если таблица не упорядочена и знак точного соответствия не установлен, будет возвращено значение из строки, значение первого столбца которой близко (по мнению системы) на искомое значение;
4. Нумерация столбцов, значения из которых подставляются как результат работы функции ВПР(), ведется для выделенной области, а не по всему листу, на котором расположена справочная таблица.
Создадим справочник, из которого будем подставлять значения (рисунок 7)
| A | B | C | D | E | F | |
| 17 |
|
|
| 1234 | Кофта | 100 |
| 18 |
|
|
| 2222 | Жилет | 200 |
| 19 |
|
|
| 3333 | Брюки | 300 |
| 20 |
|
|
| 4444 | Свитер | 700 |
Рисунок 7
На рисунке 7 специально показано, что справочная таблица находится не по краям листа.
Теперь запишем функцию ВПР() для определения наименования номенклатуры для ячейки C3: = ВПР(В3;D17:F20;2;Истина)
Поиск позиции осуществляется по артикулу, поэтому в качестве искомого значения указывается значение ячейки С2. В первом столбце справочной таблицы также приведены артикулы номенклатуры.
Внимание! Если столбец, по которому осуществляется поиск, будет не первым, функция вернет не верный результат!
Второй параметр – это диапазон, в который входит вся справочная таблица. Строго говоря, для определения наименования нам достаточно указать только диапазон D17:E20.
Третий параметр – номер столбца в справочной таблице, из которого будет возвращено значение. Наша выделенная область начинается со столбца D, т.е. – это первый столбец справочной таблицы. Таким образом, столбец E является вторым столбцов выделенной области. Именно его значение и будет возвращено как результат работы функции.
Четвертый параметр желательно всегда указывать как Истина, чтобы минимизировать вероятность некорректной работы функции.
Если скопировать функцию вниз для всех заполненных ячеек таблицы, таблица примет вид, показанный на рисунке 8:
| A | B | C | D | E | F | |||
| 1 | Отчет о продажах | |||||||
| 2 | Дата | Артикул | Наименование | Цена | Количество | Сумма | ||
| 3 | 01.10.2017 | 1234 | Кофта | 100 | 3 | 300 | ||
| 4 | 01.10.2017 | 2222 | Жилет | 200
| 2 | 400 | ||
| 5 | 01.10.2017 | 3333 | Брюки | 300 | 5 | 1500 | ||
| 6 | 02.10.2017 | 1234 | #Н/Д | 100 | 2 | 200 | ||
| 7 | 02.10.2017 | 2222 | #Н/Д | 200 | 3 | 600 | ||
| 8 | 02.10.2017 | 3333 | #Н/Д | 300 | 6 | 1800 | ||
| 9 | 02.10.2017 | 4444 | #Н/Д | 700 | 1 | 700 | ||
Рисунок 8
Из таблицы, что для части строк заполнение выполнилось, а для части – выведено сообщение об ошибке «#Н/Д» (нет данных).
Чтобы понять причину возникновения данной ошибки, установиv курсор в ячейку C6. И обратим внимание на второй параметр функции. Произошло смещение диапазона поиска. В самом деле, в функции указана относительная адресация, поэтому при копировании функции ВПР () вниз по таблице произошло аналогичное смещение и строк диапазона поиска.
Возникает вопрос, а почему в этом случае были найдены значения для двух следующих строк таблицы? Все просто, смещение диапазона произошло и для этих двух строк, но искомое значение попало в получившийся диапазон.
Чтобы устранить последствия этой ошибки, укажем в формуле для первой ячейки во втором параметре признак абсолютной адресации как для строк, так и для столбцов. В данном случае указание абсолютной адресации для столбцов является избыточным (не ошибочным!), но будет использовано чуть позднее.
После внесения изменений и копировании формулы вниз по строкам исходной таблицы она примет вид, показанных на рис. 9.
| A | B | C | D | E | F | |
| 1 | Отчет о продажах | |||||
| 2 | Дата | Артикул | Наименование | Цена | Количество | Сумма |
| 3 | 01.10.2017 | 1234 | Кофта | 100 | 3 | 300 |
| 4 | 01.10.2017 | 2222 | Жилет | 200 | 2 | 400 |
| 5 | 01.10.2017 | 3333 | Брюки | 300 | 5 | 1500 |
| 6 | 02.10.2017 | 1234 | Кофта | 100 | 2 | 200 |
| 7 | 02.10.2017 | 2222 | Жилет | 200 | 3 | 600 |
| 8 | 02.10.2017 | 3333 | Брюки | 300 | 6 | 1800 |
| 9 | 02.10.2017 | 4444 | Свитер | 700 | 1 | 700 |
Рисунок 9
В таблице, показанной на рисунке 9, все наименования заполнены автоматически и при изменении артикула будут заменены на те значения, что будут соответствовать вновь введенному артикулу. Но цены позиций номенклатуры указаны вручную. Чтобы этого избежать и обеспечить автоматическое заполнение цен, скопируем функцию из ячейки C3 в ячейку D3, предварительно добавив признак абсолютной адресации перед столбцом первого параметра функции ВПР(), а также изменив номер столбца, из которого возвращается значение, со второго на третий.
|
|
|
И растянем формулу вниз. Если все сделано правильно, внешний вид таблиц не изменится.
Формула для ячейки D3 имеет вид: = ВПР($В3;D17:F20;3;Истина)
Для проверки правильности работы формул добавим в нашу таблицу несколько строк, «протянем» автозаполнение столбцов С и В на добавленные строки и в столбце В введем значения артикулов, присутствующих в справочной таблице. Таблица примет вид, похожий на тот, что приведен на рисунке 10:
| A | B | C | D | E | F | |
| 1 | Отчет о продажах | |||||
| 2 | Дата | Артикул | Наименование | Цена | Количество | Сумма |
| 3 | 01.10.2017 | 1234 | Кофта | 100 | 3 | 300 |
| 4 | 01.10.2017 | 2222 | Жилет | 200 | 2 | 400 |
| 5 | 01.10.2017 | 3333 | Брюки | 300 | 5 | 1500 |
| 6 | 02.10.2017 | 1234 | Кофта | 100 | 2 | 200 |
| 7 | 02.10.2017 | 2222 | Жилет | 200 | 3 | 600 |
| 8 | 02.10.2017 | 3333 | Брюки | 300 | 6 | 1800 |
| 9 | 02.10.2017 | 4444 | Свитер | 700 | 1 | 700 |
| 10 |
| 4444 | Свитер | 700 |
|
|
| 11 |
|
| #Н/Д | #Н/Д |
|
|
| 12 |
| 3333 | Брюки | 300 |
|
|
| 13 |
|
|
|
|
|
|
| 14 |
|
|
|
|
|
|
Рисунок 10
