Создание новой папки. Переименование папки

Наводим курсор мыши на любое свободное место Рабочего стола и жмём правую кнопку мыши. В открывшемся окне меню наводим курсор мыши на «Создать», а в следующем появившемся окне меню наводим курсор мыши на «Папку» и жмём левую кнопку мыши.

Появилась папка с выделенным названием «Новая папка». В момент выделения текста в папке меняем её текущее название «Новая папка» на наше название «Задание», набрав текст с клавиатуры и нажав клавишу «Ввод» (Enter).

…вбиваем с клавиатуры «Задание»

Примечание: если мы позже захотим переименовать папку «Задание» в любое другое название, можем навести курсор на эту папку и нажать правую кнопку мыши. В появившемся окне меню выбрать «Переименовать». Так текст имени папки «Задание» будет снова выделен и готов к новому переименованию.

Откроем созданную папку «Задание».

Для этого наведём курсор мыши на папку «Задание» и нажмём правую кнопку мыши. В появившемся окне меню наводим курсор на «Открыть» и жмём на левую кнопку мыши.

Откроется пока пустая папка «Задание» в примерно таком виде (конкретный вид будет зависеть от настроек Вашего компьютера).

Пустая папка.

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

Создание нового файла Excel. Переименование файла

Наводим курсор мыши на любое свободное место в открытой папке «Задание» и нажимаем правую кнопку мыши. В окне меню выбираем «Создать», а в следующем открывшемся окне меню выбираем «Лист Microsoft Excel»

В ранее пустой папке «Задание» теперь появился новый файл с выделенным именем «Лист Microsoft Excel». Когда имя файла выделено так, оно готово к переименованию – то есть мы можем набрать любое новое имя файла с клавиатуры и оно заместит собой имя «Лист Microsoft Excel».

Вбиваем с клавиатуры наше наименование «Сотрудники», жмём Ввод (Enter) и получаем созданный нами файл Excel с нужным именем «Сотрудники» в папке Рабочего стола которую мы назвали «Задание».

Переименовываем созданный файл Excel в «Сотрудники»

Копирование и вставка файлов. Перенос файлов из других папок, флэш-карт и т. п.

В эту же папку скопируем и файлы с исходной информацией, полученные от отдела кадров и от секретаря. Допустим, эти файлы нам передали на флэшке или сохранили в папке общего доступа. Для копирования файлов в папку «Задание» сначала откроем флэшку или папку общего доступа (в нашем случае – флэшка Transcend), кликнем в открывшейся папке по нужному файлу правой кнопкой мыши и нажмём «копировать».

После чего откроем папку «Задание», кликнем правой кнопкой мыши на свободное место в папке и выберем «вставить».

В папке «Задание» появился скопированный с флэшки файл.

Аналогично скопируем с флэшки и второй файл. Все три файла (два файла с исходной информацией и созданный нами новый файл «Сотрудники») теперь лежат в одной папке «Задание».

Все три файла оказались в папке «Задание»

Заполнение файла Excel информацией

Теперь приступим к заполнению пустого файла «Сотрудники» информацией, как требуется от нас в задании. Для начала открываем файл аналогично тому, как ранее открывали папку: наводим курсор мыши на файл «Сотрудники» и нажимаем правую кнопку мыши. В появившемся окне меню выбираем «Открыть». Открыть файл Excel можно также и двойным щелчком по левой кнопке мыши.

Открываем созданный нами файл Eхcel «Сотрудники».

Знакомство с интерфейсом

Файл откроется чистым листом примерно такого вида (конкретный вид зависит от настроек компьютера и версии Excel, расположение видимых кнопок можно редактировать в настройках, но большинство пользуется настройками по умолчанию). Познакомимся с тем, что мы видим:

1 – Кнопки управления основным окном программы находятся в верхней правой области окна Excel. Они позволяют закрыть открытый файл, свернуть его, свернуть в окно, развернуть окно на весь экран.

2 – Панель быстрого доступа находится в верхней левой области окна Excel. Если мы часто используем одни и те же команды, можно вынести кнопки этих команд сюда и запускать команды отсюда. Например, часто используют команду Сохранить изменения. Если её не нажать, то всё что Вы проделали в файле после его открытия – исчезнет после закрытия файла. Часто используют и команду Отменить последнее действие. Например, если случайно удалили нужную ячейку или нужный столбец, то нажимаем на эту кнопку и возвращаемся в состояние, когда эта ячейка или столбец ещё не удалены. При желании мы можем добавить на панель быстрого доступа любые другие команды, нажав на кнопку редактирования Панели быстрого доступа.

3 – Вкладки. Под панелью быстрого доступа расположена лента с доступными вкладками. На каждой вкладке свой набор команд по умолчанию. Этот набор команд можно при желании отредактировать: добавить или убрать команды. Не рекомендую новичкам заниматься подобным редактированием, всё самое необходимое уже настроено по умолчанию. Но если действительно надо добавить команду, делаем это так: наводим курсор на любую точку Вкладки с командами или Панели быстрого доступа и нажимаем правую кнопку мыши. Появится окно меню, в котором наводим курсор на «Настройка Ленты». Нажимаем клавишу «Ввод» на клавиатуре или на левую кнопку мыши, как удобнее.

После этого попадем в окно настроек параметров Excel, где можем управлять отображением вкладок и команд в этих вкладках или создать свою вкладку со своим набором команд.

4 – Команды вкладки расположены под лентой с вкладками. При открытии файла мы видим команды вкладки «Главная», так как вкладка Главная открывается по умолчанию. Если перейдём на другую вкладку, наведя курсор на название другой вкладки и нажав левую кнопку мыши, то мы увидим другой набор команд.

Например, на вкладке «Формулы» увидим примерно такой набор команд по умолчанию (конкретный набор зависит от версии Excel).

5 – Заголовки столбцов таблицы. Для доступа к отдельным ячейкам внутри электронной таблицы используются имена ячеек. Имя ячейки – это комбинация её столбца и её строки. Имена столбцов отображаются в их заголовках латинскими буквами, по умолчанию стандартная таблица Excel содержит столбцы от А до XFD (всего 16 384 столбца).

6 – Номера строк таблицы. По умолчанию стандартная таблица Excel содержит 1 048 576 строк, пронумерованных подряд.

7 – Поле имени объекта. По умолчанию в нём видим имя активной ячейки (active cell), в которой находится табличный курсор. Поэтому активную ячейку часто называют «выделенной».

Имя ячейки состоит из пересечения её столбца и строки. Например, А1 – пересечение столбца А и строки 1.

Мы можем выделить любой объект (ячейку, группу ячеек, таблицу, график) и назначить (вбить с клавиатуры) в этом Поле имени объекта любое имя, например Table_01. Чтобы в дальнейшем осуществлять доступ к именованным объектам по их именам. На практике этой возможностью часто пользуются при работе со множеством массивов и в длинных формулах, чтобы их сократить. Задавать имена объектам без особой необходимости не рекомендуется.

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

9 – Строка формул. Показывает содержимое активной ячейки. В этой строке формул можно не только увидеть, но и отредактировать, то есть изменить содержащуюся в ячейке формулу вычисления или константу (текст или число). Если в активной ячейке содержится константа, а не формула, то в строке формул увидим ту же самую константу, как и в самой активной ячейке, разницы не будет. Но если в активной ячейке будет формула, то увидим разницу: если в активной ячейке будет отображаться результат вычисления по этой формуле, например «4», то в строке формул будет отображаться не результат вычислений, а текст формулы, например = 2+2, который можно будет рассмотреть и отредактировать в случае необходимости.

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

11 – Полосы прокрутки строк и столбцов

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

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

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

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

Выделение другой ячейки с помощью стрелок на клавиатуре: если хотим выделить ячейку справа от активной – жмём на клавишу клавиатуры со стрелкой, указывающую вправо. Например, если до нажатия на стрелку «Вправо» активной была ячейка А1, то после нажатия активной станет ячейка В1. Если затем нажмём на стрелку «Вниз» – то вместо В1 активной станет В2, нажмём после этого на стрелку «Влево» – активной станет ячейка А2. А если после этого нажмём на стрелку «Вверх» – то активной снова станет ячейка А1.

Заполнение файла Excel исходной информацией

Вернёмся к нашему заданию и начнём заполнять файл исходной информацией.

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

Разберём, как перенести эту информацию в наш файл «Сотрудники» на отдельный лист, который назовём «Информация от секретаря».

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

«Лист1» выделится, как на рисунке,

и в этот момент набираем с клавиатуры новое название листа «Информация от секретаря», после чего нажимаем «Ввод» (Enter).

…набираем с клавиатуры «Информация от секретаря».

Операция копирования и вставки массива данных

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

Если хотим одновременно скопировать несколько ячеек из таблицы, например с верхней левой А1 по нижнюю правую G26 (такой блок ячеек называют диапазоном А1:G26 или массивом А1:G26), то для выделения копируемого массива сначала встаём на угловую ячейку, например «А1».

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

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

Есть и другой способ выделения нужных ячеек: мы можем выделить для копирования не часть столбцов А:G, то есть не диапазон A1:G26, а сразу все строки нужных нам столбцов. Для этого наводим курсор на название столбца «А» выше ячейки А1, жмём левую кнопку мыши и не отпускаем её до тех пор, пока не дотянем курсор до столбца «G». Выделятся все ячейки всех строк в столбцах с А по G включительно (включая строки, расположенные ниже 26 строки).

После того, как копируемый фрагмент выделен, жмём на кнопку «Копировать» на вкладке Главная (кнопка выделена красным овалом на рисунке). Вместо кнопки «Копировать» можно использовать нажатие на клавиши «Ctr» и «C» на клавиатуре (сначала одним пальцем нажимаем на «Ctr» и, не снимая палец с неё, другим пальцем жмём на «C»).

Третий способ копирования выделенного диапазона (вместо Кнопки «Копировать» или использования клавиш «Ctr+C») – это навести курсор на выделенный диапазон, нажать правую кнопку мыши и в появившемся меню выбрать «Копировать».

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

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

Нажимаем на кнопку «Вставить» на вкладке «Главная».

На нашем листе «Информация от секретаря» вставятся из Буфера обмена скопированные данные. Вместо кнопки «Вставить», для вставки можем нажать на клавиши «Ctr» + «V» на клавиатуре. Также можем вызвать правой кнопкой мыши меню и выбрать в нём «Вставить».

В скопированных на лист «Информация от секретаря» данных не все слова читаются полностью, так как ширина столбца меньше ширины некоторых слов. Если мы захотим выровнять столбец А по ширине, то есть сделать ширину столбца А такой, чтобы было видно самое длинное слово в столбце А, то наводим курсор на правую границу столбца и жмём 2 раза с малым интервалом (дважды кликаем) на левую кнопку мыши. Куда именно кликать показано стрелкой на рисунке.

Если захотим аналогичным образом выровнять по ширине несколько столбцов, то сначала выделяем все эти столбцы с А по G (навести курсор на «А», нажать левой кнопкой мыши и, не отпуская, дотянуть курсор до «G»). Затем наводим курсор на границу любых двух столбцов (например, между А и B) и дважды кликаем левой кнопкой мыши. Столбцы «раздвинутся» до такой ширины, чтобы можно было прочитать самое длинное слово в них.

Для быстрого выделения сразу всех столбцов и строк всего Листа используют клик левой кнопкой мыши на верхнюю левую угловую область (левее столбца А и выше строки 1), выделена кругом на рисунке. Этот способ выделения всего листа используют не только при корректировке столбцов и строк по ширине, но и при быстром копировании содержимого всего Листа сразу.

Таким образом, мы скопировали, вставили и выровняли по ширине вставленный массив исходных данных.

Теперь скопируем данные из второго файла с исходными «Информация из Отдела кадров» на лист, который назовём «Информация от ОК». Так как пока у нас нет в файле второго листа, его надо создать. Сделаем это наведя курсор на название листа (аналогично тому, как мы делали при Переименовании листа), только теперь выберем в меню «Вставить» и далее выбрать «Лист».

В последних версиях Excel снизу есть встроенная кнопка добавления нового листа. Достаточно нажать на «+», как показано на рисунке, и появится новый лист.

Появился новый лист, который мы переименовываем в «Информация от ОК» (аналогично тому, как мы переименовывали первый Лист1).

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

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

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

Редактирование исходной информации в Excel. Сортировка, Удаление столбцов, Форматирование ячеек, Изменение высоты строк и Ширины столбцов, Выравнивание содержимого в ячейках

Приступаем к заполнению листа «Итог».

Сначала скопируем на лист «Итог» информацию с листа исходных от Секретаря. Выделяем и копируем столбцы А:E с названиями Отдел, Должность, Фамилия, Имя, Отчество. Скопировали.

Как это часто бывает на практике, сталкиваемся с небольшой проблемой: фамилии в файле, который нам был предоставлен от одного источника (от Секретаря) расположены в ином порядке. Строки отсортированы иначе, чем строки с фамилиями в файле, который мы получили из другого источника (отдела кадров).

Если бы строки с фамилиями на обоих исходных листах (и от Секретаря и из Отдела кадров) имели бы один и тот же порядок, то копирование было бы простым. Мы бы выделили нужные нам столбцы (с датами рождения и приёма на работу), скопировали бы и вставили бы их. Но в нашем случае так сделать нельзя, поскольку это приведёт к ошибке: в файле из Отдела кадров и на листе

«Информация от ОК» во второй строке фамилия «Борщёв»,

в то время как в файле от Секретаря и на листе «Итог» вторую строку занимает «Иванов».

Если мы сразу скопируем и вставим столбцы с листа «Информация от ОК» на лист «Итог», с данными как на листе «Информация от Секретаря», то вторая строка с Датой рождения и Датой приёма на работу Борщёва подставятся во вторую строку с Отделом, Должностью, Фамилией, Именем и Отчеством Иванова.

Чтобы избежать этой ошибки, выполним предварительную обработку данных: приведём строки на листе «Итог» в тот же алфавитный порядок, как они отсортированы на листе «Информация от ОК». Чтобы строка с фамилией «Борщёв» на листе «Итог» стала бы второй, сразу под шапкой, как и на листе «Информация от ОК». После этого сможем скопировать столбцы с листа «Информация от ОК» на лист Итог, и данные Борщёва скопируются в строку с Борщёвым, а не с Ивановым. Воспользуемся для этого Сортировкой.

Операция Сортировка:

– Выделяем область для сортировки (столбцы А:E либо ячейки A1:E26).

– На вкладке «Данные» жмём на кнопку «Сортировка».

– В появившемся окне выбираем «Сортировать по…» «Фамилия», выбираем алфавитный Порядок сортировки «От А до Я», и жмём «Ок».

Сортировка

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

После сделанной сортировки порядок строк с фамилиями на листе «Итог» совпадает с порядком строк на листе «Информация от ОК».

Примечание: сделать Сортировку (и многое другое) можно также с помощью меню, которое появляется при нажатии правой кнопки мыши. Использование правой кнопки мыши для вызова меню – очень полезный навык, ускоряющий работу в Excel.

Скопируем данные с листа «Информация от ОК» для переноса на лист «Итог». Выделим на листе «Информация от ОК» три столбца: «ФИО», «Дата приёма на работу» и «Дата рождения». Для задания нам столбец ФИО не нужен, но мы его используем для того, чтобы убедиться, что строки с фамилиями сотрудников после сортировки везде теперь совпадают.

После визуальной проверки того, что порядок строк верный (фамилия из столбца «F» с ФИО совпала с фамилией из столбца «С»), лишний столбец «F» с ФИО нужно удалить.

Удаление столбца:

Выделяем столбец F, наведя курсор мыши на заголовок F и нажимаем левую кнопку мыши. Затем жмём на правую кнопку мыши. В появившемся меню выбираем «Удалить».

Все исходные данные на лист «Итог» перенесены. Столбцы «Стаж полных лет на дату отчёта» и «Возраст полных лет на дату отчёта» создадим сами.

Для этого встаём на ячейку «H1» и с клавиатуры набираем «Стаж полных лет на дату отчёта». Затем встаём на ячейку «I1» и с клавиатуры набираем «Возраст полных лет на дату отчёта».

В таком виде таблица для печати не годится: верхняя строка таблицы с заголовками столбцов (её часто называют «Шапкой») имеет шрифт разной величины в разных столбцах и разную заливку.

Форматирование ячеек:

Приведём заголовки столбцов F:I в тот же вид (в тот же формат), как у столбцов А:E.

Для этого встаём на ячейку E1, жмём правую кнопку мыши и в появившемся окне меню выбираем «Копировать»

Затем выделяем ячейки F1:I1 и жмём правую кнопку мыши. В появившемся меню выбираем «Специальная вставка».

Затем, в появившемся меню «Специальной вставки» выбираем «форматы» и жмём «ОК».

Формат ячеек F1:I1 стал таким же, как и E1 (той же заливки, того же шрифта).

Изменение высоты строк и ширины столбцов.

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

Для этого выделяем строку, нажав курсором на «1» левее ячейки А1, затем жмём на правую кнопку мыши. Выбираем «Высота строки».

В появившемся окне меняем высоту с 15 на 75 и жмём ОК.

Заголовок стал выше в 5 раз, чем обычная строка:

Высота строки 1 с заголовком увеличилась

Примечание: увеличить или уменьшить Высоту строки также можно и с помощью курсора. Наводим курсор мыши на границу между строками (например, между строками 1 и 2), нажимаем левую кнопку мыши и, не отпуская нажатую левую кнопку, тянем курсор вниз или вверх. Граница строки 1 при этом сдвигается. Когда строка 1 станет нужной высоты – отпускаем левую кнопку мыши. Аналогично и с увеличением или уменьшением Ширины столбца: наводим курсор на границу между столбцами (например, между столбцами А и В), нажимаем левую кнопку мыши и, не отпуская нажатую кнопку, тянем курсор влево или вправо. Граница столбца А при этом сдвигается. Когда столбец А станет нужной ширины – отпускаем левую кнопку мыши.

Выравнивание содержимого в ячейках.

Чтобы выровнять текст внутри ячеек, наводим курсор мыши на выделенные ячейки и нажимаем правую кнопку мыши, вызывая меню. В появившемся меню выбираем «Формат ячеек…».

В появившемся окне Формата ячеек на вкладке «Выравнивание» сначала ставим галочку в квадратик слева от «Отображение – переносить текст». Теперь, если текст внутри ячейки длинный и выходит за границы ячейки, то он будет расположен внутри ячейки в несколько рядов. Та часть текста, которая не уместилась в границах ячейки, будет переноситься в следующий ряд ячейки, а не скрываться.

Теперь сделаем выравнивание текста по горизонтали. Наводим курсор мыши на значок, открывающий список вариантов Выравнивания по горизонтали и кликаем по нему левой кнопкой мыши. Откроется несколько вариантов, любой из которых можно выбрать вместо стоящего по умолчанию варианта «по значению».

Для заголовков таблицы выберем «Выравнивание – по горизонтали: по центру».

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

Содержимое строки «1» с заголовками таблицы по горизонтали выровняли по центру. Получилось так:

Аналогичным образом меняем Выравнивание по вертикали – вместо заданного по умолчанию «по нижнему краю» выбираем вариант «по центру».

Кнопки для команд выравнивания по вертикали также находятся на Вкладке Главная. С их помощью можно выровнять текст ячейки по вертикали без захода в меню.

Ячейки заголовков таблицы отформатированы и теперь можно её распечатать на стандартном листе.

Часть столбцов у нас пока не заполнена (Стаж полных лет на дату отчёта и Возраст полных лет на дату отчёта). Их надо вычислить, то есть произвести математический расчёт и получить результат.

Математические расчёты в Excel

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

Простые вычисления, такие как Сложение, Вычитание, Умножение, Деление, Возведение в степень делаются с помощью операторов (+, -, *, /, ^, &). Все формулы начинаются со знака равенства (=).

Покажем на примерах, как работает формула с операторами. Вобьём в пустую ячейку А2 число 3, а в пустую ячейку А3 число2.

– Чтобы сложить эти два числа, выделяем пустую ячейку E2, вводим =А2+А3, а затем нажимаем клавишу ВВОД. Мы получили результат 5. Такой же результат мы получим, если вместо ссылок на ячейки А2 и А3 используем в формуле константы 2 и 3. То есть, если мы введём в выделенную ячейку =2+3 и нажмём клавишу ВВОД, получим тот же результат 5.

– Чтобы вычесть из числа 3 число 2, выделяем пустую ячейку E3, вводим =А2-А3 (или вводим =3—2), а затем нажимаем клавишу ВВОД.

– Чтобы умножить число 3 на число 2, выделяем пустую ячейку E4, вводим =А2*А3 (или вводим =3*2), а затем нажимаем клавишу ВВОД.

– Чтобы поделить число 3 на число 2, выделяем пустую ячейку E5, вводим =А2/А3 (или вводим =3/2), а затем нажимаем клавишу ВВОД.

– Чтобы возвести число 3 в степень 2, выделяем пустую ячейку E6, вводим =А2^А3 (или вводим =3^2), а затем нажимаем клавишу ВВОД.

– Чтобы соединить число 3 с числом 2 (получив число 32), выделяем пустую ячейку E7, вводим =А2&А3 (или вводим =3&2), а затем нажимаем клавишу ВВОД.

Примечание: Оператор соединения & работает не только с числами, но и с текстом. Если к числу присоединяем текст, итоговое значения становится текстом, а не числом. Если вводим текст как константу (слово «Результат»), а не ссылкой на ячейку с текстом (на ячейку Е1), то текст нужно взять в двойные кавычки.

В колонках С и D мы видим формулы, по которым считаем, но не видим результата. Как этого добились? Это сделано с помощью установки пробела перед знаком =. Excel воспринимает содержимое как текст, а не как формулу, так как первый знак не =, а пробел (хотя мы его и не видим глазами). Если мы удалим первый знак пробела в ячейках столбцов С и D, то сработают формулы и получим в ячейках столбцов С и D тот же результат, что и в столбце E. Этот приём может быть полезен, когда в процессе создания таблицы хотим сохранить и показать этапы выведения формул.

Ссылки на ячейки и диапазоны ячеек.

Каждый Лист файла Excel состоит из ячеек. Каждая ячейка находится на пересечении одного из 16 384 столбцов (столбцы обозначены латинскими буквами) и одной из 1 048 576 строк(обозначены порядковым числом). То есть на каждом листе более шестнадцати миллиардов ячеек. Сделать ссылку на любую ячейку можно, указав название столбца (например А) и номер строки (например 2). А2 – это ссылка на ячейку, которая находится в первом столбце А и во второй строке. В нашем примере в ней находилось число 3. Если мы в формуле на одном листе ссылаемся на ячейку другого листа – то в ссылке перед ячейкой появляется название этого другого листа. Ссылаться можно не на одну, а сразу несколько ячеек, на диапазон ячеек. Например, ссылка на диапазон E2:Е4 ссылается сразу на три ячейки: E2, Е3 и Е4.

Константы.

Константы – это постоянные значения (в отличие от изменяющихся, от переменных), которые вводятся в ячейки и в формулы. Константы используют в формулах реже, чем ссылки на ячейки. Хотя формула =3+2 даст тот же результат, что и формула =А2+А3, но в случае изменения значений в ячейке А2 или ячейке А3 формула =А2+А3 сделает новое вычисление сразу, а формула с константой – нет. Нужно будет заходить в формулу с константой и менять 3 или 2 на новое значение. Гораздо удобнее вводить участвующие в формулах значения в отдельные ячейки, где их можно легко контролировать и изменять, а в формулах использовать только ссылки на них. Если встать на ячейку с формулой, например на Е2, то в строке формул (полоска над латинскими буквами столбцов) увидим содержимое формулы, которое сможем изменить при желании.

Примеры формул.

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

Разберём примеры формул:

= 3 +2

Числа 3 и 2 – это константы, сложение которых выполняет оператор +.

= А2 + А3

А2 и А3 – это ссылки на ячейки, сложение содержимого ячеек выполняет оператор +.

= 3 & «Результат»

Число 3 и текст «Результат» – это константы, соединение которых выполняет оператор &.

= А2 и Е1

А2 и Е1 – это ссылки на ячейки, соединение содержимого ячеек выполняет оператор &.

=СУММ (А2:А3).

Это формула с функцией.

После нажатия кнопки «Вставить функцию», в появившемся меню мастера функций выбираем нужную нам функцию.

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

СУММ – это функция Excel в составе формулы. Функция Excel – это встроенная команда, которая принимает вводимые значения (аргументы), определённым образом их обрабатывает и возвращает итоговый результат. Например, функция СУММ принимает в качестве аргументов числовые константы, ссылки на ячейки или диапазоны и суммирует их. В формуле =СУММ (А2:А3) диапазон состоит из начальной ячейки А2 и конечной ячейки А3, и двоеточия между ними. Ячейки содержат числа 3 и 2, итоговым результатом работы формулы = СУММ (А2:А3) будет 5. Такой же, как и у формулы = 3 +2 и у формулы = А2 + А3. У любой функции всегда есть открывающая скобка» (», закрывающая скобка»)», а аргументы отделяются между собой с помощью точки с запятой»;».

=СУММ (А2:А3;E2:Е4;Е7;2)

В данной формуле у функции СУММ не один аргумент, а четыре: ссылки на два диапазона А2:А3 (в этих ячейках в примере числа 3 и 2) и E2:E4 (в этих ячейках в примере числа 5, 1, 6), ссылка на ячейку E7 (в этой ячейке в примере число 32), и константа 2. Результатом работы этой формулы является число 51.

=СРЗНАЧ (А2:А3) – функция, возвращающая среднее значение, в нашем примере результат =2.5 (среднее между 3 и 2).

=МИН (А2:А3) — функция, возвращающая минимальное значение, в нашем примере результат =2 (минимум между 3 и 2).

= МАКС (А2:А3) — функция, возвращающая максимальное значение, в нашем примере результат =3 (максимум между 3 и 2).

= СЧЁТ (А2:А3) – функция, возвращающая количество чисел в диапазоне, не учитывая ячейки с текстом и пустотой. В нашем примере результат =2 (обе ячейки, и ячейка А2 и ячейка А3, содержат числа).

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

ВАЖНО: широко используются не все 400 с лишним функций Excel, а лишь функции СУММ, СРЗНАЧ, СЧЁТ, МИН, МАКС, СЕГОДНЯ, ДОЛЯГОДА, ОКРУГЛ, ОКРУГЛВНИЗ, ОКРУГЛВВЕРХ, ВПР, ЕСЛИ, ЕСЛИОШИБКА, СУММЕСЛИ, СУММЕСЛИМН, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ. ИТОГИ, ЛЕВСИМВ. Подавляющее большинство пользователей решают все свои рабочие задачи в Excel только с их помощью и не пользуются никакими другими функциями! Не тратьте время на изучение всех функций подряд! В случае необходимости, всегда можно воспользоваться Мастером функций и отыскать любую из возможных функций Excel.

Файлы с обучением всем основным функциям Excel вы найдёте на моём сайте matrixcheck.ru в файлах Excel, доступных для скачивания. В этих файлах сможете, меняя значения в ячейках исходных данных для примера, увидеть работу формул с этими функциями. Например, лист Excel для обучения работе с функцией СУММ выглядит следующим образом:

Обучение работе с функцией СУММ

Мастер функций

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

После нажатия кнопки «Вставить функцию», в появившемся меню мастера функций выбираем нужную нам функцию.

В Мастере функций можно найти любую функцию и её описание

Применение функций СЕГОДНЯ, ДОЛЯГОДА, ОКРУГЛВНИЗ для вычисления стажа и возраста

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

Чтобы рассчитать «Стаж полных лет на дату отчёта», нужно вычислить, сколько лет прошло до Текущей Даты с момента Приёма на работу, и полученное число преобразовать в количество целых лет.

Для вычисления Текущей Даты воспользуемся функцией СЕГОДНЯ, для вычисления прошедшего периода в годах воспользуемся функцией ДОЛЯГОДА, а для преобразования полученного значения из лет со знаками после запятой в целые года воспользуемся функцией ОКРУГЛВНИЗ.

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

Все три функции впишем в одну формулу и вобьём в ячейку H2. Формула из комплекса этих трёх функций будет выглядеть так: «=ОКРУГЛВНИЗ (ДОЛЯГОДА (СЕГОДНЯ ();F2);0)».

Если дата приёма на работу = 22.04.2020 (в ячейке F2), то при формировании отчёта в период с 23.04.2022 по 21.04.2023 формула в ячейке H2 покажет результат Стаж полных лет = 2. Когда наступит день после 21.04.2023, эта же формула выдаст результат больше 2-х.

Разберём подробно функции, с помощью которых мы получили этот результат.

Функция СЕГОДНЯ

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

Функция ДОЛЯГОДА.

С её помощью в примере вычислили, какой стаж: сколько лет прошло от Даты приёма на работу до Текущей Даты Сегодня.

Функция ОКРУГЛВНИЗ. С её помощью в примере отбросили от числа лет стажа знаки после запятой. В результате получили Стаж полных лет, как и требовалось в задании.

Обучение работе с функцией ОКРУГЛВНИЗ

Примечание: ОКРУГЛВВЕРХ и ОКРУГЛ – функции, схожие с функцией ОКРУГЛВВНИЗ, только ОКРУГЛВВЕРХ округляет число всегда вверх, а ОКРУГЛ – или вниз или вверх по обычному правилу округления (если следующее после разряда число меньше 5 – то вниз, иначе – вверх).

Теперь скопируем ячейку H2 в ячейки H3:H26.

Сначала выделяем ячейку H2. Затем копируем её в буфер обмена любым из описанных выше способов (Ctr+C, кнопкой со вкладки Главная или с помощью вызова меню правой кнопкой мыши), выделяем ячейки H3:H26 и вставляем в них содержимое ячейки H2 любым из описанных выше способов (Ctr+V, кнопкой со вкладки Главная или с помощью вызова меню правой кнопкой мыши).

Примечание: при копировании формул внутри одного столбца сверху вниз, часто пользуются таким приёмом: выделяют табличным курсором верхнюю ячейку с формулой, которую собираются скопировать в нижние ячейки столбца (у нас это ячейка Н2), наводят курсор мыши на правый нижний угол этой ячейки с формулой (у нас это ячейка Н2), появляется «+», и в этот момент дважды кликают левой кнопкой мыши. Содержимое всех ячеек ниже ячейки Н2 в столбце H заполняется содержимым, аналогичным ячейке Н2 (текстом или формулой) до последней ячейки столбца Н, слева или справа от которой в соседних с ней столбцах есть непустая ячейка (у нас это Н26).

Получили заполненный столбец Н с рассчитанным по формуле Стажем полных лет:

Чтобы рассчитать Возраст полных лет на дату отчёта, с помощью функции ДОЛЯГОДА определяем, сколько лет прошло с Момента Даты рождения (в ячейке G2) до Текущего момента Сегодня. А с помощью функции ОКРУГЛВНИЗ отбрасываем дробную часть.

Встаём на ячейку I2 и вбиваем такую формулу =ОКРУГЛВНИЗ (ДОЛЯГОДА (СЕГОДНЯ ();G2);0).

Cкопируем ячейку I2 в ячейки I2:I26. Результат тот же, как если бы мы скопировали I2 в ячейки I3:I26, от вставки содержимого I2 в ячейку I2 ничего не меняется.

Получили столбец с расчётом возраста

Печать итоговой таблицы Excel

Перед печатью, ещё раз посмотрим на Итоговую Таблицу и исправим все недочёты.

Например, заметили, что ячейки столбцов H и I не имеют границ, как у других ячеек таблицы.

Сделаем так, чтобы все ячейки столбцов H и I имели те же границы со всех сторон ячейки, что и в прочих столбцах. Для этого Выделим H2:I26 и на вкладке «Главная» нажмём на кнопку «Границы», выбрав затем в меню из списка вариант «Все границы». Ячейки H2:I26 теперь имеют такие же границы, как и прочие ячейки таблицы.

Обратили также внимание на то, что первая строка с названиями столбцов (шапка таблицы) становится не видна при прокрутке вниз.

Для того, чтобы шапка таблицы всегда была видна, а не исчезала бы при смещении курсора или при прокрутке, закрепим области. Для этого встанем табличным курсором в ячейку B2 и на вкладке «Вид» нажмём кнопку «Закрепить области». Области закрепятся по левому верхнему углу ячейки B2: строка 1 (всё что выше ячейки B2) и столбец А (всё что левее ячейки В2) станут видны при любом положении табличного курсора или при любой прокрутке вниз или вправо.

Теперь, при прокрутке вниз или при смещении курсора, всегда будет видна 1 строка и Столбец А.

Теперь, когда замеченные недочёты устранили, жмём на «Файл» и в выпавшем меню выбираем «Печать».

Обратили внимание, что если не отредактируем параметры перед печатью, то распечатаются две страницы, при этом на второй странице будет лишь один столбец. Это нас не устраивает.

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

Далее выбираем вариант нумерации страниц: жмём на «Параметры страницы» как показано на рисунке, выбираем вкладку «Колонтитулы», на ней выбираем вариант Колонтитула.

Всё готово. Теперь, если нажать ПЕЧАТЬ – наша Итоговая таблица или распечатается на принтере (если выбран подключённый принтер) или сохранится в виде PDF-файла (если выбран в качестве принтера «Microsoft Print to PDF», как на рисунке).

Здесь же мы можем изменять настройки печати в зависимости от того, сколько и чего собираемся печатать.

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

Если бы у нас было много страниц для печати, но при этом распечатать мы бы хотели не все, а лишь часть из них, то мы бы указали (вместо Пустоты по умолчанию) какие именно страницы хотим вывести на печать.

Если бы у нас было меньше столбцов и больше строк, то для уменьшения количества распечатываемых листов мы бы выбрали не «Альбомную» (которая стоит по умолчанию), а «Книжную» ориентацию страницы.

Сохраним итоговую таблицу в нашей папке «Задание» как PDF-файл, который назовём ПЕЧАТЬ_СОТРУДНИКИ.

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

Сначала наводим курсор мыши на название листа Итог и жмём правую кнопку, вызывая меню. В появившемся окне меню выбираем «Переместить или скопировать». Если бы перед нами стояла задача не сделать копию нужного листа, а удалить ненужный лист, то вместо «Переместить или скопировать» мы бы выбрали в окне меню «Удалить».

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

В новом появившемся листе «Итог (2)» выделим все ячейки и поменяем размер шрифта на 17. В предложенном меню 17 размера нет (только 16 или 18), поэтому вобьем «17» с клавиатуры и нажмём ввод.

Увеличение шрифта привело к тому, что часть информации стала не пригодной для печати. Если не отредактируем, то на печать отправится такая вот таблица:

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

Сохраняем таблицу с 17 шрифтом как файл PDF в ту же папку «Задание», назовём её ПЕЧАТЬ_СОТРУДННИКИ_2. Задание выполнено. Таблица подготовлена к печати в двух файлах: со стандартным 12 шрифтом и с увеличенным 17. Файлы можно отправить на печать на принтер, послать по почте или передать на флэшке.

Закрепление заголовка таблицы при печати.

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

На вкладке Разметка страницы нажмём на кнопку Печать Заголовки. В открывшемся меню вбиваем Сквозные строки $1:$1 (или выделим первую строку с заголовком с помощью мыши). Теперь шапка таблицы будет печататься в каждом новом листе, если строк в таблице будет много и вся таблица не войдёт на один лист печати.

Печать сквозных строк (на каждой странице будет видна 1 строка)

Какие ещё приёмы редактирования таблиц наиболее часто используют на практике

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

Вставка дополнительных строк, столбцов

Выделяем строку 1, сверху которой хотим получить новую строку. Жмём правую кнопку мыши, а в появившемся меню нажимаем на «Вставить». Все строки таблицы смещаются вниз, а 1-й строкой становится новая пустая строка.

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

Вставка Примечания

Чтобы сохранить пояснение к ячейке, вставляют Примечание. Ставим табличный курсор в ячейку, к которой хотим сделать примечание (например, в D4), жмём правую кнопку мыши и в появившемся меню выбираем «Вставить примечание». Вбиваем то примечание, которое хотим вставить, например «Сотрудница в декретном отпуске». По умолчанию примечание будет отображаться только тогда, когда курсор наведён на ячейку с примечанием.

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

Можем делать сколько угодно примечаний на одном листе. Например, встанем на ячейку B4 и сделаем ещё одно примечание «Отдел временно не работает».

Форматирование: объединение ячеек с помощью кнопок на вкладке Главная

В новой пустой строке 1 над данными столбцов, начиная со столбца «Отдел» и заканчивая «Дата рождения» создадим объединённую ячейку «ИСХОДНЫЕ ДАННЫЕ», а над столбцами Стаж и Возраст, создадим объединённую ячейку «РАСЧЁТ НА ДАТУ ОТЧЁТА».

Сначала выделяем область, которую хотим объединить (ячейки B1:H1), затем нажимаем на кнопку объединения ячеек на вкладке «Главная», как показано на рисунке. В объединённую ячейку B1 вбиваем с клавиатуры текст «ИСХОДНЫЕ ДАННЫЕ».

Содержимое объединённых ячеек, как и содержимое обычных ячеек, можно выровнять по Вертикали и Горизонтали. По вертикали можно выровнять по «Верхнему краю», «Середине» или «Нижнему краю». По Горизонтали содержимое ячеек можно выровнять по «Левому краю», «Центру» или «Правому краю».

Форматирование: изменение шрифтов и заливок с помощью кнопок на вкладке Главная

Выделим ячейку B1 зелёной заливкой, шрифт изменим на Arial и цвет шрифта сделаем красным и жирным. А ячейку I1 выделим жёлтой заливкой, шрифт изменим на Arial Narrow, выберем подчёркнутый курсив.

Для примера сделаем поочерёдно строки 3, 4, 5, 6 разных шрифтов, цветов заливок, цветов шрифта. Вот как это будет выглядеть:

Чтобы вернуть всё обратно, выделим все эти строки и выберем изначальный вариант шрифта Calibri, без заливки, шрифт чёрный и нажмём «Ввод». Либо скопируем строку 7 (с форматом ячеек таким, каким он был и в ячейках 3—6 до преобразований), выделим строки 3—6, жмём на правую кнопку мыши и выбираем Специальная вставка и далее Форматы, затем жмём ОК. Все ячейки Строк 3—6 станут того же формата, что и ячейки строки 7.

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

Группировка и скрытие столбцов и строк

Иногда количество столбцов в таблице или строк в таблице такое большое, что её неудобно просматривать. Чтобы применить группировку (схлопывание) столбцов, выделяем столбцы, которые хотим сгруппировать, а на вкладке «Данные» нажимаем на кнопку «Структура» и выбираем «Группировать».

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

Выделенные столбцы сгруппировались и свернулись. Увидеть вновь скрытые сгруппированные столбцы мы сможем при нажатии на «+» или на «2» над названиями столбцов.

А чтобы вновь сгруппировать или свернуть столбцы после просмотра, нужно нажать на «1» или в область над столбцами, как показано на рисунке.

Аналогичным образом группируют и строки.

Скрытие столбцов и строк

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

Раскрытие скрытых строк

Фильтр

Чтобы в таблице видеть не все строки, а лишь соответствующие определённому признаку, используют Фильтр. Встаём на ячейку заголовка столбца, например на Отдел, и либо нажимаем на кнопку «Фильтр» на вкладке «Данные», либо правой кнопкой мыши вызываем меню и выбираем «Фильтр».

У ячеек заголовка в правом нижнем углу появился фильтр. Чтобы наша таблица показывала только сотрудников отделов Закупок и Администрации, оставляем галки только у этих отделов, а у других – снимаем. Фильтр применился, и все прочие строки оказались скрыты. Аналогичным образом можем отфильтровать по любому другому столбцу таблицы: Имени, Стажу и другим.

Применение фильтра

Форматирование: Изменение знаков после запятой с помощью кнопок на вкладке Главная

Если мы захотим, чтобы возраст в нашей таблице был бы не только «полных лет», но и точный, со знаками после запятой, добавим столбец «Возраст лет точный на дату отчёта». Ячейку I1 объединим с К1. Уберём функцию ОКРУГЛВНИЗ из формулы вычисления возраста полных лет для 3-й строки =ОКРУГЛВНИЗ (ДОЛЯГОДА (СЕГОДНЯ ();H3);0). Эту формулу =ДОЛЯГОДА (СЕГОДНЯ ();H3) внесём в ячейку К3. Далее скопируем её и вставим во все строки столбца K.

Изначально после запятой видим разное количество знаков. Чтобы отображалось одинаковое количество знаков после запятой, например 2, воспользуемся кнопкой Сокращения знаков на вкладке Главная.

Кнопка Сокращения знаков после запятой на вкладке Главная

Форматирование с помощью правой кнопки мыши. Формат ячеек. Вкладки Число, Выравнивание, Шрифт, Граница, Заливка

Формат ячеек. Вкладка Число.

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

Вызовем правой кнопкой мыши меню и выберем «Формат ячеек», вкладку «Число», числовые форматы: «Числовой»; Число десятичных знаков = 2.

Формат ячеек. Число. Числовой. Число десятичных знаков 2.

Примечание: если мы выберем число десятичных знаков 2 для числа 27,84166667, то число начнёт лишь отображаться как 27,84, но его значение останется прежним 27,84166667. И когда это число будет участвовать в формуле в других ячейках, например при суммировании значений по столбцу, то участвовать в сумме будет именно 27,84166667, а не 27,84. Это часто приводит к тому, что Сумма Итого не совпадает с суммой отображаемых значений на величину накопившейся разницы между отображаемым значением и фактическим.

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

ВАЖНО: вместо использования кнопок на вкладке «Главная», ЛЮБОЕ форматирование ячеек можно сделать, вызвав правой кнопкой мыши меню и выбрав «Формат ячеек», а затем выбрав нужный вариант в открывшихся вкладках.

Формат ячеек. Выравнивание.

На вкладке «Выравнивание» можем не только выравнивать содержимое ячеек по Горизонтали и Вертикали, но и добавлять Отступ, делать Объединение ячеек, Автоподбор ширины, Переносить текст и менять его Ориентацию.

Вот так выглядит в таблице Ориентация: Вертикальный текст – Продаж, Надпись 45 градусов – менеджер, Надпись 90 градусов – Борщёв:

Формат ячеек. Шрифт.

На вкладке «Шрифт» можем выбрать любой Шрифт из доступных и его размер, выбрать начертание (обычный, курсив, полужирный, полужирный курсив), сделать видоизменение (зачёркнутый, надстрочный или подстрочный) и выбрать любой цвет Шрифта.

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

Формат ячеек. Граница.

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

Вот так могут выглядеть границы разных типов, толщины, цвета линий в таблице:

Формат ячеек. Заливка.

На вкладке «Заливка» можем выбрать цвет, способ заливки и узор заливки. Нажимаем на «Другие цвета» и в появившемся меню Цвета выбираем нужный нам цвет заливки, нажимаем на Узор и Цвет Узора выбираем тип и цвет узора. Нажимаем на Способы заливки и выбираем из вариантов типов заливки.

Вот лишь несколько вариантов из всевозможных Цветов заливки, Узоров, Способов заливки, которые могут быть использованы.

Несколько вариантов заливки ячеек.

Операция Условное форматирование

Условное форматирование полезно при работе с таблицами, когда хотят визуально отсортировать данные, выделить в таблице заливкой или шрифтом не все ячейки, а только ячейки по заданному критерию. Обычно выделяют числа больше, меньше или между некими границами. Для примера возьмём готовую таблицу с данными по продажам Овощного Ларька «Дары Кубани» 03—05 января и выделим жёлтой заливкой и красным полужирным курсивом ячейки с продажами более 20кг.

Сначала выделяем ячейки, в которых будем выявлять и форматировать ячейки со значением больше 20кг. Это ячейки С4:С12. После выделения С4:С12 на Вкладке «Главная» нажимаем на кнопку «Условное форматирование» и в выпавшем меню выбираем Правило выделения ячеек «Больше».

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

После нажатия на «Пользовательский формат» попадаем в меню Формата ячеек, в котором на Вкладке Шрифт выберем Полужирный курсив и Цвет Красный.

На Вкладке «Заливка» выберем Жёлтый цвет заливки. При желании можно выбрать Узор и Цвет узора для выделяемых ячеек, а также способы заливки.

Нажимаем на ОК и видим, что все числа со значением больше 20 в диапазоне С4:С12 имеют красный полужирный курсив, а ячейки с ними выделены жёлтой заливкой.

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

Тот же результат можем получить, создав правило форматирования.

Аналитическая таблица в Excel

Извлечение данных из разных источников в одну таблицу: Относительные, Абсолютные и Смешанные Ссылки; Функция ВПР или Связка функций ИНДЕКС и ПОИСКПОЗ

На практике часто стоит задача соединить в одной аналитической таблице данные, которые содержатся в разных таблицах. При этом нужно, чтобы при изменении этих данных в разных таблицах-источниках они бы моментально изменялись и в итоговой таблице. Добиться этого можно с помощью ссылок на другие ячейки, а также с помощью функции ВПР или связки функций ИНДЕКС и ПОИСКПОЗ. ВПР проще, используется на практике чаще, однако ВПР имеет ограничение, которого нет в связке ИНДЕКС и ПОИСКПОЗ.

Ссылки на конкретные ячейки покажут нам содержимое этих конкретных ячеек. Мы можем делать ссылки на ячейки из других листов внутри одного файла, на ячейки других открытых файлов (когда мы одновременно открыли несколько файлов Excel), и даже на ячейки закрытых файлов.

Если мы хотим, например, чтобы в ячейке A4 на Листе3 было то же самое число, которое содержится в ячейке А4 в этом же файле на Листе2, достаточно в ячейке А4 на Листе3 вбить такую ссылку: =`Лист2`! A4. Название адреса листа в ссылке помещается между двумя апострофами (``) и заканчивается восклицательным знаком (!), затем следует адрес ячейки. Не обязательно делать это с клавиатуры: можно вбить знак равно (=), затем перейти на Лист2, навести курсор мыши и кликнуть левой кнопкой в ячейку А4, в завершение нажать Ввод. Ссылка =`Лист2`! A4 появится сама.

Если мы хотим сделать ссылку на ячейку из другого файла, например на ячейку H2 листа Итог в открытом файле «Сотрудники», то в ссылку перед названием листа нужно вставить название файла с расширением Excel (.xlsx) в квадратных скобках ([]): =` [Сотрудники. xlsx] Итог`! H2. Не обязательно делать это с клавиатуры: можно вбить знак равно (=), затем перейти в файл Сотрудники на лист Итог, навести курсор мыши и кликнуть левой кнопкой в ячейку Н2, в завершение нажать Ввод. Ссылка в виде =` [Сотрудники. xlsx] Итог`! H2 появится сама.

Если файл Сотрудники теперь закрыть, то ссылка на ячейку H2 листа Итог закрытого файла Сотрудники станет выглядеть так =`C:\Users\Алексей\Desktop\Задание\ [Сотрудники. xlsx] Итог`! H2. В ссылке перед названием файла появился полный путь к папке Задание, в которой этот файл- источник Сотрудники находится.

Примечание: при использовании ссылок на закрытые файлы возможен риск неожиданного удаления этих файлов-источников, изменения их наименования или перемещения в другие папки. Если такое произойдёт, то значение в ячейке с такой ссылкой не сможет обновиться. Если ячейка, на которую была сделана ссылка, будет удалена вместе с листом, строкой или столбцом, то появится сообщение об ошибке =#ССЫЛКА!.

Ссылки могут быть относительными, абсолютными и смешанными. Относительная ссылка – это обычная ссылка без знака $. Если при копировании ячейки хотят закрепить ссылку на столбец или строку, то ставят знак доллара ($) перед названием столбца или строки. Разница между относительной ссылкой =`Лист2`! A4 и абсолютной ссылкой =`Лист2`! $A$4 в том, что если мы на Листе 3 скопируем ячейку А4 с относительной ссылкой =`Лист2`! A4 в ячейку А5, то увидим в ячейке А5 ссылку с относительным изменением по строке =`Лист2`! A5. Если скопируем её в ячейку B4, то увидим в ней ссылку с относительным изменением по столбцу =`Лист2`! B4. А если скопируем ячейку А4 с абсолютной ссылкой =`Лист2`! $A$4 в любую другую ячейку, то во всех ячейках увидим одну и ту же ссылку без изменений =`Лист2`! $A$4. В смешанных ссылках закреплён только столбец или только строка, они при копировании ячейки с ссылкой неизменны по закреплённой части и изменяются по незакреплённой своей части.

Функция ВПР.

ВПР это функция вертикального просмотра. Функция ВПР ищет Искомое Значение сверху вниз в крайнем левом столбце указанного диапазона Таблицы. Находит его, после чего в строке с найденным Искомым значением смещается вправо в указанный столбец и возвращает значение ячейки. Той ячейки, которая находится в указанном столбце строки с найденным Искомым Значением.

Разберём на примере, как отработает функция =ВПР (E9;D7:E16;2;0). Она будет искать значение из ячейки E9 («огурцы») в крайнем левом столбце диапазона D7:E16 (в столбце D, то есть в ячейках D7:D16). Обнаружив «огурцы» в ячейке D14, функция возвратит результат из ячейки в той же 14 строке, но не из первого столбца «D», а из второго столбца «E», поскольку мы указали номер столбца «2». Результатом работы функции станет извлечённое из ячейки E14 значение «75».

Разберём применение Ссылок и ВПР для переноса данных на практическом примере.

Овощной ларёк «Дары Кубани» торгует яблоками, огурцами и помидорами. Продавцы ларька продают их каждый день в разном количестве и по разной розничной цене, ориентируясь на цены в соседних ларьках. Количество проданного товара и цену продажи продавцы ларька ежедневно заносят построчно в таблицу Продажи, к которой у Хозяина ларька есть доступ. Хозяин ларька не хочет, чтобы продавцы ларька знали реальную цену закупки яблок, огурцов и помидоров и условия работы с поставщиком, поэтому хранит эти данные отдельно, в другой своей таблице Условия Закупки.

Хозяину ларька интересно, какова Маржа и наценка продаж. Маржа – это разница между Выручкой от продаж товара и Затратами на закупку товара. Это деньги, из которых хозяин выплатит зарплату, арендные платежи, налоги и получит в итоге Чистую Прибыль от торговли, которой сможет законно распоряжаться по собственному усмотрению. Наценка – это отношение маржи к затратам на закупку, сколько % прибавляется к закупочной цене при продаже. Чтобы выполнить расчёт Маржи и наценки, надо использовать данные от продавцов из Таблицы Продажи и данные о цене закупки из таблицы Условия Закупки.

Для этого в своём файле Excel Хозяин ларька создаёт три листа: Первый – «1.Условия Закупки» может выглядеть так:

Второй лист «2.Продажи» с данными от продавцов (мы его уже видели, когда изучали операцию Условное форматирование), а третий лист «3.Расчёт Маржи и Наценки».

На этот итоговый Лист «3.Расчёт Маржи и Наценки» в колонки А:D с помощью относительных ссылок переносим данные с листа «2.Продажи» из соответствующих столбцов (Дата Продажи, Товар, Продано в кг и Цена Продажи в розницу за кг).

Например в ячейке A4 ставим ссылку: =`2.Продажи `! A4, и в ячейку А4 на лист 3 переносится значение ячейки А4 с листа «2.Продажи». При копировании ячейки А4 в диапазон ячеек А4:D12 эта относительная ссылка изменяется и переносит значения из соответствующих ячеек А4:D12 с листа «2.Продажи».

В ячейке Е4 стоит формула с функцией ВПР: =ВПР (B4; ` 1.Условия Закупки `! A:B;2;0). Эта формула отыскивает значение из ячейки B4 (яблоки) в колонке А листа «1.Условия Закупки», находит его в 4- й строке, и переносит из второй колонки (В) этой четвёртой строки значение «70».

Колонки F, G, H, I содержат формулы. F4=D4*C4 (Выручка = Продано в кг*Цена продажи в розницу за кг); G4=C4*E4 (Затраты на закупку у поставщика = Продано в кг*Цена Закупки за кг); H4=F4-G4 (Маржа = Выручка – Затраты на закупку у поставщика); I4=H4/G4 (Наценка в % = Маржа/Затраты на закупку). Формат ячеек колонки I – процентный. Расчётная аналитическая таблица маржи и наценки выглядит так:

Расчётная аналитическая таблица Маржи и Наценки

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

Посмотрим теперь, как вместо ВПР можно использовать связку функций ИНДЕКС и ПОИСКПОЗ. Сначала познакомимся с ними.

В нашем примере, в ячейку Е4 вместо формулы =ВПР (B4;`1.Условия Закупки `! A:B;2;0) внесём =ИНДЕКС (`1.Условия Закупки `! B:B;ПОИСКПОЗ (B4;`1.Условия Закупки `! A:A;0);1), и убедимся, что результат тот же: «70».

C помощью функции ПОИСКПОЗ сначала отыскали строку, в которой на листе «1.Условия закупки» содержится Искомое_значение из ячейки В4 (Яблоки). Ею оказалась строка 4. Зная, что Цена всегда содержится в столбце В, с помощью ИНДЕКС извлекли значение из пересечения найденной строки 4 и известного столбца В. При этом сам столбец с извлекаемой ценой мог бы находиться как справа, так и слева от столбца, содержащего Искомое_значение. Таким образом, связка функций ИНДЕКС и ПОИСКПОЗ имеет больше возможностей, чем ВПР.

Использовать связку ИНДЕКС и ПОИСКПОЗ многим кажется труднее, чем ВПР. Но этот способ гораздо удобнее, когда необходимо получить значения из исходной таблицы, в которой столбец с требуемым значением находится слева от столбца с искомым значением.

Как сгруппировать данные по условию: Фильтр и Функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ; СУММПРОИЗВ; ЕСЛИ; СУММ; СУММЕСЛИ; СУММЕСЛИМН

На практике часто стоит задача показать итоги таблицы в сгруппированном виде. Такие задачи обычно выполняются с помощью фильтра и нескольких функций, таких как ПРОМЕЖУТОЧНЫЕ ИТОГИ, СУММПРОИЗВ, ЕСЛИ, СУММ, СУММЕСЛИ, СУММЕСЛИМН. Кроме того, широко используют Сводные таблицы, о которых поговорим отдельно. Макросы и запросы Power Query, которые также можно использовать для группировки данных, в этом курсе рассматривать не будем.

Покажем, как обычно решают задачу группировки данных.

Хозяин Ларька Дары Кубани хочет видеть, сколько итого яблок, огурцов и помидоров продано в килограммах.

Фильтр и функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

Установим фильтр в шапке таблицы (в третей строке в примере) и отберём фильтром в колонке B «яблоки». Чтобы посчитать, сколько итого яблок продано, воспользуемся функцией ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

В ячейку С2 вобьём функцию =ПРОМЕЖУТОЧНЫЕ. ИТОГИ (9;C4:C35). Вбивая номер функции 9, мы сообщаем, что нам нужны промежуточные итоги именно по Сумме. Диапазон часто берут с запасом (не по 12-ю заполненную, а по 35-ю пока пустую строку в примере), чтобы в случае добавления нескольких строк в таблице не переписывать каждый раз формулу.

Видим, что функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ просуммировала Яблоки. Когда вместо Яблок отберём фильтром «Помидоры», то та же функция в ячейке С2 покажет Сумму по помидорам, а когда отберём фильтром по «Огурцам» – то сумму по огурцам.

Скопировав ячейку С2 в ячейки F2, G2 и H2 получим формулу вычисления промежуточных итогов и по Выручке, Затратам на закупку и Марже.

Но такой способ суммирования с помощью Фильтра и функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ не всегда удобен, так как нужно постоянно переключать отбор в фильтре.

Чтобы всегда видеть ИТОГИ не применяя фильтр, сделаем над таблицей Таблицу с итогами, добавив пять строк сверху шапки таблицы (по одной строке для Шапки таблицы, Яблок, Огурцов, Помидоров и Итого. В ячейку С6 строки ИТОГО запишем формулу =СУММ (C3:C5), и скопируем ячейку С6 в ячейки F6, G6 и H6. А при записи формулы в ячейку С3 воспользуемся функцией СУММЕСЛИ.

СУММЕСЛИ и СУММЕСЛИМН – позволяют обходиться одной формулой вместо нескольких действий при сложных вычислениях. Например, они с успехом заменяют необходимость сортировки и промежуточных вычислений при АВС анализе, позволяя получать значение накопительной суммы критерия и сравнивать его с пороговым уровнем без необходимости делать сортировку. Но АВС анализ часто используют только маркетологи и аналитики, поэтому кейсы с такими специальными приёмами использования основных функций предлагаю найти на моём сайте matrixcheck.ru. А в рамках этого Курса рассмотрим полезный приём, который широко используется всеми: как с помощью всего одной формулы CУММЕСЛИ заполнить множество ячеек в разных строках и столбцах Итоговой таблицы.

В ячейку С3 запишем формулу с СУММЕСЛИ, сделав её диапазоны с запасом строк на случай добавления новых строк с новыми днями продаж =СУММЕСЛИ ($B$9:$B$500;$B3;C$9:C$500). Скопируем ячейку С3 и вставим её в С4 и С5. Поскольку в ссылке на критерий $В3 – закреплён столбец и не закреплена строка, то при копировании формулы в ячейки С4 и С5, вместо $В3 скопируются $В4 и $В5. Строки Диапазонов закреплены знаком доллара $ и не изменятся.

Обратите внимание на важный момент, за который очень любят работать с Excel: написав всего одну формулу в ячейке С3 =СУММЕСЛИ ($B$9:$B$500;$B3;C$9:C$500), можем теперь её скопировать и вставить не только в ячейки С4 и С5, но и в ячейки F3, F4, F5, G3, G4, G5, Н3, H4, Н5 за несколько секунд простым действием: выделяем диапазон F3:H5 и вставляем. Благодаря смешанной ссылке, в каждой из ячеек окажется верная формула: строки Диапазна суммирования везде останутся неизменными c 9 по 500, так как они закреплены знаком доллара $, а столбцы Диапазона суммирования не закреплены знаком $ и поэтому поменяются с С на F, G или H. Подобные приёмы использования смешанных ссылок в формуле часто применяют на практике.

Итак, с помощью формулы с функцией СУММЕСЛИ мы теперь будем видеть итоги по каждой из групп товара без помощи фильтра и функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

Средняя Цена продажи = Выручка / Продано в кг, то есть для её расчёта вобьём в ячейку D3 формулу =F3/C3 и скопируем ячейку D3 в ячейки D4 и D5 и D6. Ячейки I3, I4, I5 и I6 заполним, скопировав ячейку I9 и вставив её формулу в I3:I6. В ячейки E3:E5 скопируем формулу из ячейки E9.

Аналитическая Таблица со сводными данными готова.

На практике очень часто используют функцию ЕСЛИ. С её помощью обычно задаётся логика отбора для выполнения другой функции или вводится новый критерий.

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

После 5-го января, 6-го января была сделана новая запись продаж на листе «2.Продажи»:

На листе «3.Расчёт Маржи и Наценки» добавим три новых строки для 6го января: скопируем строку с формулами, например 12-ю или 17-ю, или любую другую, и вставим её в строки 18, 19 и 20. Вся новая информация с листа «2.Продажи» перенесётся по ссылкам в столбцы А:D, а в других столбцах таблицы произойдёт расчёт по формулам. Так как накопившаяся за все дни сумма продаж яблок в килограммах теперь превышает тонну, то и цена закупки по условиям договора должна измениться с 70 на 65 рублей во всех строках. Вариантов сделать это в Excel множество, выберем для примера не самый короткий по записи, зато наглядный:

В ячейку Е9 вместо формулы, которая в ней была до этого момента и выдавала результат 70, запишем новую формулу. Зададим с помощью ЕСЛИ логику отбора: когда суммарные продажи данного вида товара превысят 1000, то будет браться не вторая колонка Цены Закупки из таблицы с листа «1.Условия Закупки», а третья колонка.

=ЕСЛИ (СУММЕСЛИ ($B$9:$B$500;B9;C$9:C$500)> 1000;ВПР (B9;`1.Условия Закупки `! $A$3:$C$6;3;0); ВПР (B9;`1.Условия Закупки `! $A$3:$C$6;2;0))

С помощью СУММЕСЛИ мы сначала просуммировали все килограммы из колонки С в тех строках, где в колонке B то же самое значение что и в ячейке В9, затем сравнили эту сумму с 1000, и если сумма оказалась больше 1000, то взяли цену из 3-й колонки Таблицы с Условиями закупки. В противном случае – взяли цену из 2-й колонки.

Скопируем эту формулу в ячейки E10:E20. Те товары, продажи которых превысят 1000 кг, теперь станут автоматически учитываться с более низкой ценой закупки, и это благодаря использованию в формуле ЕСЛИ. В ячейки Е3:E5 вставлять новую формулу пока не будем, чтобы подчеркнуть разницу.

Рассмотрим варианты ввода нового критерия c помощью ЕСЛИ.

Например, хотим разделить наш товар на новые группы «Фрукты» и «Овощи».

Создадим новый столбец J для нового критерия «Новая группа» и вобьём в ячейку J9 формулу =ЕСЛИ (B9=«Яблоки»; «Фрукты»; «Овощи»). Скопируем эту формулу в ячейки J10:J20.

Использование функции ЕСЛИ: Новая группа Фрукты и Овощи.

Как создать отбор по сложному набору условий

Когда хотят отобрать из большого массива только строки, отвечающие сложному набору условий, который в дальнейшем может меняться, обычно в таблице создают отдельный столбец с новым критерием «отбор по условию», в котором с помощью ЕСЛИ присваивают «1» тем строкам, которые удовлетворяют набору условий и «0» – прочим. Вместо чисел 1 или 0 в качестве критерия можно вставить любой текст, например слово «отбор», «да», «нет», но с 1 и 0 в дальнейшем удобнее выполнять математические действия умножения, например, используя функцию СУММПРОИЗВ.

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

Итак, Хозяин Ларька решил, что овощами ему не выгодно торговать с наценкой меньше 50%, если при этом выручка от продажи определённого вида овощей меньше 3000 рублей в день. И Хозяин Ларька хочет знать, на какую сумму было закуплено овощей с невыгодными продажами. Создадим столбец К «Невыгодная продажа» и в ячейку К9 запишем формулу обнаружения строк, которые соответствуют всем условиям «невыгодных продаж»:

=ЕСЛИ (J9=«Овощи»; ЕСЛИ (F9 <3000;ЕСЛИ (I9 <50%;1;0);0);0).

За счёт вложения нескольких функций ЕСЛИ друг в друга, проверяем сразу несколько условий: и что Группа = Овощи (иначе 0), и что продано этих овощей меньше 3000 руб. в день (иначе 0), и что продажи были с наценкой менее 50% (иначе 0). И только для Овощей с продажами менее 3000 руб в день и наценкой при этом менее 50% результатом формулы является «1» (это признак «Невыгодной продажи», как его определил Хозяин Ларька).

Используем для расчёта функцию СУММПРОИЗВ.

В любую ячейку, где хотим видеть ответ на вопрос «на какую сумму было закуплено овощей с „невыгодными“ продажами», например в ячейку К7, внесём формулу =СУММПРОИЗВ (K9:K20;E9:E20;C9:C20)

Мы могли бы в нашем примере использовать и формулу =СУММЕСЛИ (K9:K20;"=1»; G9:G20), так как у нас уже есть отдельный столбец G c рассчитанными затратами на закупку у поставщика. И она даст тот же результат.

Но формула с функцией СУММПРОИЗВ сработает даже в том случае, когда у нас не будет отдельного столбца с уже рассчитанными затратами.

Исправление ошибок в исходных данных. Функции ЕСЛИОШИБКА, ЛЕВСИМВ, Операция Поиск и Замена

Функции ЕСЛИОШИБКА и ЛЕВСИМВ очень полезны при работе с исходными данными в смешанных массивах с числовыми данными, пустотой, текстом с ошибками (например, когда в исходном массиве встречаем и яблоки, и яблок, и яблоко, а использовать в таблице должны только одно объединяющее слово «яблоки»).

Покажем их работу на примере.

7 января наняли нового продавца, который внёс информацию с ошибками:

В результате этого, получили на листе с расчётом ошибки формул. Функции не смогли найти такой товар чтобы подставить Цену и поэтому отображают ошибку «#Н/Д». Либо не могут перемножить число на текст «ноль» или текст «нет», и поэтому отображают ошибку «#ЗНАЧ!».

Как это исправить, если повлиять на источник данных невозможно? Сделаем корректирующие поправки в формулы с помощью ЕСЛИОШИБКА и ЛЕВСИМВ.

В ячейку B21 запишем формулу, редактирующую ошибочные исходные данные с помощью функций ЕСЛИ и ЛЕВСИМВ. Если исходная информации о товаре начинается с «ябл», то отображаться в расчётной таблице будут «Яблоки», независимо от дальнейшего текста, если «пом» – то «Помидоры» и «Огурцы» в прочих случаях. =ЕСЛИ (ЛЕВСИМВ (`2.Продажи `! B16;3) =«ябл»; «Яблоки»; ЕСЛИ (ЛЕВСИМВ (`2.Продажи `! B16;3) =«пом»; «Помидоры»; «Огурцы»))

Скопируем ячейку B21 в ячейки В22:В23.

Теперь товар стал отображаться корректно, несмотря на некорректные исходные, и по корректному названию товара стала корректно подставляться Цена закупки и определяться Новая Группа товара «Фрукты».

Чтобы преобразовать любой текст в число 0, удобно воспользоваться функцией ЕСЛИОШИБКА. Внесём в ячейку С21 формулу =ЕСЛИОШИБКА (`2.Продажи `! C16*1;0).

Перемножая текстовое содержимое ячеек С16:D17 c листа «2.Продажи» на единицу, мы вынуждаем функцию при встрече с текстом в исходных данных зафиксировать ошибку (так как перемножать текст на числа нельзя, у такого действия нет корректного результата). Значение при зафиксированной ошибке = 0. Так как 0 это уже не текст, а число, то с ним можно выполнять дальнейшие арифметические действия. А если нам встречается не текст, а число, то умножение на 1 ничего в этом числе не меняет. Таким образом, с помощью ЕСЛИОШИБКА мы сохраняем все числа, а тест заменяем на число 0.

Скопируем ячейку С21 и вставим в ячейки С21:D23

Ошибки из-за текста вместо 0 исправлены, остались ошибки деления на ноль в колонке I. Ошибки с наценкой возникли из-за того, что мы изначально не указали в формуле, что именно нужно показывать при делении на 0. Запишем в ячейку I21 формулу, что при любой ошибке следует показывать прочерк: =ЕСЛИОШИБКА (H21/G21; «-»)

Скопируем ячейку I21 в ячейки I21:I23 и избавимся от ошибок:

На практике ошибки в наименованиях исходных данных часто устраняют с помощью операции Поиск и замена, которую вызывают нажатием клавиш Ctrl+F с клавиатуры.

Мы можем искать и заменять как числа, так и текст. Как с учётом Регистра (заглавных и прописных букв), так и без такого учёта. Искать и редактировать все ячейки сразу или по очереди. Задавать для поиска и замены формат ячеек или не задавать.

Важно запомнить такие моменты: если мы предварительно выделили область, то «Поиск и замена» будут работать только в этой области, не выходя за её пределы. А16:D18 в примере. Если бы мы не выделили область и табличный курсор стоял бы на одной из ячеек, то поиск и замена произошли бы на всём листе, а не только в ячейках А16:D18.

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

Подстановочный знак»?» – означает замену одного символа, а подстановочный знак «*» – любое количество символов. Если же требуется найти сами сами знаки»?» или «*», то перед ними ставят тильду. Например, если ищем текст со знаком вопроса «кто?», то записать его в поиске надо как «кто~?».

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

Сводные таблицы

Теперь поговорим о таком важном инструменте представления сгруппированных данных, как Сводные Таблицы.

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

Например, Хозяину Ларька интересно, сколько было продано в кг овощей каждого из видов и итого овощей в каждый из дней, и он быстро хочет видеть таблицу с этой информацией на основе нашей исходной таблицы. Названия товаров нужно сделать как строки, Даты – как столбцы, а Количество проданного в килограммах – как сумму значений.

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

Сначала выделяем ту область Исходной таблицы, на основе которой хотим создать Сводную таблицу. Например, выделим диапазон А8:K23.

На вкладке «Вставка» нажимаем на команду «Сводная таблица» и появляется окно «Сводная таблица из таблицы или диапазона».

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

Оказались на новом Листе4 с открывшейся вкладкой «Анализ сводной таблицы». Эту вкладку можно вызвать, встав курсором на область Сводной таблицы (ячейки А3:С20 на рисунке).

В правой части листа видим «Список Полей» сводной таблицы, готовый к редактированию. Первая строка выделенного диапазона исходной таблицы, то есть ячейки с названиями столбцов – это Поля сводной таблицы. Поля Сводной таблицы называются так же, как назывались столбцы исходной таблицы. Содержимое столбцов исходной таблицы – это содержимое Полей сводной таблицы.

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

Выполним задание Хозяина Ларька: переместим поле «Дата продажи» в область столбцов, поле «Товар» – в область строк, поле «Продано в кг» в область Значения и поле «Новая Группа» в область Фильтры.

Если не все поля видны, найти нужное поле можно с помощью Поиска или с помощью Ленты прокрутки.

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

Выберем в фильтре те Группы товара, которые хотим увидеть в Сводной Таблице.

Останется лишь таблица с Овощами:

Предположим, что Хозяин Ларька передумал и хочет теперь видеть строки по Овощам и Фруктам без наименования Товаров. В готовой Сводной таблице это сделать быстро и просто:

Вызываем список полей Сводной таблицы поставив курсор на любую ячейку сводной таблицы, например на B7, и вызвав меню правой кнопкой мыши. Список полей также можно вызвать со вкладки Анализ сводной таблицы.

В Списке полей меняем местами поля Товар и Новая Группа: перетаскиваем Товар из области Строки в область Фильтры, а поле Новая Группа из области Фильтры в область Строки.

С помощью кнопок фильтра можно фильтровать не только те поля, которые вынесены в область Фильтры (Товар), но и область Столбцов (Дата продажи) и область Строк (Группы товара).

Хозяин Ларька опять передумал, и хочет теперь видеть столбцами названия Товара, а строками – Даты продаж. В Сводной таблице за секунды можно выполнить и этот запрос: снова вызываем список полей, перетаскиваем поле «Товар» в область Столбцы, а поле «Дата продажи» в область Строки. Поле «Новая Группа» переместим из области Строки в область Фильтры.

Как видим – с помощью сводной таблицы можно быстро и гибко настроить отображение итогов.

На практике часто пользуются возможностью ввести в сводной таблице Новое Вычисляемое Поле. Например, введём Новое Вычисляемое поле «Наценка», которое = Маржа/Затраты на закупку. В последних версиях Excel вставить вычисляемое поле можно через Вкладку «Анализ сводной таблицы», нажав на «Поля, элементы, наборы» и выбрав «Вычисляемое поле». В ранних версиях Excel Вычисляемое Поле можно было выбрать из меню, поставив курсор в ячейку сводной таблицы и нажав на правую кнопку мыши.

В сводной таблице появилось новое поле «Сумма по полю Наценка, %». Но столбцы в сводной таблице появились не в процентном формате, а в числовом с округлением до 1, из-за чего видны только 0 и 1. Чтобы в сводной таблице назначить столбцам с наценкой Процентный формат, сначала выделим все столбцы с Наценкой. Для этого наведём курсор на ячейку С6 и в момент появления чёрной стрелочки, направленной вниз, нажимаем левую кнопку мыши. Все столбцы с наценкой выделятся. Далее жмём на «%» на вкладке Главная, как показано на рисунке, либо вызываем правой кнопкой мыши меню и выбираем Формат ячейки – Процентный. И вместо 0 в столбцах Наценки теперь увидим значения от 0% до 49%, а вместо 1 – значения от 50% до 149%.

Если слишком длинные имена столбцов мешают нам, то можем их изменить. Например, заменим «Сумма по полю Продано в кг» на «Кг», а «Сумма по полю Наценка,%» на «Нац.%».

Наводим курсор мыши на Поле, имя которого хотим изменить, например на ячейку С6, и жмём правую кнопку мыши. В меню выбираем Параметры поля значений. Попав в параметры поля значений, меняем Пользовательское имя. Вместо «Сумма по полю наценка, %» вбиваем «Нац.%» и жмём ОК.

Аналогично меняем название «Сумма по полю Продано в кг» на «Кг». Получили компактную Сводную таблицу.

Получили компактную итоговую сводную таблицу

Выделим главное, что надо запомнить новичкам о сводных таблицах:

Сводные таблицы обычно создаются на основе уже созданных исходных таблиц.

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

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

Сводные таблицы можно легко редактировать: добавлять и убирать поля, менять местами столбцы и строки, фильтровать отображаемую информацию.

Диаграммы

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

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

Покажем на нашем примере, как с помощью Круговой диаграммы показать Долю в Общей Марже от продажи Яблок, Помидоров и Огурцов.

Обычно диаграммы строят так (но есть и другие способы):

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

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

Выделим в исходной таблице данные, которые нам нужны: ячейки H2 (название Диаграммы, «Маржа, руб»), H3:H5 (значения для построения диаграммы) и B3:B5 (названия товаров, доли которых мы хотим увидеть на Диаграмме). Такие названия называют легендой Диаграммы.

Примечание: выделить несколько ячеек, находящихся в разных местах листа одновременно, без потери выделения предыдущих ячеек можно, если нажать клавишу СTR. Не отпускать CTR, пока с помощью мыши не выделить нажатием левой кнопки мыши все нужные ячейки в разных местах листа. Чётное нажатие курсором мыши на уже выделенную ячейку при нажатой клавише CTR отменяет выделение. Этот приём полезно запомнить и использовать.

На листе Вставка нажмём на значок Круговой Диаграммы, и перейдём в конструктор Диаграммы.

Диаграмма появилась, но исходные данные в ней отображены некорректно. Чтобы исправить, наводим курсор на область построения диаграммы (в центр) и жмём правую кнопку мыши. В выпавшем окне выбираем «Выбрать данные».

Проблема прояснилась: товары, которые должны быть подписями, там не видны. Жмём на кнопку «Строка/Столбец»

Теперь всё стало на свои места. Но у диаграммы нет названия.

Жмём на кнопку «Изменить» в элементах легенды и назначаем Именем Ряда1 «Маржа, руб». Сделать это можно, наведя курсор мыши на H2 (с названием Маржа, руб) и кликнув левой кнопкой. После чего «Маржа, руб» появится как название диаграммы. Можно и просто вбить в это поле «Имя ряда» любое имя с клавиатуры.

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

«Добавить подписи данных». Легенду с названиями товара можно перетащить в любое место с помощью курсора мыши, например, разместить слева. А можно вызвать правой кнопкой мыши меню, выбрать «формат легенды» и более детально отредактировать легенду.

Добавляем подписи данных

Примечание: если бы в момент выбора данных для построения диаграммы мы сразу выделили бы два диапазона данных: H2:H5 и B2:B5, и лишь затем нажали бы на кнопку построения Круговой диаграммы, то нам не пришлось бы проделывать лишней работы. Программа предположила бы, что столбец без чисел (Итоги) – это категории, а Столбец с числами (Маржа) – это значения. И сразу выдала бы нам верный вид диаграммы, к которому мы в итоге пришли.

Посмотрим внимательно, что мы видим в строке формул в момент выделения диаграммы:

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

Если диаграмма не нравится, всегда можно её изменить. Например, Хозяин ларька захотел, чтобы вместо рублей в круге диаграммы отображались бы Доли в % и Названия товара. Наводим курсор мыши в область диаграммы, которую хотим изменить. Жмём правую кнопку мыши. В выпавшем меню выбираем формат подписей данных. Отмечаем галкой «доли» и «имя категории» и убираем галку с «значение».

Теперь легенда (надписи товаров) стала нам не нужна, можем удалить её с диаграммы. Наводим курсор мыши на область легенды, кликаем правой кнопкой и в меню выбираем «Удалить».

Получили требуемый вид Круговой диаграммы.

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

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

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

Помимо Круговых Диаграмм, часто используют Графики и Гистограммы. Создадим на основе нашей таблицы для примера смешанный тип Диаграммы, на котором будет и График – линия в динамике, и Гистограмма – колонки в динамике.

Хозяин Ларька желает видеть по Помидорам динамику Маржи и Наценки на одном графике.

Прежде всего, выделим ячейки с требуемыми данными: А8:B23 – Даты для динамики и Виды товаров, Н8:I23 – Маржа в руб. и Наценка в %.

После выделения нужных ячеек жмём на кнопку «Комбинированная Диаграмма» на вкладке Вставка. Сразу получаем Комбинированную диаграмму в таком виде:

Чтобы в диаграмме остались только Помидоры, можно воспользоваться фильтром исходной таблицы. Также можно отобрать только Помидоры вызвав правой кнопкой мыши меню, а затем нажав на «Выбрать данные».

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

Выбрать исходные данные для Ряда также можно и в Строке формул.

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

Заключение

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

Часто бывает так, что сотрудник отправил файл Excel коллеге, а у коллеги файл «Не открывается». Обычно это связано с тем, что у коллеги установлена на компьютере ранняя версия Excel, которая не распознаёт более позднюю версию. Чтобы решить эту проблему, отправителю файла нужно сохранить файл в предыдущей версии. Для этого достаточно перейти на вкладку Файл, нажать «Сохранить как» и выбрать более раннюю версию Excel, например «Книга Excel 97—2003». Помимо ограничения по объёму информации (в ранних версиях возможен не миллион строк, а всего шестьдесят четыре тысячи), это может привести к ухудшению цветовой палитры файла (в ранних версиях она была беднее) и некоторых функций. Зато любая версия Excel открывает файлы всех предыдущих версий, поэтому Excel файл в формате «Книга Excel 97—2003» откроют все поздние версии.

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

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

На этом Курс закончен. Надеюсь, он был Вам полезен. Информацию о видео-курсе по этому материалу и возможности пройти тест можно найти на моём сайте http://matrixcheck.ru. На этом сайте Вы также найдёте информацию о часто используемых функциях Excel и кейсах решения конкретных практических задач. Связаться со мной по вопросам индивидуального тренинга можно также по почте a.skorodumov@mail.ru.