, расположенные слева от ярлычков.
Если в книге создано большое количество листов, можно пометить ярлычки важнейших из них определенным цветом, чтобы было проще ориентироваться. Щелкните правой кнопкой мыши на ярлычке, наведите указатель мыши на подменю Цвет ярлычка и в открывшейся палитре выберите понравившийся оттенок.
Форматирование ячеек
Форматирование содержимого ячеек Excel ничем не отличается от форматирования текстовых таблиц в Word. Идем в группу Шрифт на вкладке Главная. Видим там кнопки шрифтов, заливки ячеек и отображения границ. Поскольку с ними вы уже хорошо знакомы, не будем останавливаться на рассмотрении каждого из них. Общий принцип работы следующий: сначала нужно выделить ячейку или диапазон, а затем нажать кнопку в группе Шрифт.
В группе Выравнивание вкладки Главная расположены инструменты выравнивания содержимого ячейки относительно границ. Некоторые из них мы уже знаем. Остановимся на новых.
Вот эти кнопки позволяют сориентировать данные в ячейках относительно верхней, нижней границы или по центру.
С помощью команд меню кнопки Ориентация (нажмите ее стрелочку) можно задать направление поворота текста в ячейке.
Часто, если нужно написать заголовок, приходится объединять ячейки и текст одной из них помещать по центру всей области объединения. В Excel есть волшебная кнопка Объединить и поместить в центре .Для создания заголовка наберите текст в ячейке, выделите нужный диапазон, который собираетесь объединять, включая ячейку с текстом, и нажмите эту кнопку.
На рис. 2.15 я вам привела примеры всех перечисленных приемов форматирования. Тренируйтесь, делайте у себя то же самое.
Рис. 2.15. Различные способы форматирования ячеек
Когда вы начнете работать с таблицами, то быстро поймете, что все время приходится менять ширину столбцов и высоту строк, чтобы содержимое ячеек полностью показывалось на экране, не перекрывая при этом другие ячейки.
Мы уже научились таскать границы вручную, сейчас я расскажу, как сделать это автоматически. Чтобы подобрать ширину ячейки или диапазона, выделите ее или его, нажмите кнопку Формат в группе Ячейки на вкладке Главная и выполните команду Автоподбор ширины столбца. Для выравнивания высоты ищите команду Автоподбор высоты столбца в меню этой же кнопки.
Теперь нажмите кнопку Стили ячеек в группе Стили вкладки Главная. Что такое стиль ячейки? Это набор параметров форматирования. А если по-русски, то вы можете выбрать: шрифт, цвет заливки и шрифта, наличие и вид границы, числовой формат ячейки… Просто ищите то, что понравилось в коллекции (рис. 2.16), и щелкайте кнопкой мыши на нужном варианте.
Рис. 2.16. Выбор стиля ячеек
Кстати, вы можете применять стили оформления Excel и к отдельным ячейкам, и к таблице целиком.
Стиль таблицы как единого целого определяет оформление заголовка, границы и заливку ячеек. Если вы уже ввели все данные в таблицу, а теперь решили ее оформить, выделите таблицу и нажмите кнопку Форматировать как таблицу в группе Стили на вкладке Главная. Выбрав в коллекции подходящий стиль, щелкните кнопкой мыши на его эскизе. В открывшемся окошке необходимо установить флажок Таблица с заголовками, если текст заголовков вы уже ввели. В противном случае поверх вашей выделенной таблицы будет вставлена строка заголовка с именами столбцов по умолчанию Столбец 1, Столбец 2 и т. д., которые нужно будет переименовать.
Вы можете сначала задать стиль таблицы, а затем начать заполнять ее. Для этого выделите примерную область вашей будущей таблицы, нажмите кнопку Форматировать как таблицу, выберите подходящий стиль и в открывшемся окошке просто нажмите ОК. В нижнем правом углу заготовки вы увидите маленький треугольник. Чтобы изменить размеры заготовки таблицы, наведите на него указатель мыши, и когда он примет вид , протащите границу по горизонтали или вертикали, чтобы увеличить или уменьшить количество оформленных ячеек. Перетаскивание границы можно выполнять только по вертикали или горизонтали. Если вам нужно увеличить и число строк, и число столбцов, протяните сначала нижнюю границу вниз, а затем правую вправо. Определившись с размерами, заполните форму. При желании вы можете комбинировать стиль таблицы со стилями отдельных ячеек в ее составе. Вот смотрите, как я оформила свой расчет с использованием стиля из коллекции Excel (рис. 2.17).
Рис. 2.17. Использование встроенного стиля таблицы
Формат чисел
Наконец-то добрались до формата чисел. Я уже не раз о нем упоминала, теперь разложу все по полочкам (хотя общий смысл вы уже могли понять).
Числа в Excel могут отображаться в различных форматах. В этом разделе мы поговорим о том, какие существуют форматы чисел и как задать для ячейки определенный числовой формат.
По умолчанию в Excel установлен общий формат ячеек. Это значит, что в любую чистую ячейку вы можете смело вводить любые данные: текст, числа или формулы. Вас поймут и данные запомнят. А сейчас мы узнаем (или повторим) те особенные способы введения чисел, для которых Excel автоматически меняет формат.
• Если вы вводите число через запятую, Excel трактует это как десятичную дробь.
• При вводе чисел через точку, слэш или тире в ячейке устанавливается формат даты, и данные отображаются в виде даты.
• Если вы вводите число через двоеточие, автоматически устанавливается формат времени.
• Если вы хотите представить введенное число в процентном формате, добавьте после него знак %.
• Чтобы представить число в денежном формате (в рублях), наберите после него р… В денежном формате к числу добавляется знак денежной единицы, а каждые три цифры числа отделяются друг от друга пробелами для вашего же удобства.
• Если вы отделите пробелами в числе по три цифры, например 36 258, 2 739, вы переведете его в числовой формат. Он аналогичен денежному, за исключением того, что на экран не выводится знак денежной единицы.
• Если в ячейку нужно поместить простую дробь, например 3/5 или 11/2, поступите следующим образом. Введите целую часть дроби (для дробей меньше единицы нужно набрать ноль), затем нажмите пробел и наберите дробную часть, используя слэш, например 1 4/5. В результате в ячейке будет установлен дробный формат и запись отобразится без изменений (не превращаясь в десятичную дробь).
Excel считает с точностью до 15 знака после запятой, однако в ячейках выводится только два десятичных знака (при необходимости эту настройку можно изменить, скоро расскажу как). Полностью число можно увидеть в строке формул, выделив ячейку.
Изменить формат числа можно, не только по-разному набирая числа, но и с помощью специальных инструментов. За установление числовых форматов ячеек отвечают кнопки, которые находятся в группе Число на вкладке Главная.
• Откройте меню кнопки Числовой формат . Вы можете выбрать формат чисел для выделенной ячейки или диапазона. С большинством форматов вы уже познакомились. Сделаю лишь замечание относительно процентного формата. При его выборе число, находящееся в ячейке, будет умножено на 100, и к нему добавится знак %.
Новыми для вас являются экспоненциальный и текстовый форматы. Коротко о каждом из них.
Любое число можно представить в виде десятичной дроби, умноженной на 10 в степени, равной количеству знаков после запятой. Так, число 1230 можно записать в виде 1,23 × 103, а число 0,00015 как 1,5 × 10-4. Точно так же поступит и Excel, если вы выберете экспоненциальный формат. Только он использует разделитель Е в записи вместо ×10. Так, число 1230 в экспоненциальном формате будет выглядеть как 1,23Е+03, а число 0,00015 как 1,5Е-04.
Текстовый формат можно использовать не только для текста. Он полезен, когда требуется, чтобы Excel распознавал число как текст, и оно не принимало участие в расчетах. При выборе текстового формата число в ячейке будет выровнено по левому краю, как и текст.
• Кнопка переводит содержимое ячейки в финансовый формат. По умолчанию выбран российский рубль. Если вам нужен доллар или евро, щелкните на стрелке этой кнопки и выберите нужный вариант.
• Кнопка переводит содержимое выделенных ячеек в процентный формат.
• С помощью кнопки можно задать для ячеек числовой формат с разделителями-пробелами по три знака.
• С помощью кнопок Увеличить разрядность и Уменьшить разрядность можно увеличить или уменьшить количество знаков, отображаемых после запятой.
Проведение расчетов
Ну вот, теперь мы подходим к самому главному. Ведь основное назначение Excel – это расчеты. А расчеты – это формулы. Правила ввода простейших формул вы уже изучили выше. Сейчас мы попробуем разобраться с формулами посложнее.
Правила ввода формул
Правило 1. Ввод любой формулы всегда нужно начинать со знака равенства =.
Правило 2. В формуле могут находиться только:
• знаки арифметических действий: +, -, *, /, ^ (знак возведения числа в степень) и %;
• скобки (), [];
• числа, текст (он берется в кавычки);
• ссылки на ячейки и диапазоны ячеек (как на текущем листе, так и на других листах книги);
• встроенные функции.
Прочитали? Я понимаю ваше замешательство. Сейчас попробую объяснить подробнее. Что такое встроенная функция? Идем в строку формул, нажимаем кнопку Вставить функцию . В открывшемся окне Мастера функций выбираем в раскрывающемся списке Категория пункт Полный алфавитный перечень. Любуемся. Если вы мышкой выделите в списке любую из этих функций, то рядом появится подсказка, что именно эта функция делает.
Как видите, разнообразие функций огромно. Не расстраивайтесь, если не понимаете, зачем они все нужны. Я тоже далеко не все из них знаю, а пользуюсь буквально десятком. И вам, скорее всего, больше знать не понадобится.
Давайте рассмотрим конкретные примеры формул. Еще раз напоминаю, формула всегда начинается со знака =.
=А2+В2 – сложение значений двух ячеек.
=А1*0,8 – умножение числа из ячейки А1 на 0,8.
=D1^2+1 – возведение числа из ячейки D1 в квадрат и прибавление единицы к результату.
Пример использования встроенной функции:
=СУММ(А1:А5) – суммирование значений из диапазона ячеек А1:А5.
Еще немного теории. Вспомним еще раз школьный курс алгебры. Функция – это такая выдуманная математиками штука, которая описывает зависимость между двумя величинами. Помните, в школе писали f(x) = х2или у = х2, где у – это функция, ах – аргумент функции. Y зависит от х. Меняем х, меняется и у. То есть функция зависит от аргумента.
Смотрим на наш пример. СУММ – имя функции. Аргумент функции А1:А5 – диапазон ячеек. Аргумент всегда заключается в скобки.
Впрочем, всю эту теорию про функции и аргументы вы можете благополучно пропустить мимо ушей и просто знать, что конкретная функция СУММ записывается вот в таком виде: СУММ(число 1; число 2…), где число 1, число 2 и т. д. – аргументы функции, то есть те числа, которые нам нужно просуммировать.
В случае СУММ(А1:А5) у функции аргумент один – ссылка на диапазон ячеек. Можно было бы написать так: СУММ(А1:А5; В7), тогда аргумента у функции стало бы два, и суммировались бы диапазон ячеек от А1 до А5 и число из ячейки В7.
Что мы можем использовать в качестве аргументов функции?
• Числа. Просто числа. Так и пишем СУММ(3; 1234; 265758494040). Банальный калькулятор.
• Ссылки на ячейки. Это мы уже рассмотрели. Функция берет число из ячейки, адрес которой вы укажете. Удобно, что число можно менять, и сумма будет пересчитывать автоматически
• Ссылки на диапазон ячеек. Это мы уже тоже видели.
• Ссылки на ячейки и диапазоны ячеек, которые находятся на другом листе. Рассказываю подробнее. В файле (книге) может быть несколько листов. Допустим, вы на одном листе считаете свой доход. Получаете финальное число. А на другом листе вы считаете расходы, и вам нужно, чтоб у вас перед глазами было число, которое покажет вам, сколько денег у вас осталось. Что нужно сделать? Правильно, из доходов вычесть расходы. Доход на Листе1, расходы на Листе2. Что делаем? В формулу на Листе2 вставляем не просто ссылку В2 (тогда Excel возьмет число из ячейки В2 Листа2), а 'Лист1'!В2, то есть В2 из Листа1 Обратите внимание, Лист1 отделен штрихами, а после него стоит разделитель – !. Штрих вводится на английской раскладке с помощью клавиши, на которой написана буква «Э». Если нужен диапазон из другого листа, то нужно написать 'Лист 1'!А1:С4.
Ну, я думаю, вы уже немного разобрались с тем, что представляет собой функция в Excel. Это довольно сложная штука, но если ее не бояться, то можно посчитать все, что угодно. Главное – удобство, не нужно эти функции из головы выдумывать, все уже придумали и в библиотеку сложили.
Давайте расскажу, как вставить функцию в формулу.
Первый способ: нажимаем кнопку Вставить функцию в строке формул.
Второй способ: идем на вкладку Формулы в группу Библиотека функций. Там функции распределены по категориям в зависимости от того, для чего они предназначены. Что делает и как правильно записывается та или иная функция, можно прочесть во всплывающей подсказке, которая появляется при наведении указателя мышки на имя функции в меню (рис. 2.18).
Рис. 2.18. Смотрим, что может сделать функция
Третий способ: жмем треугольничек рядом с кнопкой Сумма в группе Редактирование на вкладке Главная (рис. 2.19). Тут находятся те функции, которые используются чаще всего.
Рис. 2.19. Эти функции нужны чаще других
Четвертый способ: вводим название функции ручками с клавиатуры. Плохой способ. Легко ошибиться.
Давайте мы с вами подробно рассмотрим функции, которые вынесены в меню кнопки Сумма в группе Редактирование на вкладке Главная (см. рис. 2.19). Не зря же их считают самыми популярными.
Сумма. Мы уже долго рассматриваем эту функцию, и не зря – она безусловный лидер программы Excel. Ее использование упрощено до предела.
Если вам нужно просуммировать данные, которые расположены в одном столбце или строке, выделите их и нажмите кнопку . Результат сложения тут же отобразится внизу (в случае столбца) или справа (в случае строки). Щелкните кнопкой мыши на ячейке с результатом, в строке формул вы увидите функцию =СУММ(). Вот так одним движением руки можно просуммировать свои затраты в магазине.
Если необходимо просуммировать значения из нескольких строк или столбцов (не важно, являются они смежными или нет), выделите ряды и вновь жмите кнопку . Суммы тут же отобразятся в ячейке, следующей за рядом.
Для суммирования ячеек, находящихся в прямоугольной области или в разных частях документа, сделайте вот что.
1. Выделите щелчком кнопки мыши ячейку, в которой нужно отобразить результат суммирования.
2. Нажмите кнопку . При этом в ячейке вы увидите =СУММ(), где в скобках будет мигать курсор. Это вам мягко намекают на то, что нужно ввести аргументы функции.
3. Выделите нужный диапазон ячеек (при необходимости – несколько диапазонов, удерживая нажатой клавишу Ctrl). При этом он будет охвачен бегущей рамкой, а в скобках формулы появятся ссылки на диапазоны ячеек (рис. 2.20).
4. Нажмите Enter для получения результата.
После того как вы введете формулу, если вам нужно будет в ней что-то изменить, то всегда можно это сделать. Например, вам захочется поменять диапазон ячеек, которые нужно просуммировать (см. рис. 2.20).
Рис. 2.20. Суммирование диапазона ячеек
Что делаем?
1. Дважды щелкаем на ячейке, содержащей формулу. При этом диапазон, принимающий участие в расчетах, будет охвачен синей рамкой с маркерами в углах.
2. Подводим указатель мыши к нужному углу и, когда он примет вид стрелки , протаскиваем границы рамки так, чтобы захватить новые ячейки (или, наоборот, исключить старые). При этом адрес диапазона в скобках формулы будет изменяться автоматически. Впрочем, вы можете и просто набрать новые адреса ячеек в формуле.
3. Нажимаем Enter для подсчета нового результата.
Что еще есть в списке кнопки (см. рис. 2.19)?
• Среднее. Вызывает функцию =СРЗНАЧ(), с помощью которой можно подсчитать арифметическое среднее диапазона ячеек (просуммировать все данные, а затем разделить на их количество).
• Число. Вызывает функцию =СЧЁТ(), которая определяет количество ячеек в выделенном диапазоне.
• Максимум. Вызывает функцию =МАКС(), с помощью которой можно определить самое большое число в выделенном диапазоне.
• Минимум. Вызывает функцию =МИН() для поиска самого маленького значения в выделенном диапазоне.
Кстати, результат работы некоторых из этих функций можно видеть, не обращаясь к ним самим непосредственно. Выделите интересующий вас диапазон и посмотрите вниз на строку состояния Excel.
Слева от регулятора масштаба появятся значения суммы, количества ячеек в диапазоне и среднего арифметического (рис. 2.21). Очень удобно, не правда ли?
Рис. 2.21. Расчет в строке состояния
Относительные и абсолютные адреса
Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными.
Попробую объяснить разницу. Помните, когда я писала об автозаполнении выше, то, рассматривая рис. 2.2, мы убедились в том, что можно в одной строке запомнить формулу =В2*С2, а потом скопировать ее навесь столбец. При этом умный Excel в каждой следующей строчке сам поменяет цифры. Строчкой ниже формула превратится в =ВЗ*СЗ и т. д.
Так вот, это потому, что в данном случае адреса ячеек относительные. То есть Excel запомнил, что для того, чтобы получить нужное произведение, необходимо взять две ячейки (В2 и С2) левее той, в которой пишется результат (D2), и их содержимое перемножить. Для каждой следующей строчки программа опять же возьмет две ячейки, левее ячейки с результатом.
Часто бывают случаи, когда во всех формулах используется одна и та же ячейка. Например, вбиваем в ячейку А1 курс доллара, а потом во всей таблице нам нужно будет цену товара умножить именно на то, что стоит в ячейке А1. Вот тут-то нам и понадобится абсолютный адрес.
Абсолютный адрес не будет изменяться при копировании или автозаполнении ячеек. А выглядит он так: $А$1. Это абсолютный адрес ячейки А1.
Чтобы быстро сделать относительный адрес в формуле абсолютным, дважды щелкните кнопкой мышки на формуле, поставьте курсор на нужную ссылку и нажмите клавишу F4. Но можно знак $ добавлять в формулы вручную с клавиатуры.