Она состоит. Самое время попробовать программу в деле, вы узнаете, как работать в Excel. Мы создадим новый документ, внесем в него данные, выполним простые расчеты и построим временную диаграмму. После освоения этого урока вы уже сможете работать в программе, выполнять лёгкие задачи. Это будет первый уверенный шаг к профессиональному пользованию Эксель.
Место преступления: формулы не рассчитаны
По крайней мере, это так теоретически. Или формула отображается как таковая. Все три причины быстро разрешаются.
Решение 1. Включить автоматический расчет
Параметры расчета можно найти в пункте меню «Формулы». В раскрывающемся меню вы можете выбрать «Автоматический», «Автоматический, за исключением таблиц данных» и «Вручную». Более сложным способом изменения параметров расчета является использование параметров программы на вкладке «Файл». Единственное преимущество этого пути состоит в том, что вы можете изменить другие параметры программы.Исходные данные: ежемесячные объёмы продаж товара в 2015 году, стоимость единицы товара, выручка за 2014 год помесячно. Приступаем к расчетам.
Создаём новый документ Эксель
Если Excel у вас еще не запущен – нажмите «Пуск» — «Все программы» — «Microsoft Office» , и в открывшейся папке – «Excel» . Программа будет запущена. В открывшемся окне выберите «Пустая книга» .
Знак равенства = тогда не интерпретируется как таковой, а рассматривается как произвольный символ. Следствие. Формула не рассчитывается, а отображается как формула. Просто изменить формат ячейки недостаточно, так как вы можете легко попробовать. По этой причине вы не можете «обучать» несколько ячеек одновременно. Во-первых, формат ячейки должен быть преобразован в нетекстовый формат во все ячейки, в которых формулы не вычисляются автоматически.
Решение 3. Определите и удалите круговую крышку
Впоследствии необходимо индивидуальное подтверждение формул через «Формулу-бар». Если результат формулы зависит от самого себя или относится к другой ячейке, содержимое которой зависит от результата формулы, используется круговая ссылка. Другими словами, формула, прямо или косвенно ссылающаяся на собственную ячейку, является циклической ссылкой. Простейший случай круговой ссылки встречается в формуле суммы, когда ячейка результата включена в диапазон, который нужно вычислить.
Создание нового документа Excel
Если же программа уже запущена – нажмите комбинацию клавиш Ctrl+N для создания новой рабочей книги.
Заполняем лист Excel данными
В результате, перед вами новый пустой документ с активным листом «Лист 1» . Давайте заполним его исходными данными. Начнём с шапки, т.е. определимся со структурой таблицы. Пусть в первом столбце будут месяца года. Кликните левой кнопкой мыши в ячейку А1 , она будет активизирована для заполнения. Напишите: «Месяц». Аналогично в колонке В1 запишем «Выручка 2015», С1 – «Продажи», D1 – «Цена», E1 – «Выручка 2014». Шапка готова.
Отображение вычислений и подсчетов в строке состояния
В качестве внутренней процентной ставки комментарии недавно дали интересный вопрос от Стэна. Внутренняя норма доходности, или, скорее, внутренняя ставка процента, является динамическим методом расчета инвестиций. В дополнение к статическим методам, которые могут быть использованы только для расчета прибыли от инвестиций с фиксированным начальным и конечным значением, также возможно включить платежи и изъятия в случае динамической процедуры. Поэтому это интересно, если смотреть на более длительный период времени, где, с одной стороны, «свежие деньги» пополняются, но, с другой стороны, также регулярные доходы, такие как проценты или дивиденды.
Теперь напишем названия месяцев в столбце А . Установите курсор в ячейку А2 , напишите «Январь» и нажмите Enter . Табличный курсор перенесётся на одну строчку вниз. Чтобы заполнить последующие месяца – воспользуемся функцией Эксель – автозаполнение. Верните курсор в ячейку А2 и наведите мышь на квадратик в нижнем правом углу активной клетки. При этом, курсор мыши превратится в чёрный крестик. Зажмите левую кнопку мыши и тяните курсор вниз, как бы растягивая надпись «Январь» на ячейки вниз. Эксель автоматически будет записывать новые месяца в каждой следующей строчке.
Метод внутренней процентной ставки является простым, но, конечно, не на 100% правильным, способом расчета среднегодовой доходности инвестиций. Как уже было написано, цель состоит в том, чтобы представить практический пример. Позвольте мне начать с таблицы для бронирования. Каждая строка отражает бронирование, столбец А содержит дату бронирования, колонку Б забронированную сумму, а в колонке С - небольшой комментарий, который является бронированием. При вводе проводки обратите внимание, что вводятся покупки с отрицательным знаком, продажами, процентами и дивидендами с положительным знаком.
Пока вы не отпустили мышь – программа показывает подсказку о том, какой месяц будет в соответствующей ячейке. Посмотрите, на рисунке я дотянул список месяцев до апреля. Вы же протяните курсор на 11 строчек вниз, на подсказке отобразится «декабрь». Отпускайте мышь и «вуа-ля», одним взмахом руки мы прописали целых 12 месяцев!
Таким образом, вы смотрите на ценности с точки зрения своего кошелька. Если деньги уходят, чтобы купить что-то, тогда заказ получает отрицательный знак, когда деньги поступают, положительный. Последняя строка важна для расчета внутренней процентной ставки на ключевую дату. В этом предполагается, что весь депо продается, и деньги возвращаются в кошелек, показанный в строке.
Текущий уровень депо определяется значениями во втором хранилище табличных таблиц. Индивидуальные ценности в депо должны, конечно, быть адаптированы одновременно с каждым бронированием. В большинстве случаев вы можете обойтись без ввода оценочного значения. . Как вы можете видеть, указание оценочной стоимости не требуется.
Автозаполнение ячеек в MS Excel
Теперь заполним количества и цены. Поставим курсор в клетку С2 , запишем количество и нажмём «Ввод » на клавиатуре. Выделение переместится в С3 , там тоже запишем количество. Так, заполняем все продажи до декабря (С13 ). Аналогично поступим с ценами в столбце D . В нашем примере цены содержат копейки. В русифицированной версии Excel они отделаются от дробной части запятой. В оригинальной версии – точкой. Еще заполним выручку 2014 года. Вот так будет выглядеть заполненная таблица данных:
Слабость метода внутренней процентной ставки
Вы можете загрузить таблицу образцов и немного поиграть с цифрами или добавить свои собственные значения. Чтобы вставить дополнительные заказы, они должны быть вставлены между строкой 9 и строкой 10, так что текущее значение депо всегда находится в последней строке, и значения правильно передаются в формулу. Ничто не является совершенным, поэтому, конечно, не вычисление нормы прибыли с использованием метода внутренней ставки процента.
Перестрахование по внутренней процентной ставке Метод предполагает, что все распределения могут быть реинвестированы в внутреннюю процентную ставку. Если дивиденд выплачивается, то предположение состоит в том, что его можно инвестировать точно так же. В действительности, однако, это редко бывает. . Конечно, есть другие моменты, но, на мой взгляд, нет смысла еще более усложнять такую простую процедуру. Просто имейте в виду, что внутренняя процентная ставка всегда является питательной ценностью. Лучше всего знать метод внутренней процентной ставки, просто играя с примером.
Таблица заполнена исходными данными
Расчеты в Эксель
Все формулы в Excel начинаются сознака «=» . Если первый символ в ячейке – этот знак, программа понимает, что дальше будет следовать вычисляемая формула. Поставим курсор в ячейку B2 и вычислим выручку в январе 2015 года. В строке формул напишем «=» и кликнем левой кнопкой мыши на С2 , задав ссылку на продажи января. Дальше ставим знак умножения «*» и кликаем на D2 – ссылка на стоимость товара. Жмём Enter и видим, что в выделенной ячейке отобразился результат расчётов, а в строке формул для этой ячейки – введенная нами формула. Excel извлечёт значение из С2 и умножит его на значение в D2 . Если изменить любое из них – формула сразу же будет пересчитана.
Как заморозить верхние ячейки
Это очень простой учебник, в котором вы узнаете основные шаги по добавлению чисел в ячейки. Вы также узнаете, как настраивать, копировать и комбинировать их. Это видео даст вам инструкции по размещению верхних и нижних колонтитулов в ваших электронных таблицах, а также их номера.
В этом учебном пособии вы познакомитесь с условным форматом, инструментом, предназначенным для организации или поиска данных в соответствии с указанными вами правилами или критериями. Функция, если это логическая функция, которая позволяет проверить, являются ли определенные условия истинными или ложными.
Формула и результат вычисления
Давайте протянем эту формулу на все строки таблицы, как мы делали с заполнением месяцев. Тянем за квадратик в правом нижнем углу ячейки с формулой. Результат – программа просчитала выручку для всех месяцев. Обратите внимание, при протягивании изменяются адреса ссылок в формуле. Например, в ячейке В12 у нас формула: =C12*D12 , а не = C2*D2 .
Это видео познакомит вас с операциями и утилитами матричных функций, также известными как «формулы массива». В этом видео вы узнаете, как создавать динамические таблицы, также называемые динамическими таблицами, из данных ваших рабочих листов или книг калькуляции. Этот инструмент предназначен для фильтрации, анализа, обобщения и представления вашего материала простым и организованным способом.
Добавление паролей в документы
Это идентично добавлению чисел, если только оно не написано, а не в формуле. Помните, что две матрицы можно умножить только в том случае, если один из них является вектором строки, а другой - вектором столбца. Изменение вектора строки на вектор столбца называется транспозицией.
Теперь вычислим разницу между выручкой 2014 и 2015 годов. Назовём столбец F – «Разница». Методика написания формул не меняется, только вычислим В2-Е2 , протянем формулу для всех месяцев. Видим, например, что в январе 2015 года наша выручка на 7,5 ед. меньше, чем в 2014.
Графики Excel
Осталась последняя задача – построить временную диаграмму и визуально оценить динамику продаж.
Видео: подсчет ячеек с помощью строки состояния Excel
Результат должен заполнить все ячейки. Они могут быть помещены именами, и таким образом упрощается их выбор, когда они обширны. Но почти никогда не используются все. И на этом этапе важно понять фундаментальную концепцию работы с электронными таблицами: диапазоны ячеек. Просто набор из одной или нескольких ячеек. В дополнение к содержанию данных многие операции могут применяться и служить основой для других объектов в электронной таблице.
Диапазоны обычно обозначаются ссылками на ячейки их верхних левых и нижних вершин. Четыре ячейки, которые составляют диапазон, находятся в обратном видео. Однако, когда диапазон используется как аргумент или параметр функции, синтаксис и распознавание данных могут быть очень громоздкими.
Чтобы построить график, нужно выделить диапазон данных, на основании которых будет выстроена кривая. Наведите курсор мышки на ячейку А2, чтобы она указывала внутрь клетки, а не на её границу. Зажмите левую кнопку и тяните курсор к ячейке В13 . После этого отпустите кнопку мыши. Если вы всё сделали правильно, диапазон А2:В13 будет выделен тёмным цветом.
Мы проанализируем его в практической форме с использованием диапазона, который мы выбрали ранее. Прежде чем приступить к работе, имейте в виду, что названия этих инструкций приводят к путанице. В нем вы найдете пустое текстовое поле, Имена в книге, за которым следует пустой список, а внизу - таблица, которая позволяет вам выбрать диапазон ячеек, в который вы хотите указать имя.
Подсчет ячеек в диапазоне с помощью функции СЧЁТ
Однако это не обязательно, если, как было сделано в этом примере, диапазон был отмечен заранее. Теперь напишите в именах ящиков в книге, которые вы найдете значимыми для определения диапазона. Сразу же, в поле «Название», в крайнем левом углу панели формул, появится это имя, идентифицирующее диапазон, который все еще выбран.
Строим график на основании выделенных данных. Для этого, не ленте перейдите на вкладку «Вставка» , а там – «Диаграммы» — «Вставить график» — «График с маркерами» . На экране появится диаграмма изменения выручки по месяцам 2015 года. Первый выделенный столбец программа использовала для подписей по горизонтальной оси, а второй – для значений по вертикальной. Исходя из этой диаграммы вы можете делать выводы, например, о графике поставок товара на склады и торговые точки.
Какова цель использования диапазонов с именами? Одна из его основных утилит - облегчить выбор ранга; т.е. избегайте процесса щелчка по верхней левой ячейке вершин и перетаскивайте мышь в нижнюю правую вершину. Эта задача, хотя и легка в небольших наборах ячеек, довольно тяжелая с более длинными диапазонами.
Подсчет пустых ячеек в смежном диапазоне с помощью функции считатьпустоты
Имея имя, диапазон можно выбрать следующим образом: если он все еще выбран, снимите флажок «Начальный» с щелчка на любой ячейке за ее пределами. Там вы найдете список с именами всех сгенерированных диапазонов. Затем нажмите «Пуск» и нажмите кнопку «ОК».
Результат вычислений
Сохраняем расчеты
Чтобы сохранить наш документ – нажмите комбинацию клавиш Ctrl+S , в открывшемся окне нажмите «Обзор» . Открывается стандартное окно сохранения, где нужно указать расположение файла и задать его имя. Нажатием кнопки «Сохранить», подтверждаем создание нового файла.
Другое конкретное использование - это использование диапазона в качестве аргумента для функции. Специальный способ обращения к диапазону данных - использование меток. Практический пример позволит нам лучше проанализировать этот неиспользуемый инструмент.
Чтобы заставить вас выполнить численный расчет, вы должны запрограммировать тот, который отвечает за возврат всего того, что уже существует в ячейке, или, альтернативно, выполнение вычисления и сброс. Первый метод, который мы собираемся вам научить, - это тот, который отвечает за «передачу» уже существующей суммы.
Сохранение нового документа
Вот мы и выполнили минимальную задачу, а заодно познакомились с основными возможностями Эксель. Дальше мы будем расширять и совершенствовать полученные знания. В ближайших постах мы рассмотрим
Основное назначение Excel - выполнение расчетов с данными. Обработка данных происходит в ячейках содержащих формулы. Правила ввода простейших формул вы уже изучили в начале раздела. В данном подразделе мы рассмотрим общие принципы создания формул любой сложности и изучим примеры типичных расчетов в Excel.
В списках справа выберите также Рабочий лист и Изменить записи. Затем в правой части текущего окна введите простую строку. Как вы можете видеть, это относится к нескольким ячейкам. С одной стороны, тот, который должен включать в себя диапазон комментариев, и, во-вторых, тот, который содержит полный диапазон результатов.
После написания он закрывает окно редактора, и из электронной таблицы изменяется одно из значений, которые вмешиваются в функцию, например, изменяет единицы относительно количества А, например, до 50. Автоматически результат будет пересчитан на листе и, кроме того, если вы посмотрите вверху, вы увидите, что он также включает в себя общее, одинаково обновляемое.
Правила ввода формул
Ввод любой формулы всегда нужно начинать со знака равенства «=». В формуле могут находиться:
В Excel имеется большое количество функций, с помощью которых можно проводить вычисления и другие действия, относящиеся к самым разнообразным областям знаний. При использовании встроенной функции после знака «=» следует ввести ее имя, а затем в скобках аргументы функции - данные, которые используются в расчетах. Аргументами функции могут быть числа, ссылки на ячейки или диапазоны ячеек, а также другие встроенные функции (они называются вложенными). Рассмотрим конкретные примеры:
Очевидно, это выражение вам придется изменить, в зависимости от математического расчета, который вы хотите выполнить. Проверьте, как он обновляется, чтобы показать общее количество, и он будет делать это каждый раз, когда вы что-то измените в этой таблице. Если вы представляете его в своей компании или используете его часто, но обычно заполняете его вручную, у нас есть два волшебных слова, которые помогают много: автоматические вычисления.
Видео: использование функций счёт, счётесли и счётз
Независимо от того, насколько распространенным является получение, изменение, печать и отправка электронных таблиц, многие до сих пор не знают, сколько времени они могут сэкономить, предварительно подготовив электронную таблицу для автоматического выполнения вычислений при каждом вводе значений.
А2+В2 - сложение значений двух ячеек;
А1*0,8 - умножение числа из ячейки А1 на 0,8;
D1^2+1 - возведение числа из ячейки D1 в квадрат и прибавление единицы к результату;
СУММ(А1:А5) - суммирование значений из диапазона ячеек А1:А5. Это пример использования встроенной функции. Здесь СУММ - имя функции, А1:А5 - диапазон ячеек, ее единственный аргумент, заключенный в скобки;
МУМНОЖ(B1:B2;B7:C7) - вычисление произведения матриц B1:B2 и B7:C7. Как видно, данная функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов. Если функция имеет несколько аргументов, они отделяются друг от друга точкой с запятой. В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем листе и на других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа, отделенное штрихами, и поставить разделитель «!», например ‘Лист1’!В2, ‘Лист 3’!А1:С4. Штрих можно ввести, нажав клавишу «Э» при активной английской раскладке.
Разумеется, синтаксис всех встроенных функций Excel запомнить невозможно, да и делать это не нужно, поскольку в повседневной практике для решения наиболее часто встречающихся задач вы будете использовать всего несколько встроенных функций.
Опции вставки в документ встроенных функций находятся на вкладке Формулы в группе Библиотека функций. Функции распределены по категориям в зависимости от того, для решения каких типов задач они предназначены. Назначение той или иной функции можно прочесть на всплывающей подсказке, которая появляется при наведении указателя мыши на имя функции с меню (рис. 17).
Если вы хотите просмотреть полный список встроенных функций Excel, нажмите кнопку Вставить функцию, которая находится в строке формул. В открывшемся окне Мастера функций выберите в раскрывающемся списке Категория пункт Полный алфавитный перечень и щелчком выделите имя функции, чтобы ниже прочитать о выполняемых ею действиях.
Название встроенной функции можно ввести с клавиатуры (что крайне нежелательно ввиду высокой вероятности ошибки), вставить из соответствующего меню кнопок, расположенных в группе Библиотека функций на вкладке Формулы или же из окна Мастера функций. О двух последних вариантах будет упомянуто ниже.
| Рис. 17. Просмотр назначения функции |
Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная. Рассмотрим задачи, связанные с их использованием.
Простейшие расчеты
Функция суммирования данных является самой востребованной, именно поэтому задействовать ее в Excel проще всего.
Если данные расположены в одном столбце или строке, выделите их и нажмите кнопку . Результат сложения тут же отобразится внизу (в случае столбца) или справа (в случае строки) ряда. Выполните на нем щелчок, и в строке формул вы увидите, что Excel задействовал функцию =СУММ().
Если необходимо просуммировать значения из нескольких строк или столбцов (не важно, являются они смежными или нет), выделите ряды и вновь воспользуйтесь данной кнопкой. Суммы тут же отобразятся в следующей за рядом ячейке.
Для суммирования ячеек, находящихся в прямоугольной области или в разных частях документа, выполните следующие действия.
1. Выделите щелчком ячейку, в которой нужно отобразить результат суммирования.
2. Нажмите кнопку . При этом в ячейку будет помещена функция суммирования, а в ее скобках будет мигать курсор, указывающий на то, что нужно ввести аргументы.
3. Выделите нужный диапазон ячеек (при необходимости - несколько диапазонов, удерживая нажатой клавишу). При этом он будет охвачен бегущей рамкой, а скобках формулы появятся ссылки на диапазоны ячеек (рис. 18).
4. Нажмите для получения результата.
 |
| Рис. 18. Суммирование диапазона ячеек |
Даже после того, как ввод формулы будет завершен, вы всегда сможете отредактировать ее, изменив или добавив аргументы или функции. Так, чтобы изменить диапазон ячеек в только что рассмотренном примере, действуйте следующим образом.
1. Выполните двойной щелчок на ячейке, содержащей формулу. При этом диапазон, принимающий участие в расчетах, будет охвачен синей рамкой с маркерами в углах.
2. Подведите указатель мыши к нужному углу и, когда он примет вид двунаправленной стрелки, протащите границы рамки так, чтобы захватить новые ячейки (или, наоборот, исключить старые). При этом адрес диапазона в скобках формулы будет изменяться автоматически.
3. Нажмите для подсчета нового результата.
Щелкнув на стрелке кнопки , вы раскроете список команд, вызывающих функции, которые можно задействовать так же быстро, как и функцию суммирования. Схема действий при их использовании не отличается от последовательности шагов для функции суммирования. Ниже приводится краткое описание функций, вызываемых командами кнопки.
Результат работы некоторых из перечисленных функций можно видеть, не обращаясь непосредственно к самим функциям. Выделите интересующий вас диапазон и посмотрите вниз на строку состояния Excel. Слева от регулятора масштаба появятся значения суммы, количества ячеек в диапазоне и среднего арифметического (рис. 19).
Комплексные расчеты
Вы изучили примеры простейших расчетов в Excel. Теперь попробуйте разобраться с комплексной задачей, требующей комбинации действий, рассмотренных нами ранее.
Задача 1. Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. 20 без учета НДС.
В первую очередь необходимо создать таблицу и внести в нее расценки на услуги сотовых операторов так, как это сделано на рис. 20. Для подсчета конечной суммы с учетом НДС в конце таблицы следует обозначить отдельный столбец.
Выделите ячейку А1 и наберите в ней название работы. В ячейках следующей строки создайте заголовки таблицы. Воспользуйтесь кнопкой Перенос текста из группы Выравнивание, чтобы отрегулировать высоту строки. Ширину столбцов подберите вручную перетаскиванием границ их заголовков. С помощью кнопок выравнивания поместите текст в «шапке» посередине (относительно верхней и нижней границы ячейки) и по центру (относительно левой и правой границ).
Далее заполните сетку таблицы значениями, представленными на рис. 20. Поскольку тарифы представлены в расчете за минуту связи, планируемое время разговоров также необходимо ввести в минутах в столбцах Звонки внутри сети и Звонки в другие сети. Заполнять оба столбца вручную не нужно. Достаточно ввести значение в первой ячейке, а затем скопировать его на остальные с помощью автозаполнения.
В последнем столбце Суммарно с учетом НДС 18% будут находиться расчетные формулы, к созданию которых мы сейчас и приступим. Внизу столбца создайте заголовок Оптимум, ниже которого поместите функцию расчета минимального значения ряда, чтобы определить наименьшую стоимость. Для этого выполните щелчок на стрелке кнопки , задействуйте команду Минимум, выделите диапазон пока еще пустых ячеек в столбце Суммарно с учетом НДС 18% и нажмите.
Суммарные затраты будут складываться из абонентской платы, стоимости разговоров внутри сети и с абонентами других сетей, а также из налога на добавленную стоимость. Это нужно представить в виде формулы. Начнем расчеты с первого тарифного плана Минутка. К содержимому ячейки В3 (абонплата) нужно прибавить произведение ячеек С3 и Е3 (общая стоимость разговоров внутри сети) и произведение ячеек D3 и F3 (общая стоимость разговоров с абонентами других сетей). Так будет подсчитана сумма затрат без учета НДС. Чтобы прибавить к полученной сумме налоговую ставку, необходимо умножить результат на 18% и прибавить его к стоимости услуг. Это результат и должен отобразиться в ячейках последнего столбца.
Выделите ячейку G3, наберите в ней знак «=» и нажмите кнопку . При этом будет вставлена функция суммирования, автоматически определен диапазон ячеек, а его адрес будет выделен в скобках функции. Поскольку данный диапазон нам не подходит, нажмите, чтобы удалить ссылку из скобок. Затем выполните щелчок на ячейке В3, чтобы поместить ее адрес в формулу, и поставьте «;» для отделения следующего аргумента (слагаемого). Далее щелкните на ячейке С3 для ввода ссылки на нее в формулу, наберите знак умножения «*» и выделите ячейку Е3. Отделите новый аргумент точкой с запятой. Для ввода последнего слагаемого щелкните на ячейке D3, введите «*» и выделите ячейку F3. Так мы создали часть формулы, которая отвечает за подсчет суммарных затрат без учета налогов. Она должна выглядеть следующим образом: =СУММ(B3;C3*E3;D3*F3). Если вы где-то допустили ошибку, установите курсор возле нее щелчком мыши или с помощью клавиш движения и внесите исправления. Далее нужно прибавить налоговую часть формулы. Переместите курсор в конец за скобку и наберите «+». Ставка НДС составляет 18%. Следовательно, нужно умножить 18% на результат, полученный в первой части формулы. Наберите 18% и знак умножения «*», затем выделите первую часть формулы, не включая знака «=» (точно так же, как вы бы выделили текстовый фрагмент в Word), выполните на ней правый щелчок и задействуйте команду Копировать контекстного меню. Щелчком установите курсор в конец формулы, выполните правый щелчок и обратитесь к команде Вставить. Расчетная формула готова: =СУММ(B3;C3*E3;D3*F3)+18%*СУММ(B3;C3*E3;D3*F3). Нажмите, чтобы увидеть итоговую сумму.
Набирать вручную формулу в остальных ячейках не нужно. Воспользуйтесь автозаполнением - выделите ячейку с формулой и протяните рамку за маркер вниз. В результате формула будет скопирована в нижние ячейки, а ссылки в ней автоматически заменятся на адреса ячеек из той же строки.
Обратите внимание, автоматически посчиталась и минимальная сумма затрат. В нашем случае она соответствует тарифному плану Будь на связи. Попробуйте изменить начальные условия задачи и задать другое количество планируемых минут разговоров внутри сети и с абонентами других операторов. Пересчет суммарных затрат будет выполнен мгновенно, и вполне вероятно, что для новых значений оптимальным окажется другой тарифный план.
В завершении работы отформатируйте таблицу, чтобы она имела презентабельный вид. В первую очередь стоит перевести ячейки с тарифами в денежный формат. Для этого выделите все диапазоны с денежными величинами в столбцах Абонплата, Минута внутри сети, Минута в другие сети, Суммарно с учетом НДС 18% и Оптимум, щелкните на стрелке раскрывающегося списка Числовой формат в группе Число и выберите пункт Денежный. Выделенные ячейки будут переведены в денежный формат, но поскольку в Excel по умолчанию отображается два десятичных знака, в конце каждого числа появится незначащий ноль. Чтобы скрыть его, выделите диапазоны и щелкните на кнопке Уменьшить разрядность . Для некоторых ячеек может понадобиться скрыть и второй ноль после запятой. Сделать это можно аналогичным образом.
Теперь примените к таблице один из встроенных стилей. Для этого выделите сам прямоугольник таблицы, не включая название задания и ячейки подсчета оптимума, щелкните на кнопке Форматировать как таблицу в группе Стили и выберите понравившийся эскиз. В появившемся окошке нужно установить флажок Таблица с заголовками и нажать ОК. При необходимости уменьшите ширину столбцов перетаскиванием границ заголовков.
В заключение отформатируйте название задания и ячейку с конечным результатом. Выделите ячейки, на которые по длине распространяется название, и щелкните на стрелке кнопки и задействуйте команду Объединить ячейки. Затем задайте для ячейки с названием определенные параметры шрифта и цвет фона с помощью инструментов группы Шрифт или примените к ней один из стилей, воспользовавшись кнопкой Стили ячеек в группе Стили. Оформите и «шапку» ячейки с оптимальной суммой. После выполнения всех операций форматирования таблица с расчетом должна принять вид, подобный тому, что представлен на рис. 21.
Относительная и абсолютная адресация
Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор мы говорили об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8. Преимущество относительной адресации состоит в том, что при копировании ячеек и использовании автозаполнения ссылки в скопированных формулах меняются автоматически (относятся к ячейкам текущей, а не исходной строки), поэтому нет необходимости набирать вручную каждую формулу. Наглядный пример: в предыдущем примере мы набрали только одну формулу в первой ячейке столбца Суммарно с учетом НДС 18%, а затем воспользовались автозаполнением. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Для этого необходимо добавить перед номером строки и буквой столбца знак «$». Так, если сделать адрес ячейки В2 абсолютным, он будет выглядеть как $B$2. Также можно зафиксировать в ссылке адрес только столбца ($B2) или только строки (B$2). Это называется смешанной адресацией. Чтобы быстро изменить адресацию в готовой формуле, выполните по ней двойной щелчок, установите курсор на нужную ссылку и последовательно нажимайте клавишу для изменения типа адреса. Знак «$» можно добавлять в формулы и вручную с клавиатуры.
Рассмотрим использование абсолютной адресации на конкретном примере.
Вообразим следующую ситуацию. Вы являетесь оптовым поставщиком и для некоторых групп товаров, залежавшихся на складке, готовы предоставить скидку. Размер скидки варьируется в зависимости от того, насколько вы удачно договоритесь с покупателем. После переговоров вы должны предоставить ему прайс-лист, в котором цены будут скорректированы с учетом скидки, и по ним можно будет проводить дальнейшие расчеты.
В первую очередь вам необходимо сформировать сам прайс-лист со стандартными ценами, в одной из ячеек набрать размер скидки в процентах (его впоследствии можно будет варьировать), а также обозначить столбец, в ячейках которого будут находиться формулы, вычисляющие стоимость товара с учетом скидки. Пример такого прайс-листа приведен на рис. 22. Описание подробностей оформления опустим, поскольку вы уже приобрели в этом вопросе определенные навыки. Перейдем к сути проблемы.
Чтобы рассчитать цену с учетом скидки, необходимо от текущей цены отнять процент скидки. В нашем примере он равняется десяти. На первый взгляд решение задачи очень похоже на предыдущее, где прибавлялась процентная ставка НДС. Глядя на рис. 22, можно предположить, что в ячейке C4 необходимо из цены в ячейке В4 вычесть скидку, которая представляет собой произведение размера скидки из ячейки С1 на цену товара (В4). В виде формулы это запишется как =В4–С1*В4. Останется только распространить формулу на остальные ячейки столбца с помощью автозаполнения. Попробуйте выполнить описанные действия, и в конечном итоге вы обнаружите, что в вычислениях произошла ошибка. А заключается она в следующем.
При использовании автозаполнения ссылки в ячейках автоматически изменяются по мере копирования формул. В нашем случае формула, введенная в первую ячейку прайса, была правильной, но при попытке распространить ее на остальные ячейки столбца ссылка на ячейку с размером скидки стала меняться «сползая вниз» (превращаясь в С2, С3 и т.д.). Чтобы этого не происходило, ее адрес необходимо зафиксировать - сделать абсолютным. Для этого выполните на первой ячейке столбца скидки двойной щелчок (С4), поместите курсор на адрес ячейки, где находится величина скидки (в нашем случае это ячейка С1) и нажмите клавишу. При этом к номеру строки и букве столбца будет добавлен знак $ ($C$1), и адрес ячейки станет абсолютным - не изменится при автозаполнении столбца. В результате конечная формула будет выглядеть так: =В4–$C$1*В4. Теперь можно повторить процедуру автозаполнения, чтобы получить корректный результат. Щелкните на любой ячейке столбца скидок, чтобы убедиться в неизменности абсолютной ссылки. При изменении величины скидки весь ряд будет автоматически пересчитан.
Гораздо нагляднее использовать вместо абсолютной ссылки, содержащей знаки «$», имя ячейки, которое можно присвоить следующим образом: выделить щелчком ячейку и слева в строке формул набрать для нее уникальное имя. Щелкните на ячейке С1 (где указан размер скидки), слева в строке формул наберите Скидка. Затем в первой ячейке столбца цены со скидкой исправьте абсолютную ссылку $C$1 на имя ячейки Скидка. В результате должна получиться формула =В4–Скидка*В4. Осталось распространить формулу на все ячейки столбца с помощью автозаполнения.
В описанных выше примерах мы не упоминали об использовании кнопок группы Библиотека функций на вкладке Формулы и о Мастере функций для вставки встроенных функций в формулу. Эти моменты мы рассмотрим в примере расчета выручки от продажи товара, приведенном в подразделе «Построение графиков и диаграмм».
Ошибки в формулах
При работе с формулами в Excel нередко возникают ошибки, связанные не только с правильностью написания формулы, но и с корректным определением адресов ячеек и диапазонов с данными. Проведем краткий обзор способов выявления и устранения ошибок. Если Excel обнаружит ошибку в синтаксисе формулы (например, если между аргументами функции будет отсутствовать разделитель «;», будут пропущены или поставлены лишние скобки, обнаружится меньше аргументов, чем того требуется для данной функции), он выдаст сообщение об ошибке. Текст сообщения будет различным в зависимости от того, удалось Excel определить источник ошибки или нет. Когда система не может определить источник ошибки, появляется сообщение, представленное на рис. 23.
 |
| Рис. 23. Сообщение об ошибке, не распознанной Excel |
В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку.
Иногда системе удается определить, какие изменения следует внести в формулу, чтобы она стала синтаксически правильной. При этом появляется сообщение, подобное тому, что представлено на рис. 24. Проверьте, удовлетворяет ли предлагаемое системой исправление условиям расчета, и в зависимости от сделанного вывода, согласитесь с автоматическим исправлением, нажав Да, или же исправьте ошибку вручную в другом участке формулы.
 |
| Рис. 24. Сообщение о распознанной ошибке |
Иногда возникает ситуация, когда после введения формулы в ячейке вместо результата появляется текстовое сообщение об ошибке. Это связано с тем, что при проведении вычислений система столкнулась с каким-либо противоречием. Список наиболее часто встречающихся сообщений в ячейках приведен ниже.
Самая опасная ошибка - правильность ввода адресов ячеек и диапазонов в формулу. Система определяет только математические и синтаксические ошибки, но предугадать, данные каких именно ячеек должны присутствовать в формуле, не может. За эти должны внимательно следить вы.
Проще всего проверить правильность указания адресов ячеек и диапазонов в формуле можно следующим образом. Выполните двойной щелчок на ячейке содержащей формулу. При этом ячейки и диапазоны, входящие в нее, будут выделены в таблице рамками с маркерами, цвет которых соответствует цвету ссылки в формуле (рис. 25). Если был указан неправильный адрес, перетащите рамку на нужную ячейку (или измените ее размеры протаскиванием маркера при увеличении или уменьшении диапазона).
