Электронные таблицы, ввод формул

Теория

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

Вставка формул

Для создания формул можно использовать любую из математических функций, встроенных в Numbers. В Numbers доступно более 250 функций для различных видов расчетов, включая статистические, финансовые и инженерные. Некоторые из функций получают информацию удаленно из интернета. Подробнее о каждой из этих функций см. в Справке по формулам и функциям, а также в браузере функций, который появляется при вводе знака равенства (=) в ячейке.

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

1. Нажмите ячейку, в которой нужно отображать результат, и введите знак равенства (=).

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

   

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

2. Нажмите ячейку, которая будет использоваться в формуле, или введите значение (например, число 0 или 5,20).

3. Введите арифметический оператор (например, +, -, * или /), а затем выберите вторую ячейку, которая будет использоваться в формуле, или введите значение.

   По умолчанию Numbers вставляет знак + между ссылками на ячейки.

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

   При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

Вставка функций

1. Нажмите ячейку, в которой нужно отображать результат функции, и введите знак равенства (=).

   Можно также нажать  в панели инструментов и затем выбрать пункт «Новая формула».

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

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

   

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

   Функция отобразится в редакторе формул со всеми обязательными и дополнительными аргументами.

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

   

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

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

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

   • Выбор ячейки. Нажмите ячейку.

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

   • Добавление значений одной строки или столбца. Нажмите заголовок над столбцом или справа от строки или же выберите все ячейки в столбце или строке.

4. По завершении нажмите клавишу Return или в редакторе формул.

   При нажатии работа редактора формул завершается без сохранения внесенных изменений.

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

Работа в классе:

Ввод данных.

Мелкий торговец компьютерной литературой получает товар со склада по оптовым ценам, и продаёт его в своём киоске по розничным, несколько большим. Производить расчет компаньоны договариваются через месяц, причём поставщик настолько любезен, что согласен принять нераспроданный за это время товар, если таковой окажется, обратно на склад. Такая форма торгового сотрудничества называется «взять товар на реализацию». Она, конечно, очень удобна продавцу, который, выходит, продаст или не продаст эти книги, - убытка, по крайней мере, прямого, не понесёт. Однако поставщик, заинтересованный в быстром обороте средств, предлагает дополнительно ещё и довольно заметную скидку с оптовой цены, если товар будет взят не на реализацию, а с предоплатой, но уже без возможности возврата. Для торговца с одной стороны получается выгода, - товар обходится дешевле. Хотя с другой стороны это риск: вдруг данная книга совсем не найдёт спроса. В конце концов, принимается компромиссное решение. Товар, в принципе, берётся на реализацию, но некоторая его часть идёт с предоплатой. Например, торговец заказывает 50 экземпляров какой-то книги, цена которой на складе составляет 35 рублей за одну штуку. По какой цене он будет её продавать – решать ему, как при этом пойдёт торговля – никто не знает, но за 10 штук он платит сразу со скидкой 20%, т.е. по 28 рублей, и это хорошо. Зато в случае неудачи вернуть можно будет не более 40 книг, даже если вообще не будет продано ни одной. И это плохо. Ну а реально, допустим, продаст он 27 книг; тогда ему останется отдать деньги ещё за дополнительные 17 штук, но уже по-обычному, без скидки, по 35 рублей за каждую, а оставшиеся 23 экземпляра – просто вернуть. Продавцу, чтобы оперативно отслеживать ход событий, желательно все эти сведения оформить в виде электронной таблицы. Рассмотрим примерную последовательность действий по её созданию.

Сначала в ячейку A1 впишем название таблицы, не придавая пока значения тому, как оно выглядит на листе. Потом в клетках третьей строки расположим заголовки колонок, требуемых для полноты картины. Затем приступим к вводу исходных данных. Названия и авторы книг (не уместившиеся в своих клетках, но, тем не менее, запомненные там), оптовая и розничная цены, количество заказанных и сразу же оплаченных экземпляров – всё это заполняется один раз, и в дальнейшем изменению не подлежит. Несколько особняком стоит информация о количестве проданных книг. Ясно, что она как раз наоборот, будет меняться неоднократно, отражая динамику событий. Хотя по своей сути она тоже относится к исходным данным, в том смысле, что вводится, так сказать, «руками» и от неё зависит приход, расход, баланс и возврат. В перечисленные же четыре колонки должны быть введены те формулы, по которым будет производиться подсчёт.

Ввод формул.

Как рассчитывать приход – ясно: количество проданных книг данного названия надо умножить на их же розничную цену. Ввод формулы в ячейку H4 начинаем, понятно, со знака «равно». Затем продолжаем действия щёлчком по клетке G4, нажатием клавиши умножения (звёздочка) и щелчком по клетке D4. Всё. Завершаем набор клавишей «Enter» и мгновенно получаем в качестве результата конкретное число (8 * 50 = 400). А ещё ясно, как определяется баланс. От прихода надо отнять расход, и то, что получится, окажется положительным или отрицательным числом. Соответствующую формулу можно ввести в ячейку J4 прямо сейчас, не дожидаясь даже, когда будет заполнена клеточка расхода. Такое нарушение очерёдности вполне допустимо. Ведь для описания того, как подсчитывать баланс, собственно, не важно само значение расхода в данный момент, - важно то, какова связь баланса с расходом. Расход в дальнейшем, когда его формула будет вписана в I4, изменится, ну соответственно и баланс будет автоматически пересчитан. С самим же расходом, вычисляемым в ячейке I4, дело обстоит сложнее. Он состоит из двух слагаемых. Во-первых, это сумма, которая была отдана поставщику за некоторое количество книг сразу, по предоплате. Во-вторых, это деньги, которые придётся отдать при окончательном расчёте за книги, проданные сверх оплаченной нормы. Если, конечно, этого самого «сверх» будет сколько-то продано, чего заранее знать нельзя. Первую половину расхода подсчитать легко, всё выполняется аналогично приходу (необходимо только учесть, что от оптовой цены надо брать, за вычетом скидки, лишь 80%). Но формула на этом не заканчивается и «Enter» нажимать рано. Наоборот, надо продолжить, т.е. нажать «плюс» и ввести второе слагаемое. Но как же его учесть, это второе слагаемое, которое ещё неизвестно чему будет равно? Разрешить это затруднение помогает замечательная встроенная функция по имени «ЕСЛИ». Конечно, по своей популярности она (да и любая другая функция) не может сравниться с автосуммированием, и её индивидуального значка нет на панели инструментов. Поэтому после «плюса» надо щёлкнуть по значку, расположенному справа от автосуммы, который служит для вызова всех остальных встроенных функций.

Функция «ЕСЛИ».

Сколько аргументов у данной логической функции? Уже в первой карточке мастера было видно, что три. Они заключены в круглые скобки после имени функции, и отделяются друг от друга точкой с запятой. «Логическое_выражение» – это переменная логического типа, т.е. могущая принимать всего два значения «Истина» или «Ложь». В нашем случае фактором, от которого зависит дальнейший план действий, является ответ на вопрос «На сегодняшний день книг продано больше, чем было оплачено при заказе?» Больше содержимое клетки G4, чем содержимое F4, или меньше? От этого зависит, как будем считать расход. Если книг продано больше, чем оплачено, т.е. если логическое выражение G4>F4 истинно, то придётся отдавать деньги за эту разницу. Умноженная на оптовую цену, она и составляет то значение, которое будет выдавать функция, если указанное условие выполнено. Ну а если условие выполнено не будет, и книг окажется продано меньшее количество, чем уже было оплачено, то какой же ещё может быть дополнительный расход - и так уже одни убытки! Функция должна выдавать значение ноль (для второго слагаемого).

Вторая карточка мастера функций как раз и предназначена для удобного ввода всех перечисленных аргументов. Между прочим, чтобы сама карточка не загораживала нужные клетки таблицы, её можно перетаскивать по экрану, захватив мышкой в любой точке серого фона. Или щёлкнуть по красному значку справа от поля ввода. Полями ввода называются три белые полосочки (или иное их количество, для разных функций разное). Находясь внутри них, щёлкаем мышкой по нужным ячейкам, нажимаем на нужные клавиши со знаками - больше, меньше, плюс, минус, умножить, разделить, скобки, цифры. Удобно, кстати говоря, пользоваться вспомогательным блоком клавиатуры, с правого её края. Надо только следить, чтобы при этом светился индикатор NumLock. Переходить от одного поля к другому можно мышью, или клавишей «Tab». Если при наборе случится ошибка, то исправлять её можно как обычно с помощью клавиш «Backspace» и «Delete». Или даже «Esc», с последующим повтором всей операции заново. Наконец, если все аргументы введены правильно, нажимается «Enter» или щёлкается «OK». Тогда в ячейке появляется число, рассчитанное в соответствии с указанной формулой по текущим исходным данным из тех клеток, адреса которых указаны в ней. Например, в настоящий момент у нас должно получиться число 320, поскольку 10 * 40 * 0,8 + 0 = 320. Оно равно сумме предоплаты, так как продано книг ещё не очень много. Но, положим, через неделю ситуация изменится, и будет продано 13 книг. Согласно формуле расход увеличится: 10 * 40 * 0,8 + (13 – 10) * 40 = 440.

Функция «МАКС».

Итак, на текущий день приход составляет 400 рублей, а расход – 320. По мере продолжения торговли, расход, конечно, будет увеличиваться, но ведь и приход тоже, иначе не было бы смысла в этой деятельности. Причём приход будет расти чуть быстрее, обуславливая, начиная с некоторого момента, положительность баланса. В нашем случае, для первой из списка книги этот момент уже позади – приход уже стал больше, чем расход. То есть торговля себя уже оправдала, однако нераспроданных книг всё же ещё очень много, и к концу срока реализации возврат, по-видимому, будет неизбежен. По какой же формуле его определять? Опять-таки, можно воспользоваться функцией «ЕСЛИ». Причём с тем же логическим выражением в качестве первого аргумента, но с другим «Значением_если_истина» и другим «Значением_если_ложь». Каковы эти новые значения? Если продано меньше, чем было оплачено, то хоть бы и хотелось возвратить всё, да нельзя по условиям договора. Оплаченное уже не возвращают. Только неоплаченное, т.е. разницу между общим полученным количеством и сразу на месте оплаченным. Если же продано всё-таки больше, чем оплачено, то можно вернуть всё, что осталось, т.е. разницу между полученным и проданным. Есть, однако, и другие встроенные функции, и вот ещё одна, с помощью которой придёшь к тому же результату, и даже более простым образом. Эта функция называется «МАКС» и принадлежит к категории статистических. В качестве аргумента ей обычно указывают некоторый диапазон адресов, какую-то область таблицы, в клетках которой надо найти самое большое по величине число. Аргументов может быть и больше одного, вообще много, хоть десяток, если области поиска не образуют одного целого прямоугольника, а расположены в разных местах таблицы. Кроме того, аргументом может служить и просто то или иное конкретное число.

В нашем случае формула для подсчёта возвращаемых книг будет такой: от полученного их количества (E4) надо отнять либо количество оплаченных (F4), либо количество проданных (G4), смотря какое из них больше. Иначе говоря, отнять надо будет то число, которое окажется максимальным при обзоре всех ячеек из диапазона адресов (F4:G4). Область поиска, хотя бы и из двух всего клеточек, указывается обычным способом – выделением, т.е. проведением по ним мышью с нажатой левой кнопкой. Не нравится так, водя мышкой, - можно записать и по-другому. В первое поле ввода вписать один только адрес F4 (щёлкнув по нему), а во второе – один только адрес G4. И так, и так результат будет 20: от числа 30 будет отнято число 10 (потому что оно больше 8) и полученное количество книг подлежащих возврату появится в клетке K4.

Копирование формул.

В принципе, для каждого названия в списке книг можно было бы повторить те же самые действия по вводу формул. Но, конечно же, должен иметься способ, чтобы эти практически одинаковые формулы не писать десять раз подряд. Надо, единожды написав формулу, суметь её скопировать в другую ячейку, или даже в целый диапазон ячеек. И в самом деле, таких способов много; точнее сказать способ один, но в разных реализациях. Однако в любом случае начать следует с выделения той ячейки, в которую формула уже вписана. А после – на выбор: одно, другое или третье, через буфер обмена, или просто перетаскиванием (методом drag-and-drop). В нашем случае удобнее всего сделать так. Наведя рамочку-курсор на ячейку H4, и тем самым выделив её, поводить мышкой (не нажимая на её клавиши) вокруг, внимательно наблюдая за видом указателя. Когда указатель находится внутри рамки или, наоборот, вне её, он представляется нам как широкий значок креста. Кода же этот крест пересекает рамку, всё равно где, - слева, справа, снизу, сверху, - он становится не крестом, а стрелкой. Ну и третий облик – узенького плюсового значка - указатель принимает, когда проходит над чёрным утолщением рамки в её правом нижнем углу. Вот именно третий вариант нам и надо уловить. Нажав в этот момент, и не отпуская левую клавишу мышки, протянем раздвижную пунктирную рамочку, которая при этом образуется, вниз до клетки H10 включительно. А потом клавишу отпустим. Все попавшие внутрь ячейки останутся выделенными. Выделение легко снять, щёлкнув по любой другой клетке, но не это главное. А главное в том, что каждая ячейка (из выделенных) теперь уже содержит в себе формулу, аналогичную той, с которой всё начиналось! То есть исходная формула скопировалась, куда ей было указано. Причём сделано это было не бездумно, один в один, а с учётом изменённого местоположения целевой ячейки. Насколько сдвинулась вниз при копировании формула, настолько же изменились и все адресные ссылки, содержавшиеся в ней. Скажем, формула в ячейке H5 будет ссылаться не как начальная на G4 и D4, (что было бы при буквальном копировании), а уже на G5 и D5. Такие ссылки, меняющиеся соответственно смещению формулы, называются относительными.

В итоге у вас должна получиться следующая таблица: