Основы Microsoft Excel 2016

Интерфейс и структура листа

Привет, ребята! Давайте сегодня познакомимся с редактором электронных таблиц Microsoft Excel 2016. Начнём мы сегодня с самых базовых элементов — интерфейса.

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

У каждой ячейки есть своё уникальное имя, которое составляется из названия столбика и номера строки. Если вы когда-нибудь играли в «Морской бой», то принцип формирования имени ячейки вы понимаете и так. Адрес выбранной ячейки отображается вот здесь, в поле «Имени ячейки». Если переключиться на другую ячейку, вы видите, что имя в поле меняется.

Если мы выделяем несколько ячеек, то в поле имя отображается вот такое значение: 8r на 4c. R — это у нас rows, строки, и C — это columns, столбики.

Ввод и редактирование данных

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

При нажатии клавиши Enter курсор ввода спускается на одну ячейку вниз в столбике. Это можно использовать для удобного и быстрого заполнения столбиков данными. Если же требуется двигаться вверх по столбику, можно использовать сочетание клавиш Shift+Enter, что, в свою очередь, поднимает нас вверх. То же самое можно делать при движении по строке, только в этом случае используется клавиша Tab, или сочетание клавиш Shift+Tab в случае, если нужно двигаться справа налево.

Удалить данные из ячейки можно с помощью клавиши Delete. Очищать, кстати, можно не только одну ячейку, но и несколько. Для этого выделяем ячейки, которые нужно очистить, и нажимаем клавишу Delete. Если нам нужно заменить данные в ячейке, достаточно просто выбрать эту ячейку и начать вводить новые данные. Нажимаем Enter, и новые данные сохраняются.

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

Автозаполнение ячеек

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

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

Удобство заключается в том, что если у нас есть несколько ячеек, заполненных однородными данными, мы можем выделить эти несколько ячеек. Обычно достаточно двух. И с помощью всё того же квадратика в нижнем правом углу выделенного диапазона, когда мы потянем вниз (обратите, пожалуйста, внимание), рядом с последней ячейкой будет небольшая подсказочка, что предполагается в этой ячейке в случае автозаполнения. Когда мы отпустим левую кнопку мыши, Excel автоматически заполняет ячейки данными.

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

Обратите внимание, что Excel попытался понять закономерность в тех данных, которые мы ввели. Как видите, я указал понедельник и среду. Закономерность примерно такая, что это каждый второй день. Поэтому он подставляет нам пятницу, пропуская четверг. После неё, пропуская субботу, идёт воскресенье, и так далее. Даты тоже подходят. Например, 1 января 2020 года, дальше скажем 5 января 2020 года. Растягиваем и получаем то, что нужно. Таким образом мы можем заполнять большое количество данных в автоматическом режиме, экономя собственное время.

Работа с формулами

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

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

А теперь давайте попробуем в столбике C посчитать сумму двух предыдущих ячеек. Для этого выделяем нужную ячейку. Сейчас в ячейке C1 мы попробуем посчитать сумму значения в ячейке A1 и... Для этого выделяем ячейку C1 и нажимаем на клавиатуре клавишу «равно». После этого, как вы можете заметить, в верхней части экрана меню приобретает серый цвет. Мы больше ничего не можем здесь нажимать. При этом в строке формулы появляется значок «равно», как и в самой ячейке. И теперь мы можем указать, с какими ячейками мы будем работать. Указывать можно двумя способами: написать от руки, например, A1, но, конечно же, гораздо проще...

Создание первой формулы

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

И теперь мы можем указать, с какими ячейками мы будем работать. Указывать можно двумя способами: написать от руки, например, A1, но, конечно же, гораздо проще мышкой, тем самым подставив название ячейки в формулу. Теперь, когда первая ячейка уже введена, мы можем выбрать, что мы будем с ней делать. Поэтому нажимаем «плюс», раз уж у нас сумма, и указываем следующую ячейку, которую будем прибавлять к первой.

Как вы можете заметить, ячейки красятся в разные цвета. При этом таким же цветом они отображаются в строке формул для вашего удобства, чтобы вы поняли, какие ячейки задействованы и где они стоят в вашей формуле. Формула читается так: значение ячейки C1 будет равно значению ячейки A1 плюс значению ячейки B1. Чтобы получить результат, нажимаем клавишу Enter.

Автоматизация вычислений

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

Поэтому удаляем это и сделаем правильно. Выделяем первую ячейку столбика C, в которой у нас уже есть нужная формула. И всё, что нам нужно сделать — схватить за квадратик в нижнем правом углу и растянуть до самого конца. Как видите, мы сразу получаем готовые результаты. Если выбрать любую из ячеек, в которые мы только что подставили значение, вы увидите, что формула изменяется в соответствии с номером строки, в которой эта формула стоит.

Практическая задача: учёт расходов

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

Прежде всего давайте немного увеличим масштаб. Это можно сделать с помощью ползунка в нижнем правом углу экрана. Но гораздо проще, конечно, с помощью зажатой клавиши Ctrl и колесика мышки увеличить себе масштаб до нужного состояния. Первым делом стоит правильно назвать столбики, чтобы таблица смотрелась красиво и понятно. Поэтому начнём со столбика «Месяц», далее столбик «Показания счётчика», следом идёт столбик с посчитанным расходом и закрывает таблицу столбик «Сумма».

Будем считать, что таблица у нас на один год. Естественно, заполнять её от руки абсолютно незачем. Вводим первый месяц, чтобы Excel понял, что от него хотят, и растягиваем его до состояния «декабрь».

Работа с шириной столбцов и данными

Подсказочка нам поможет. Как видите, в столбик B не влезает название. Чтобы всё у нас помещалось, наводим курсор мыши на разделительную стрелочку между столбиками. Вот она такая серенькая. Курсор принимает вид чёрного крестика со стрелочками слева и справа, и мы растягиваем её до необходимого размера. Примерно вот так. Либо второй вариант — автоматически подобрать по ширине содержимого. Достаточно двойного клика на разделительную линию.

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

Использование вспомогательной таблицы и расчет суммы

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

Обратите, пожалуйста, внимание на то, что числа, которые будут использоваться в дальнейшем в формулах, нельзя писать в тех же ячейках, где содержится какой-либо текст. Иначе это приведёт к ошибке. Поэтому число 15 в моём случае я ввёл в отдельную ячейку. Так как надпись с тарифами не помещается, делаем двойной клик — и происходит автоматический подбор по ширине.

Теперь мы можем посчитать сумму. Сумма равняется показаниям счётчика, умноженным на тариф. В нашем случае это 480 рублей для января. Ну а теперь очевидно: для того чтобы получить сумму для каждого месяца, мы можем просто растянуть эту формулу вниз и получить нули.

Относительные и абсолютные ссылки

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

При этом ячейка с показаниями счётчика выбирается правильно, потому что она располагается таким же образом относительно суммы. А значение тарифа принимает значение 0, потому что ячейка, из которой берётся значение тарифа, в настоящее время пуста. Чтобы этого избежать и всё сделать правильно, вернёмся в первую ячейку и отредактируем формулу следующим образом: подставим значок доллара перед названием столбика G и перед номером строки 1. Таким образом мы превращаем ссылку в абсолютную.

Абсолютные ссылки

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

Использование функций

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

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

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

Работа с функциями MIN, MAX и СРЗНАЧ

Сейчас мне нужна функция, которая будет подсчитывать минимальное значение. Она называется функция MIN. Нажимаю кнопку «Найти», и в списке появляется искомая функция. Двойной клик открывает её на редактирование.

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

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

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

В ячейке для редактирования мы увидим, что её значение сейчас равняется функции MIN с аргументом в виде диапазона ячеек, начиная с ячейки D2 и заканчивая ячейкой D13. Аналогичным образом находим максимальное значение: поиск функции MAX, открываем её, очищаем первую строчку, нажимаем на кнопочку со стрелочкой и выделяем нужный нам диапазон. Нажимаем Enter и кликаем ОК.

Функция для среднего значения — это функция СРЗНАЧ. Если хотите, вы можете набрать «среднее», и то, что нам нужно, будет найдено в поиске. Функция называется СРЗНАЧ, соответственно, среднее значение. Очищаем первую строчку, кнопочка со стрелочкой, и выбираем нужный диапазон. Enter и ещё раз Enter. Среднее значение всех наших сумм равняется вот такому вот числу.

Оформление таблицы

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

Для этого выделяем нашу таблицу. И вот здесь в разделе «Шрифт» находим кнопочку с границами. Рядом находится стрелочка, при нажатии на которую появляется выпадающий список. Нам сейчас нужны «Все границы». Как видите, границы прочертились чёрным, по умолчанию они светло-серые. То же самое сделаем для остальных. Таким вот образом.

Давайте попробуем сделать это ещё красивее с помощью выделения заголовка жирным. Выделяем заголовки и нажимаем горячие клавиши Ctrl+B, от слова bold — жирный. То же самое для тарифа и названия функций.

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

На этом наше знакомство с редактором электронных таблиц Excel 2016 закончено. Все вопросы задавайте в комментариях, подписывайтесь на наш канал, чтобы увидеть ещё больше образовательных роликов. Пока!