Учебное пособие: Базовые навыки для работы в Excel

В этом примере с помощью Мастера функций вы создадите формулу для вычисления средних за полгода затрат на товары.

1.  Выделите ячейку К10 и наберите Среднее.

2.  Выделите ячейку К12, затем в строке формул щелкните по кнопке Вставить функцию . Откроется диалоговое окно Мастер функций – шаг 1.

Рис 6.2. 

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

4.  Выделите в этом списке строку СРЗНАЧ и щелкните на кнопке ОК. Откроется второе окно Мастера функций – шаг 2.

5.  В правой части поля Число1 виден цветастый квадрат. Щелкните по нему. Окно уйдет, но останется на экране. Это необходимо для того, чтобы окно не мешало вам выделить усредняемый диапазон ячеек.

6.  С помощью мыши выделите диапазон ячеек C12:H12. В процессе выделения диапазона указываются адреса выделенных ячеек. Теперь в поле Число1 появились адреса C12:H12.

Рис 6.3. 

7.  Нажмите цветастый квадрат в правой части поля Число1. Окно развернется.

8.  Щелкните на кнопке ОК. В ячейке К12 теперь находится среднее значение затрат на товары 19755,83.

Расчет максимума и минимума затрат на товары

1.  Выделите ячейку L10 и наберите Максимум.

2.  Выделите ячейку M10 и наберите Минимум.

3.  Для удобства просмотра в ленте Вид в разделе Масштаб через кнопку Масштаб в окне Масштаб выберите 75%. Нажмите ОК.

4.  По аналогии с расчетом средних затрат рассчитайте в ячейках L12, M12 максимум и минимум затрат с помощью статистических функций МАКС и МИН.

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

Допустим, в ячейках K20, L20, M20 необходимо рассчитать среднюю, максимум и минимум прибыли. Можно повторить предыдущие два пункта и получить результат за несколько десятков щелчков мыши.

Мы получим результат за 2 (два!) щелчка мыши.

1.  Курсором мыши в виде белого креста  выделите блок из трех ячеек K12:M12 с итогами по затратам на товары.

2.  На жирной линии границы выделенного блока получите курсор мыши в виде белой стрелки .

3.  При нажатой клавише Ctrl нажмите левую кнопку мыши. У белой стрелки появится плюсик .

4.  Перетащите мышью выделенный блок в ячейки K20:M20, отпустите кнопку мыши и отпустите клавишу Ctrl.

5.  В ячейках K20:M20 увидите среднюю, максимум и минимум прибыли.

На три формулы мы потратили всего два щелчка мыши.

Объяснение: средняя, максимум и минимум прибыли вычисляются по тем же формулам, что и средние, максимум и минимум затраты на товары. Исходные шесть ячеек для формул расположены слева одинаково для прибыли и затрат на товары. Нажатие клавиши Ctrl копирует формулы. При копировании работает правило относительной ориентации.

Присвоение имен блокам ячеек

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

В этом упражнении вы введете имя диапазона ячеек, набрав его с клавиатуры. Затем вы зададите имена диапазонов с помощью ленты Формулы.

1.  Выделите диапазон ячеек С11:H11 с приходами за 6 месяцев. Адрес первой ячейки блока появился в поле Имя .

2.  В строке формул щелкните в поле Имя и наберите Приход. Нажмите клавишу Enter.

Рис 6.4. 

Выделенный диапазон ячеек теперь называется Приход.

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

4.  В ленте Формулы в разделе Определенные имена выберите команду Создать из выделенного фрагмента. В появившемся окне Создание имен … флажок установлен напротив опции В столбце слева, что означает, что в качестве имен будут взяты заголовки левого столбца.

Рис 6.5. 

5.  Щелкните на кнопке ОК.

Просмотреть списки имен блоков можно, щелкнув на стрелке «вниз», находящейся рядом с полем Имя. Каждая строка теперь стала блоком ячеек с именами своих заголовков.

Рис 6.6. 

Как использовать имена в формулах

Имена блоков ячеек могут быть использованы в формулах вместо адресов ячеек. Адрес С11:H11 можно заменить именем соответствующего блока ячеек Приход, которое было ему присвоено ранее. Например, сразу понятно, что вычисляет формула МАКС(Приход), в отличие от формулы МАКС(C11:H11).

В этом упражнении вы найдете среднее, максимум и минимум прихода по данным бюджета фирмы «Загвоздка».

1.  Выделите ячейку K11 и щелкните по кнопке Вставить функцию в строке формул. Откроется диалоговое окно Мастер функций.

2.  Убедитесь, что в списке Категория выделена строка Статистические. Затем прокрутите список Выберите функцию и выделите имя функции СРЗНАЧ. Эта функция находит в выделенном блоке среднее значение.

3.  Щелкните на кнопке ОК. Откроется следующее окно.

Рис 6.7. 

4.  Щелкните в поле Число1 и выделите его содержание.

5.  Выберите в ленте Формулы команду Использовать в формуле. Откроется список имен блоков ячеек.

6.  Выделите в списке имя Приход.

Рис 6.8. 

Это имя появится в поле Число1 диалога Мастер Функций.

7.  Щелкните по кнопке ОК. Формула составлена, и результат 33795,33 появился в ячейке K11.

8.  Выделите ячейку L11 и наберите =макс(Приход). Эта формула определит максимальное значение в диапазоне ячеек Приход. Нажмите Enter.

9.  Выделите ячейку L11, Нажмите правую кнопку мыши и выполните команду Копировать.

10.  Выделите ячейку M11, Нажмите правую кнопку мыши и выполните команду Вставить.

11.  Через строку формул исправьте МАКС на МИН. Нажмите кнопку с зеленой галочкой в строке формул.

Теперь конец Вашего рабочего листа выглядит так:

Рис 6.9. 

6.4.  Формулы с относительными и абсолютными адресами

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

На листе Бюджет 2008 ежемесячный приход в ячейках С11:H11 был задан постоянными числами. Допустим, что приход ежемесячно возрастал на фиксированный процент, указанный Ростом объема продаж в ячейке C7 (1,50%).

1.  Выделите ячейку С7 и после числа 1,50 введите %.

2.  Выделите ячейку D11, наберите формулу =С11+С11*$C$7 и нажмите клавишу Enter. В ячейке D11 появилось значение прихода с учетом ежемесячного роста объема продаж (1,50%).

3.  Скопируйте маркером автозаполнения формулу ячейки D11 в диапазон ячеек E11:H11. Результат формулы во всех ячейках будет зависеть от содержимого ячейки $C$7.

Рис 6.10. 

4.  Пощелкайте по ячейкам D11:H11 и в строке формул посмотрите содержащиеся в них формулы: =С11+С11*$C$7, …, =G11+G11*$C$7. Какие адреса в формулах изменились, какие не изменились и почему?

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

Копировать информацию в другое место рабочего листа можно: с помощью кнопок Копировать и Вставить в ленте Главная; с помощью соответствующих команд контекстного меню правой кнопки мыши; перетаскивая мышью данные на новое место.

Копирование данных на другой лист осуществляется с помощью команд Копировать и Вставить или перетаскиванием содержимого ячеек с помощью клавиши Alt.

7.1.  Перемещение строки

1.  Щелкните по заголовку строки 20. Выделится вся строка с прибылью.

2.  Поместите указатель мыши на любом участке границы выделенного блока. Он примет вид белой стрелки . Перетащите рамку блока в строку 21.

Рис 7.1. 

7.2.  Вставка столбца

Допустим, Вы хотите вставить пустой столбец перед столбцом А.

1.  На листе Бюджет2008 нажмите комбинацию клавиш Ctrl+Home. Активной станет ячейка A1.

2.  Правой кнопкой мыши щелкните на заголовке столбца A. При этом выделится весь столбец A и откроется контекстное меню.

3.  Выберите команду Вставить. Новый столбец вставлен слева от столбца A.

7.3.  Перемещение столбца

Допустим, Вы хотите, чтобы пустой столбец стоял на втором месте.

1.  Щелкните на заголовке столбца B. Он выделится.

2.  Поместите указатель мыши на границу выделенного столбца. Он примет вид белой стрелки .

3.  Перетащите рамку выделенного столбца на место столбца A.

7.4.  Скопируйте данные на другой рабочий лист

В этом упражнении вы перенесете содержимое блока ячеек A10:F21 листа Бюджет2008 на лист Первый квартал. И сделаете это за два щелчка мыши.

1.  На листе Бюджет 2008 выделите блок ячеек A10:F21. Блок содержит слово Отчет, данные за три месяца март, апрель, май, значение прибыли за май. Первый щелчок мыши.

2.  Поместите указатель мыши на границу выделенного столбца. Он примет вид белой стрелки .

3.  Нажмите клавиши Ctrl и Alt, не отпуская их, нажмите левую кнопку мыши и перетащите рамку выделенного блока к ярлычку листа Первый квартал.

4.  Когда лист Первый квартал станет текущим, не отпуская кнопку мыши и клавиш, перетащите рамку блока ячеек в положение A10:F21. Отпустите кнопку мыши, отпустите клавиши Alt и Ctrl. Второй щелчок мыши.

Если появится диалог с вопросом: «Заменить содержимое конечных ячеек?», щелкните по кнопке ОК.

Теперь блок ячеек копирован на другой рабочий лист.

Посмотрите на строку приходов. Почему за март, апрель и май одинаковые приходы? Из листа Бюджет 2008 в ячейки Е11 и F11 были скопированы формулы =D11+D11*$D$7, =E11+E11*$D$7. Эти формулы ссылаются на ячейку $D$7. В этой ячейке на листе Первый квартал пустота, которая интерпретируется как число 0. Поэтому формулы повторяют значения в левых ячейках.

Рис 7.2. 

7.5.  Как копировать только значения формул

В этом упражнении вы в конечный лист перенесете только числа – результаты формул.

1.  Перейдите в лист Бюджет 2008.

2.  Выделите блок ячеек L10:N12 со средними, максимумами, минимумами приходов и затрат на товары.

3.  В ленте Главная в разделе Буфер обмена щелкните на кнопке Копировать .

4.  Перейдите на лист Лист3.

5.  Щелкните правой кнопкой мыши на ячейке А4. Ячейка А4 определяет левый верхний угол области вставки. В появившемся контекстном меню выберите команду Специальная вставка. Откроется диалог Специальная вставка.

Рис 7.3. 

6.  В группе Вставить выберите опцию Значения и щелкните на кнопке ОК. Окно закроется, и в блок ячеек будут скопированы только численные значения.

7.  По строке формул убедитесь, что в ячейках находятся только числа, а формул нет.

Рис 7.4. 

7.6.  Упорядочение по алфавиту

Упорядочим строки 15-18 статей расходов по алфавиту наименований.

1.  Выделите блок C15:J18 с заголовками и величинами расходов.

2.  В ленте Главная щелкните по кнопке Сортировка и фильтр, затем по кнопке Сортировка от А до Я .

Статьи упорядочились по алфавиту. Остальные строки остались без изменений.

Рис 7.5. 

3.  В панели быстрого доступа нажмите кнопку Сохранить с дискетой, чтобы сохранить документ.

Составляя документ, важно разместить в нем информацию с максимальной наглядностью, тогда она станет более доступной и выразительной. С помощью экспресс-стилей таблиц и ячеек, кнопки Формат по образцу и кнопок ленты Главная вы можете быстро и профессионально оформлять документы.

8.1.  Экспресс-стили таблиц

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

1.  На листе Бюджет 2008 выделите блок ячеек C6:D8.

2.  В ленте Главная в разделе Стили выберите команду Форматировать как таблицу.

3.  В открывшемся окне экспресс-стилей таблиц выберите Светлый 15 в третьей строке, первом столбце.

Рис 8.1. 

4.  В окне Форматирование таблицы отметьте флажок Таблица с заголовками и нажмите кнопку ОК.

Рис 8.2. 

5.  В ячейке D6 слово Столбец1 замените на пробел.

6.  Выделите две ячейки C6:D6.

7.  В ленте Главная в разделе Стили выберите команду Стили ячеек.

8.  В открывшемся окне экспресс-стилей ячеек выберите Заголовок3.

9.  Теперь данные выделенного блока выглядят так:

Рис 8.3. 

10.  Выделите блок ячеек C10:N21 со всеми заголовками и числами полугодового бюджета.

11.  В ленте Главная в разделе Стили выберите команду Форматировать как таблицу.

12.  В открывшемся окне экспресс-стилей таблиц выберите Светлый 15 в третьей строке, первом столбце.

13.  В окне Форматирование данных отметьте флажок Таблица с заголовками и нажмите кнопку ОК.

14.  В ячейке С10 слово Столбец1 замените на один пробел. В ячейке К10 слово Столбец2 замените на два пробела.

Ваш рабочий лист выглядит так:

Рис 8.4. 

8.2.  Копирование формата в другую ячейку

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

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

Однократное копирование формата

Отформатируем ячейку А10 со словом Отчет на листе Первый квартал так же, как выглядит ячейка D10 со словом Март на листе Бюджет 2008.

1.  Указываем ячейку D10 со словом Март на листе Бюджет 2008.

2.  Нажимаем на кнопку Формат по образцу  в ленте Главная. Указатель мыши примет вид кисточки со знаком белый плюс.

3.  На листе Первый квартал указываем ячейку А10 со словом Отчет. Ячейка А10 примет нужный вид.

Многократное копирование формата

Отформатируем ячейки C11, C13, C15, C17, C19, C21 со словами из хозяйственного списка на листе Первый квартал так же как выглядит ячейка C6 со словами Темпы роста на листе Бюджет 2008.

1.  На листе Бюджет 2008 указываем ячейку C6.

2.  Делаем двойной щелчок мыши по кнопке Формат по образцу. Указатель мыши примет вид кисточки со знаком белый плюс.

3.  Щелкаем по очереди по ячейкам C11, C13, C15, C17, C19, C21 на листе Первый квартал. Ячейки C11, C13, C15, C17, C19, C21 примут нужный вид.

4.  Щелкаем по кнопке Формат по образцу . Она отожмется. Указатель мыши в области таблицы примет вид белого креста.

8.3.  Форматирование с помощью ленты Главная

Если вам не подходит ни один из стандартных форматов, воспользуйтесь кнопками разделов Шрифт, Выравнивание, Число ленты Главная. Быстро задать стандартный формат для числа в ячейке можно при помощи следующих кнопок раздела Число: Финансовый формат, Процентный формат и Формат с разделителями. Каждый из этих форматов по умолчанию оставляет определенное количество десятичных знаков, которое может быть изменено кнопками Уменьшить разрядность и Увеличить разрядность.

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

В этом упражнении Вы измените выравнивание названий строк и расположите название по центру таблицы.

1.  Выберите ленту Главная.

2.  На листе Бюджет 2008 щелкните на заголовке столбца А, чтобы выделить его.

3.  В разделе Выравнивание щелкните на кнопке Выровнять текст по правому краю . Текст в выделенном столбце выровнен по правому краю.

4.  Щелкните по кнопкам Курсив  и Полужирный . Текст в столбце А наклонится и станет полужирным.

5.  Укажите ячейку C4 с датой и щелкните на кнопке По левому краю .

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

6.  Укажите ячейку C1 c названием Торговый бюджет… .

7.  В разделе Шрифт выберите шрифт Arial, размер 14, Полужирный , цвет шрифта  Синий.

Теперь заголовок стал заметен. Выровняем его ровно посередине столбцов C:N.

8.  Выделите блок C1:N1.

9.  В разделе Выравнивание щелкните на кнопке Объединить и поместить в центре . Заголовок расположится в центре выделенных столбцов.

Рис 8.5. 

10.  На листе Первый квартал выделите блок D11:F21 с числами.

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

12.  На листе Бюджет 2008 укажите двойным щелчком ячейку D8 и добавьте к числу 0,9 знак процента.

13.  Выделите ячейки D7:D8 и нажмите кнопку Процентный формат . В ячейках будет показано целое число процентов.

14.  Не снимая выделения, два раза нажмите кнопку Увеличить разрядность. Появятся сотые доли процентов.

8.4.  Форматирование с помощью команды Формат ячеек

Команду и окно Формат ячеек можно вызвать либо через контекстное меню правой кнопки мыши, либо через правый нижний угол разделов Шрифт, Выравнивание, Число ленты Главная , либо горячей клавишей Ctrl+1.

1.  Правой кнопкой мыши щелкните по ячейке C4 с датой. Выберите в контекстном меню команду Формат ячеек. Появится окно Формат ячеек.

2.  На вкладке Число в поле Числовые форматы отметьте в списке пункт Дата, в поле Тип отметьте в списке образец даты, который содержит название месяца и год с 4-мя цифрами.

Рис 8.6. 

3.  Нажмите кнопку ОК.

4.  Укажите ячейку D11 с приходом за март. Нажмите клавишу Shift и, удерживая ее, укажите ячейку N21 из большой таблицы со всеми числами полугодового бюджета. Блок D11:N21 выделится.

5.  Нажмите кнопку в правом нижнем углу раздела Число . Появится окно Формат ячеек.

6.  На вкладке Число в поле Числовые форматы отметьте последний пункт (все форматы).

7.  В поле Тип вместо Основной наберите # ##0”руб.” Проверьте, должно быть 11 символов.

8.  Щелкните на кнопке ОК.

Рис 8.7. 

9.  Снимите выделение клавишей Стрелка влево.

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

Рис 8.8. 

8.5.  Как изменить высоту строк и ширину столбцов

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

10.  Получите указатель мыши в виде двусторонней стрелки  на границе заголовков между столбцами А и В.

1.  Перетащите границу вправо, чтобы полностью были видны все надписи в столбце А.

Страницы: 1, 2, 3, 4