Электронные таблицы Excel

Работа с диапазонами

Горячие клавиши

Перемещение по таблице

Быстрое перемещение по таблице
Если вы хотите скопировать табличку, то вы можете сделать это несколькими способами:
Выделением диапазона вручную
CTRL+A
Встать в левый верхний угол рабочего листа – где начинается нумерация строк и подписи столбцов
При помощи клавиш CTRL SHIFT и стрелочки на клавиатуре: встаем в первую ячейку будущего диапазона, зажимаем на клавиатуре CTRL и SHIFT, затем нажимаем на стрелочку вниз, затем вправо. Если хотим отщелкнуть строку, отпускаем CTRL и нажимаем на стрелочку вверх или в сторону.
Если вы хотите быстро спуститься в самый низ таблички, нажимаем CTRL и стрелочка вниз.

Автозаполнение
и автозавершение

Автозаполнение
Для того, чтобы воспользоваться функцией, необходимо выполнить следующие действия:
Вводим значение, например 1, день недели или название месяца
Встаем в ячейку со значением, наводим левой кнопкой мышки на правый нижний угол ячейки и когда появится черный крестик, начинаем тянуть вниз зажатой левой кнопкой.
Отпустите кнопку мыши, и вы увидите, что программа автоматически ввела вместо вас все названия месяцев.
Если вы вводите числовые значения, и ряд заполнился как 1..1…1…1, то вам необходимо нажать на маркер автозаполнения (Параметры) и выбрать “Заполнить”

Автозаполнение
Если вы хотите произвести автозаполнение дат по рабочим дням, то вам нужно:
Ввести значение даты
Протянуть левой кнопкой мышки, отпустить
Зайти в Параметры и выбрать “Заполнить по рабочим дням”
Если вы хотите произвести автозаполнение чисел с определенным шагом:
Введите значение
Протянуть правой кнопкой мышки, отпустить
Выбираем “Прогрессия”.
Если выбрана Арифметическая прогрессия и шаг, например 2, это означает, что к каждому последующему значению будет прибавляться 2.
Если выбрана Геометрическая прогрессия и шаг, например 2, это означает, что каждое последующее значение будет умножаться на 2.

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

Автозавершение
Функция Автозавершение работает только с непрерывной последовательностью ячеек.
В некоторых случаях Excel может попытаться использовать функцию Автозавершение для автоматического завершения слова и тогда, когда вам это не нужно. Если вы сначала ввели слово Умный, а затем в ячейку ниже хотите ввести слово Ум, Excel автоматически попытается применить функцию автозавершения для превращения того, что вы ввели, в слово Умный.
Чтобы все-таки ввести слово Ум, нажмите клавишу Delete на клавиатуре , как только достигнете конца более короткого слова.
Если вы находите эту опцию неудобной, вы можете ее отключить через:
Параметры Excel >>> Дополнительно >>> Параметры правки снимите галочку Автозавершение значений ячеек

Именованные диапазоны

Именованные диапазоны
Excel позволяет присваивать ячейкам и диапазонам имена.
Например, ячейку можно назвать НалогНаДоход, а диапазон — Заработная_Плата_Сотрудников.
Использование имен по сравнению с адресами ячеек или диапазонов имеет преимущества:
Упрощается процесс создания формул
Имя ячейки или диапазона можно вставить в формулу
Понятное имя диапазона (Процентная Ставка) запомнить легче, чем адрес ячейки (А123)
При вводе адресов ячеек и диапазонов легче ошибиться, чем при вводе имен
Можно быстро переместиться к областям рабочего листа, в которых находятся именованные ячейки или диапазоны. Для этого нужно воспользоваться полем Имя, которое находится слева в строке формул, щелкнуть на кнопке раскрытия списка и выбрать нужное имя из списка или команду.

Именованные диапазоны
Правила для создания именованных диапазонов:
В именах не должно быть пробелов. Вы можете воспользоваться символом подчеркивания, например Процентная Ставка
Можно использовать любые комбинации букв и цифр, но имя должно начинаться с буквы, знака подчеркивания или обратной косой черты
Имя не может начинаться с цифры (например, 2019год) или быть похожим на адрес ячейки (например, Q3). Если все-таки такие имена необходимо задать, можно прописать перед названием символ подчеркивания или обратной косой чертой, например _2019год или \2019год
Любые специальные символы и символы пунктуации, за исключением символов подчеркивания, обратной косой черты и точки, использовать в именах не разрешается
Имена должны содержать не больше 255 символов

Именованные диапазоны
Способы создания именованных диапазонов:
Выделяем диапазон и присваиваем ему имя в адресе ячейки (выделено желтым)
Как видим, Excel сам предлагает имя, основываясь на названии НАД выделенным диапазоном, и если нас это имя устраивает, нажимаем ОК.
Если содержащийся в ячейке текст приведет к созданию некорректного имени, Excel автоматически исправит это имя так, чтобы оно было корректным. Например, если в ячейке содержится текст Цена штуки (такое имя нельзя назначить ячейке, поскольку в нем содержится пробел), Excel заменит пробел символом подчеркивания (Цена_штуки).
2. Выделяем диапазон, которому хотим присвоить имя, идём на вкладку Формулы – Определенные имена – Задать имя

Специальная вставка

Вставка:
Вставить - вставляет из буфера обмена содержимое ячеек и форматы
Формулы - только формулы и значения (без атрибутов форматирования), содержащиеся в исходном диапазоне.
Формулы и форматы чисел - вставляет формулы и форматирование только чисел.
Сохранить исходное форматирование – вставляет формулы и все атрибуты форматирования.
Без рамок копируется все, за исключением рамок вокруг ячеек.
Сохранить ширину столбцов оригинала - вставляет формулы и дублирует ширину столбцов скопированных ячеек.
на рабочий лист как рисунок.
Транспонировать изменяет ориентацию скопированного диапазона, когда строки становятся столбцами, а столбцы — строками.
Значения - позволяет скопировать результаты расчетов по формулам, причем диапазоном назначения может быть как новый диапазон, так и исходный. В последнем случае первоначальные формулы будут заменены полученными значениями.
Значения и форматы чисел - вставляет результаты вычисления формул и атрибуты форматирования чисел.
Значения и исходное форматирование - вставляет результаты вычисления формул плюс все атрибуты форматирования.
Форматирование - вставляет только атрибуты форматирования исходного диапазона.
Вставить ссылку – в диапазоне назначения создается формула, которая ссылается на исходный диапазон.
Рисунок - вставляет скопированные данные на рабочий лист как рисунок.

Специальная вставка:
Наибольшие возможности при копировании предоставляет диалоговое окно Специальная вставка, которое открывается с помощью Скопировать – Правая кнопка мышки - Специальная вставка
В окне Специальная вставка расположено несколько пунктов, многие из которых идентичны опциям в раскрывающемся меню Вставка, которое представлено на предыдущем слайде.
Пункты из группы Операция позволяют выполнять арифметические операции со значениями и формулами в диапазоне.
Можно скопировать один диапазон в другой и выбрать операцию умножить. В результате Excel перемножит соответствующие значения исходного диапазона и диапазона назначения и полученными результатами заменит прежние значения диапазона назначения. Также можно вставить значения одной ячейки в диапазон ячеек с одновременным выполнением математических операций. Например, у вас есть столбец со значениями и вы хотите увеличить каждое из них на 13%. Чтобы выполнить такую операцию, введите 113% в любую пустую ячейку и скопируйте это значение. Затем выделите диапазон ячеек, содержащий значения, откройте диалоговое окно Специальная вставка, установите галочку “умножить” и щелкните на кнопке ОК. В результате каждое значение в выделенном диапазоне будет увеличено на 13%.

Умные таблицы

Умные таблицы
Преобразование обычной таблицы в умную:
Диапазон не должен содержать полностью пустых строк или столбцов. В противном случае Excel не сможет правильно определить диапазон ячеек для построения таблицы.
Встаньте в любую ячейку диапазона.
Выберите команду Вставка>>>Таблицы>>>Таблица (или при помощи горячих клавиш CTRL+T )
По умолчанию строки умной таблицы отображаются с чередующейся окраской двух разных цветов. При добавлении в таблицу новых данных правила форматирования уже существующих строк применяются и к новым данным - если значения в столбце были отформатированы как текст, то во всех новых строках этой таблицы они также будут отформатированы как текст.

Умные таблицы
Срезы - это один из способов фильтрации данных. Это более удобный способ и интуитивно понятен даже для тех, кто плохо разбирается в Excel и в них очень легко увидеть, какой из типов фильтрации был установлен.
Для того чтобы добавить один и более срезов, активизируйте любую ячейку таблицы и выберите команду Конструктор – Инструменты - Вставить срез.
В результате откроется диалоговое окно Вставка срезов, в котором будут представлены все заголовки, имеющиеся в таблице.
ВАЖНО: Срезы вряд ли будут полезны на колонки с числовыми значениями (например, Выручка)
Стиль среза можно изменить. Для этого нужно встать на срез, в появившейся вкладке ленты Срез выбрать стиль из предложенных. Так же на этой вкладке можно регулировать кол-во столбцов и строк, которые будут отобраться на срезе, а так же их высоту и ширину.
Размер Среза можно изменить, потянув за любой кружочек на контуре среза.

Умные таблицы
Итоги:
В таблице можно установить итоги, для этого нужно зайти на вкладку Конструктор таблиц (чтобы появилась эта вкладка, нужно встать в область таблицы), далее Параметры стилей таблицы и ставим галочку в Строка итогов
При наведении на стрелочку появляются опции отображения итогов (среднее, сумма, кол-во, мин, макс и тд)
По умолчанию итоги устанавливаются на последнюю колонку, но при активации ячейки, в которой должен быть итог, у нас появляется выпадающий список с возможностью выбора отображения.
Строка итогов очень удобна, когда мы имеем дело с фильтрацией (или срезами), и нам необходимо смотреть итоги только по отфильтрованным строкам.
К таблицам можно применять различные форматирования, как к обычным таблицам: форматы ячеек, условное форматирование.
В Таблице можно устанавливать сортировку.
Из умных таблиц можно строить сводные таблицы, удалять дубликаты.
Таблицу можно преобразовать в обычную, выбрав на ленте команду Преобразовать в диапазон

Виды ошибок в формулах

Типы ссылок

Изменение типа ссылок:
Абсолютные или смешанные ссылки можно ввести вручную, вставив перед столбцом или строкой знаки доллара. Можно также воспользоваться клавишей F4, нужно нажать клавишу несколько раз, чтобы программа "прокрутила" по циклу все четыре типа ссылок. Например, если в начале формулы вы вводите =А1, то первое нажатие клавиши преобразует ссылку на ячейку в $А$1, второе — в А$1, третье — в $А1, а четвертое вернет ей первоначальный вид — А1. Нажимайте клавишу до тех пор, пока не появится нужный вам тип ссылки.
Если клавиша F4 не срабатывает, вам необходимо нажимать Fn+F4, но это не всегда удобно. Вам нужно “активировать” F4, нажав одновременно Fn+Shift+F4.
Важно знать:
При вырезании и вставке формулы (при перемещении ее в другую ячейку) ссылки в формуле не изменяются, а при копировании формулы - изменяются.

Выпадающий список

Настройка выпадающего списка
Заранее создайте список, который вы хотите добавить в раскрывающийся список
Встаньте в ячейку, в которой хотите создать список.
Перейдите на вкладку Данные >>> Работа с данными >>> Проверка данных
3. В диалоговом окне выберите Тип данных – Список и в качестве источника укажите заранее созданный список
Подсказки:
В качестве Источника можно указать значения как перечисление через ; Например: 2019;2020;2021
Для того, чтобы в источник попадали новые значения автоматически, преобразуйте заранее созданный список в умную таблицу через горячие клавиши CTRL+T