Занятие 3

Основы ввода и вывода данных

MS Excel от новичка до профессионала

Подробнее...

Занятие 3 Основы ввода и вывода данных

Презентация

Speaker DeckSlideShare

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

Навыки экзамена Microsoft Office Specialist (77-420):

Название навыка Номер навыка
Изменение представления книги (режимы просмотра) 1.4.6
Прикрепление панелей 1.4.11
Установка области печати 1.5.1
Печать отдельных листов 1.5.3
Установка масштабирования при печати 1.5.4
Настройка рабочих книг для печати 1.5.7
Добавление данных в рабочие листы 2.1.1
Копирование и вставка данных 2.1.3
Демонстрация работы механизма автозаполнения 2.1.4
Заполнение колонок данными 2.1.5
Вставка и удаление ячеек 2.1.6

Теоретическая часть:

  1. Ввод и редактирование простых данных

Видеоверсия

Текстовая версия

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

Текст в ячейке Excel

Введенный текст занимает только одну ячейку

Весь введенный текст находится в ячейке A1, ячейка B1 пустая, кстати, общее количество знаков в ячейке составляет 32 767, это около 30 страниц стандартного текста и это только размер одной единственной ячейки, поэтому не стоит переживать, что что-то не поместится.

На заметку
Максимальный размер листа Excel — 1 048 576 строк и 16 384 столбца, это более 17 миллиардов ячеек (17 179 869 184) и в каждой может поместится 30 страничный реферат (32 767 знаков).

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

Текст в ячейке Excel

Текст в ячейке Excel отображен и в строке формул

Завершить ввод данных в ячейку листа можно:

  1. Нажав на клавишу Enter или Tab.
  2. Нажав в пустом месте листа Excel.

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

Кстати, если у вас не получается редактировать данные непосредственно в ячейке, проверьте, возможно, у вас не разрешено редактирование данных в ячейке. Файл> Параметры> Дополнительно> Разрешить редактирование в ячейках.

Параметры Excel

В параметрах Excel можно запретить редактирование непосредственно в ячейке

Если вы передумали изменять содержимое ячейки, то нужно нажать клавишу Esc на клавиатуре. Если изменения в ячейке уже сделаны и их необходимо отменить, то следует воспользоваться либо горячим сочетанием клавиш: «Ctrl+Z», либо командой «Отменить» на панели быстрого доступа.

На заметку
Excel запоминает до 3000 последних команд.

Обратно же вернуть отмененную операцию можно с помощью сочетания Ctrl+Y, либо командой повторить на панели быстрого доступа.

На заметку
Распространенные горячие сочетания одинаково работают не только в программах пакета MS Office (Word, Excel, PowerPoint…), но и других программах, не имеющих отношения к Microsoft. Рекомендуется их запомнить.

Изменение ширины столбца

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

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

Числовые данные не поместились в ячейку по ширине

Числовые данные не поместились в ячейку по ширине

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

Ошибка ###### никак не сказывается на вычислениях!

Изменить ширину столбца можно несколькими способами:

  1. Подвести курсор мыши к границе смежных столбцов, чтобы он принял форму двунаправленной стрелки и с помощью зажатой левой клавиши мыши подобрать правильную ширину столбцов.
  2. Вызвать контекстное меню на заголовке столбца, выбрать пункт меню «Ширина столбца» и цифрами указать ширину столбца. Этот способ удобно использовать, когда необходимо указать одинаковую ширину сразу для нескольких столбцов, выделив группу можно единожды указать ширину.
  3. Выделение столбца в Excel

    Выделение столбца в Excel

    Выделяются столбцы простым растягиванием мышки с зажатой левой клавишей.

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

  4. Команда «Формат», отвечающая за подбор ширины столбца/высоты строки, находятся в группе «Ячейки» вкладки «Главная».
  5. Команда Формат на ленте интерфейса

    Команда «Формат» на ленте интерфейса позволит настроить строку или столбец

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

Редактирование содержимого ячеек

Редактировать данные можно непосредственно в ячейке, а можно в строке формулы.

Каким образом вы будете редактировать данные не важно, но есть несколько особенностей:

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

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

Следует также отметить, что в режиме редактирования меняется поведение клавиш Home и End они перемещают курсор в начало или конец строки в пределах редактируемой ячейки.

Какой режим редактирования предпочесть в строке формул или в ячейке?

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

Редактирование большого объема данных в ячейке

Редактирование большого объема данных в ячейке

Что нужно знать при редактировании больших объемов данных в ячейках, так это как начать новую строку, ведь простое нажатие Enter закончит режим редактирования. Для начала нового абзаца в ячейке Excel нужно воспользоваться сочетанием клавиш «Alt+Enter».

Для начала нового абзаца в ячейке Excel нужно воспользоваться сочетанием клавиш «Alt+Enter».

Удаление и очищение содержимого ячеек

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

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

Использование клавиши Delete равнозначно команде «Очистить содержимое», которую можно найти в контекстном меню

Очистка содержимого с помощью контекстного меню

Очистка содержимого с помощью контекстного меню

либо на вкладке «Главная» группа «Редактирование»

Очистка содержимого с помощью команд на ленте интерфейса

Очистка содержимого с помощью команд на ленте интерфейса

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

Добавление/удаление строк, столбцов и ячеек

Последним пунктом, рассмотренным в разделе ввода и редактирование простых данных будет добавление на лист Excel новых строк и столбцов, их удаление.

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

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

Удаление и добавление новых строк или столбцов на лист Excel

Удаление и добавление новых строк или столбцов на лист Excel

Если вставляется столбец на лист, то он будет вставляться слева от текущего, а строка вставляется выше текущей. Текущим столбцом/строкой считается тот, на котором находится маркер выделения активной ячейки.

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

Добавление или удаление строк/ столбцов с помощью контекстного меню

Добавление или удаление строк/ столбцов с помощью контекстного меню

Часто операции вставки и удаления строк/столбцов проводят с использованием контекстного меню по заголовку.

Для удаление/ добавления строки или столбца можно вызвать контекстное меню на заголовке

Для удаление/ добавления строки или столбца можно вызвать контекстное меню на заголовке

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

  1. Вставка и удаление ячеек

Видеоверсия

Текстовая версия

Частным случаем операцией вставки или удаления можно считать вставку/удаления одной ячейки. Логика проведения такой операции заключена в простое правило:

Количество ячеек на листе неизменно, соответственно, при удалении ячейки на листе появляется новая пустая ячейка, а при вставке – из листа удаляется одна ячейка

Этому правилу, кстати, подчиняется и удаление/добавление строк или столбцов, но там это не столь заметно, поскольку результат удаления/добавления столбца или строки очевиден и легко предсказуем.

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

Удаление и добавление отдельной ячейки на лист Excel

Удаление и добавление отдельной ячейки на лист Excel

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

Способы вставки ячеек на рабочий лист

Способы вставки ячеек на рабочий лист

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

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

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

В общем, удалять отельные ячейки нужно с осторожностью.

  1. Ввод различных типов данных

Видеоверсия

Текстовая версия

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

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

В Excel существует несколько инструментов, позволяющих ускорить процесс ввода текстовых меток одним из них является инструмент автозавершение.

Автозавершение (AutoComplette)

Если в столбце встречаются повторяющиеся значения, Excel предложит вариант автозавершения на основе анализа предыдущих записей.

Работа автозавершения

Демонстрация работы автозавершения

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

Включение/отключение данной функции находится по адресу: Файл/ Параметры/ Дополнительно/ Автозавершение значений ячеек (в разделе «Параметры правки»)

Ввод числовых данных

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

Диалоговое окно Формат ячеек

Варианты представления числовых данных можно узнать в диалоговом окне «Формат ячеек»

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

Пример различных вариантов представления числовых форматов

Пример различных вариантов представления числовых форматов

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

В Excel предусмотрены специальные символы, которые можно использовать вместе с числовыми данными в ячейках листа:

Символ Описание
+ перед числом опциональный параметр, обозначающий, что значение в ячейке будет положительным;
— или () отрицательное значение, пример использования: «-500» или «(500)»;
% означает, что значение составляет часть от 100 (проценты), пример использования: 50%. 50% равняется 0,5;
= означает начало ввода формулы, все, что введено после этого знака Excel попытается вычислить, например, если записать «=50-40», то в ячейке получится 10, а если записать «50-40», то это значение будет воспринято как текст;
/ многофункциональный символ может использоваться для обозначения даты, например, 15/04/1983, что равняется 15.04.1983, или 15/04 будет записано, как 15 апр. В тоже время, знак «/» используется и для обозначения дробного числа, если используется вместе со знаком «=», например, =1/2, что равняется 0,5;
: используется для обозначения времени, например, 6:00 – шесть часов утра;
. и , использование этих двух символов зависит от региональных настроек системы пользователя, а также настроек самого Excel. По умолчанию Excel наследует региональные настройки системы, поэтому, если операционная система настроена на регион Россия или Украина, то «.» воспринимается как разделитель дат, например, запись 15.04.1983 идентична 15/04/1983, а «,» – это разделитель десятичной дроби, например, 15,83. Если же система имеет настройки США, то там «.» используется, как разделитель десятичной дроби, а символ «,» используется улучшенного восприятия больших чисел, т.е. разделения разрядов, с целью улучшенного восприятия больших чисел.

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

Настройка разделителей в параметрах Excel

Настроить разделители можно в параметрах Excel

Работа с датами

Рассмотрим работу с датами более подробно.

Горячее сочетание
Чтобы ввести текущую дату необходимо воспользоваться сочетанием Ctrl+; (одновременно нажать: Ctrl+Shift+4) а для времени Ctrl+: (одновременно нажать: Ctrl+Shift+6).

Представление дат в Excel зависит от региональных настроек системы, это важно понимать, поскольку неправильно введенная дата программой может начать восприниматься как обычный текст.
Например, в России и Украине даты вводятся как: дд.мм.гг, а в США мм.дд.гг, поэтому в русскоязычной системе запись «1/28/28» Excel воспримет как обычный текст, в англоязычной версии это будет 28 января 2028 года. Для русскоязычной версии следует ввести 28/1/28, либо 28.1.28.

Для конкретной ячейки или группы ячеек можно переопределить формат отображения дат, выбрав команду «Формат ячеек…» в контекстном меню, но это не повлияет на их первоначальный ввод. Т.е. даже, для ячейки, которая будет отображать даты в формате мм.дд.гг (США), в русскоязычной версии Excel следует вводить как дд.мм.гг.

Применение к ячейке формата даты

Применение к ячейке формата даты

При вводе даты лучше всего вводить год полностью, но можно и вводить только последние 2 цифры, однако здесь важно понимать, что числа от 00 до 28 воспринимаются Excel как 2000 … 2028 год, а от 37 до 99, как 1937…1999 года. Вот такая особенность.

Если нужно быстро вставить текущую дату или время в ячейку, то для этого служит горячее сочетание клавиш: «Ctrl+;», «Ctrl+:», но для русскоязычной версии вводить нужно так будто вы в русской раскладке, т.е. «Ctrl+Shift+4» и «Ctrl+Shift+6» не зависимо от текущей раскладки клавиатуре.

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

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

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

Итак, инструменты, отвечающие за работу автозаполнения находятся:

  • Вкладка «Главная», группа «Редактирование», команда «Заполнить».
  • Автозаполнение с ленты интерфейса

    Автозаполнение с ленты интерфейса

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

    Маркер автозаполнения

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

Выбор дополнительных опций автозаполнения

Выбор дополнительных опций автозаполнения

Если растянуть ряд с использованием правой клавиши мыши, то дополнительные опции для автозаполнения появятся сразу

Растягивание с помощью правой клавиши мыши

Растягивание с помощью правой клавиши мыши

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

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

Очищение содержимого с помощью маркера автозаполнения

Что касается числовой информации, то Excel анализирует выделенные ячейки и пытается предугадать продолжение ряда, например, если выделить два числа 1 и 2, а потом заполнить список автозаполнением, то продолжение ряда будет: 3, 4, 5, и т.д., в то же время, если выделить 1 и 3, то продолжение будет: 5, 7, 9 и т.д. В автозаполнении не обязательно использовать только числа, можно дописывать текст, например, ряд: «мужчина 1, мужчина 2″, будет продолжен, как: » мужчина 3, мужчина 4 и т.д.».

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

Посмотреть существующие списки, или создать свой, можно в настройках Excel: Файл/ Параметры Excel/ Дополнительно/ Группа Общие/ Изменить списки…

Настройка пользовательских списков для автозаполнения

Можно создать свои списки автозаполнения, либо изменить существующие

Кстати списки в разные, в зависимости от языкового варианта табличного процессора, для русскоязычной версии Office списки выглядят так:

Демонстрация стандартных списков в русскоязычной версии Excel

Демонстрация стандартных списков в русскоязычной версии Excel

А вот система с английским и украинскими языковыми пакетами Office

Списки с английским и украинским языковыми пакетами

Списки с английским и украинским языковыми пакетами

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

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

Некоторые хитрости работы автозаполнения

Некоторые хитрости работы автозаполнения

Несколько примеров работы функции автозаполнение в Excel.

Примеры работы автозаполнения

Примеры работы автозаполнения

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

Мгновенное заполнение (Flash Fill)

Работа функции «Мгновенное заполнение» похоже на работу автозаполнения, но, все же, функция несколько сложнее. Эта возможность сравнительно новая, т.к. впервые появилась только в 2013 версии Excel.

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

Работа мгновенного заполнения

Работа мгновенного заполнения

Функция мгновенного заполнения находится на вкладке «Данные», группа «Работа с данными», можно использовать горячее сочетание клавиш: «Ctrl+E».

Функция мгновенного заполнения на вкладке Данные

Функция мгновенного заполнения на вкладке «Данные»

Горячее сочетание
Ctrl+E вызывает функцию мгновенного заполнения

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

Создание электронных адресов с помощью мгновенного заполнения

Создание электронных адресов с помощью мгновенного заполнения

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

Дополнительные примеры работы мгновенного заполнения

Дополнительные примеры работы мгновенного заполнения

На заметку
Больше примеров мгновенного заполнения по ссылке внизу данного занятия
  1. Вырезка, копирование и вставка данных

Видеоверсия

Текстовая версия

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

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

Функции вырезки, копирования и вставки можно производить различными способами:

  • с помощью мышки;
  • с помощью команд ленты интерфейса;
  • с помощью горячих сочетаний клавиш (Ctrl+C (копирование), Ctrl+X (вырезка), Ctrl+V (вставка));
  • с помощью панели буфера обмена MS Office.

Копирование и перемещение данных с помощью мышки

По умолчанию режим перетаскивания (drag-and-drop) включен, поэтому можно просто выделить мышкой целевые ячейки, нажав на край выделения (только не на маркер автозаполнения) перетащить в новое место на листе, не забыв удерживать клавишу Ctrl (возле курсора должен появится небольшой плюсик). Эта же операция без зажатой клавиши Ctrl приведет к перемещению данных.

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

Перетягивание диапазона мышкой

Перетягивание диапазона мышкой

Манипуляции с данными с помощью ленты интерфейса

Команды вырезки, копирования и вставки данных находятся в самой первой группе «Буфер обмена» вкладки «Главная», возможно, в Microsoft таким образом намекают на чрезвычайной востребованности команд данной группы.

Вырезать и копировать на вкладке Главная

Вырезать и копировать на вкладке Главная

Работа с командами предельно проста: выделяется участок, нажимается копировать, или вырезать, курсор устанавливается на новое место и нажимается кнопка «Вставить». При использовании команд ленты интерфейса у пользователя появляется дополнительные возможности, как копирования, так и вставки данных.

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

Копирование диапазона как рисунка

Копирование диапазона как рисунка

Копирование диапазона данных в растровом формате равнозначно снимку экрана.

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

Возможности вставки в Excel

Возможности вставки в Excel

Рассмотрим их по порядку.

Группа «Вставка»:

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

Если исходный диапазон данных имел вычисляемые формулы, то в новом месте вычисления могут быть нарушены, часто бывает, если данные копируются в другую книгу, поэтому возникает необходимость вставить только результат вычисления, за это отвечает группа «Вставить значения»

Группа «Вставить значения»:

  • «Значения» – просто значения без какого-либо форматирования;
  • «Значения и форматы чисел» – сохраняется форматирование числе, но не сохраняется форматирование самого диапазона;
  • «Значения и исходное форматирование» – полностью копируется форматирование исходного диапазона вместе со значениями.

Группа «Другие параметры вставки»:

  • «Форматирование» – вставляется не диапазон с данными, а только внешнее форматирование диапазона;
  • «Вставить ссылку» – будет проставлена ссылка на исходный диапазон, данная опция равнозначна формуле «=ссылка на ячейки», такой вид вставки удобно использовать, когда один и тот же диапазон используется в нескольких таблицах, и любые изменения должны отображаться во всех диапазонах;
  • «Рисунок» – вставляется картинка диапазона, команда равнозначна команде «Копировать как рисунок…» / векторный;
  • «Связанный рисунок» – операция аналогична «Рисунок» за тем исключением, что изменения в диапазоне будут автоматически отображаться в рисунке.

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

Диалоговое окно специальной вставки

Диалоговое окно специальной вставки

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

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

Горячее сочетание
Ctrl+C – копирование, Ctrl+X – вырезка, Ctrl+V – вставка данных.

Использование буфера обмена

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

Буфер обмена

Буфер обмена

Пользователь сможет вставить любой из участков данных, а не только последний. Буфер обмена относится ко всем приложениям Office и сохранив несколько объектов в Excel, можно его вызвать в Word или PowerPoint и вставить записанные там данные, что, несомненно, удобно.

Буфер обмена един для приложений пакета MS Office

Буфер обмена един для приложений пакета MS Office

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

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

Работая, примеру, в программе Word мы может скопировать определенный участок текста, например, длинное название компании, а потом простым сочетанием клавиш Ctrl+V вставлять его в нужные места без необходимости повторного копирования, если, конечно, мы ненароком не скопировали позже другой фрагмент.

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

Подсвеченный диапазон

«Подсвеченный» диапазон

Пока данный ободок мерцает мы может вставлять данные в нужные места один или несколько раз, однако, как только мы начнем редактировать какую-то ячейку или нажмем «Esc», мерцание прекратится и вставить диапазон не получится. придется повторно его выделить и скопировать. Такое поведение несколько отличается от привычного в Word, когда помещенный объект можно вставить, написать часть текста еще раз вставить.

Если нам нужно несколько раз вставлять скопированный диапазон в промежутках с редактированием, то следует воспользоваться буфером обмена, однако, если нам надо данные только одной ячейки, то можно не прибегать к буферу обмена, а скопировать не ячейку, а данные в ячейке. Для этого нужно перейти в режим редактирования данных в ячейке, лучше всего в строке формул, выделить все или часть содержимого и скопировать (Ctrl+C). Никакого ободка не будет, а вставку данных можно производить аналогично, как это делается в Word или другом приложении.

Копирование не ячейки а данных из ячейки

Данные из строки формул скопируется без форматирования

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

  1. Настройка параметров просмотра для книг и листа

Видеоверсия

Текстовая версия

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

Масштаб листа

На заметку
Изменение масштаба документа не влияет на распечатываемый документ.

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

Изменение масштаба с ленты интерфейса

Изменение масштаба с ленты интерфейса

Изменить масштаб можно используя небольшие кнопки управления на панели состояния внизу книги Excel.

Управление масштабом с панели состояния

Управление масштабом с панели состояния

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

Диалоговое окно Масштаб

Диалоговое окно Масштаб

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

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

На заметку
Быстрее всего изменять масштаб можно колесиком мышки с зажатой клавишей Ctrl

Закрепление первых строк и столбцов

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

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

Команда закрепления областей находится в группе «Окно» вкладки «Вид».

Закрепление областей в Excel

Закрепление областей в Excel

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

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

Например, выполнение команды «Закрепить области» с установленным курсором в ячейке B4, закрепить первые 3 строки и столбец «A». Закрепленные области отделяются слегка заметной серой линией на листе.

Демонстрация работы закрепления областей

Закрепятся области выше и левее выделенной ячейки

Если на листе уже есть закрепленные области, то команда «Закрепить области» меняет свое название на «Снять закрепление областей» и для создание новой закрепленной области следует предварительно убрать предыдущее закрепление.

На листе одновременно может быть закреплена только одна область

Режимы просмотра книги

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

Переключение режимов представления

Переключить режимы просмотра можно с информационной панели

Страничный режим

Страничный режим

Страничный режим позволяет увидеть сколько реальных листов займет рабочий лист Excel при печати

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

Разметка страницы

Режим разметки страницы

С помощью представления разметки страницы можно настроить границы страницы. Этот режим максимально похож на стандартный режим редактирования в Word.

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

  1. Предпросмотр и печать

Видеоверсия

Текстовая версия

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

Опции печати находятся в представлении Backstage.

Настройка печати в представлении Backstage

Настройка печати в представлении Backstage

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

Горячее сочетание
Зайти в представление Backstage на команду «Печать» можно с помощью горячего сочетания клавиш Ctrl+P.

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

Команда быстрой печати на панели быстрого доступа

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

Команду быстрой печати целесообразно применять в случае, когда настройки листа печати были сделаны ранее и изменились расчетные данные. В противном случае, вы будете неприятно удивлены тем на сколько отличается результат печати от желаемого, если, конечно, не работаете в Excel в режиме «Разметка страницы», тогда результат печати будет мало отличатся от того, что на экране.

Настройка области печати

Одной из возможностей, которые доступны пользователю при печати является печать только выделенного диапазона. В Excel, аналогично Word, можно выделить диапазон, а в меню «Печать» отправить на печать только данный выделенный диапазон.

Печать выделенного диапазона

Можно вывести на печать только выделенный диапазон

Но в Excel есть дополнительные возможности настройки диапазонов для печати. Для настройки диапазона, следует, после выделения целевой области перейти на вкладку «Разметка страницы» и выбрать команду: «Область печати / задать». Можно последовательно выделять дополнительные области и добавлять их в область печати.

Добавление области печати

Можно задать области печати, а потом их распечатать

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

Области печати в страничном режиме просмотра

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

Области печати можно посмотреть, если в окошке «Имя» выбрать диапазон «Области_печати» (он автоматически создается при задании первой области печати)

Области печати в окошке Имя

Excel самостоятельно создает именованные области, при задании областей печати

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

Настройка параметров печати в Excel

Если нужно напечатать лист стандартно, вовсе не обязательно его очищать от областей печати

Впрочем, если отметить опцию «Игнорировать области печати», тогда листы будут печататься стандартно, в нашем случае будет напечатано 2 листа, а не 4.

Удаление областей печати выполняется командой: Области печати/ Убрать на вкладке «Разметка страницы»

Удаление областей печати

Удаление областей печати

Печать выбранных листов

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

Печать отдельных листов книги

Можно напечатать только выбранные листы книги

Печать всей книги

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

Печать всех листов книги

Можно напечатать всю книгу сразу

Дополнительные опции печати в Excel

Среди дополнительных опций вывода информации с книг Excel на печать следующие:

  • режим упорядочивания копий, актуально, если печатается несколько копий на нескольких печатных страницах;
  • ориентация страницы (книжная или альбомная);
  • размер бумаги (А4, А5, А3 и т.д.);
  • настройка полей (отступы от краев бумаги);
  • опции масштабирования.

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

Масштабирование при печати – очень полезная опция, которая сможет, при правильном применении, сэкономить не только прилично времени, но и нервы.

Опции масштабирования при печати представлены в таблице

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

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

Параметры страницы

Настройка масштаба печати в параметрах страницы

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

Масштабирование при печати

Пример масштабирования при печати

Настройку параметров при печати можно сделать не только в представлении Backstage, но и во вкладке «Разметка страницы», группа параметры страницы.

Настройка печати из ленты интерфейса

Настройка печати из ленты интерфейса

Настройка принтера

Excel, как и любая другая программа, поддерживает работу с несколькими принтерами. Выбор принтера осуществляется в одноименной выпадающей команде в группе «Печать» представления Backstage. Зеленой галочкой отмечен принтер по умолчанию, т.е. тот принтер на который будет выполнятся команда «быстрой печати», если в текущем сеансе работы с Excel его не изменили на другой.

Принтер по умолчанию меняется в настройках системы. Для Windows 10 это: Параметры/ Устройства/ Раздел принтеры и сканеры
Установка принтера в Windows 10

Установка принтера в Windows 10

В данном вопросе занятия были рассмотрены базовые возможности подготовки документа к печати. Тонкая настройка параметров, разрывов, колонтитулов будет рассмотрена в 9-м занятии курса.

Быстрое повторение материала:

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

Практические задания:

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


Задание 1. Автозаполнение ячеек

  1. Открыть книгу из предыдущего занятия «Задание 2.4 выполнено».
  2. Объединить в группу все листы книги за исключением таких: Отчеты по дням, Лекции 1ЮДБ и Лекции 1ЭБД.
    Подсказка
  3. Пронумеровать ячейки A38:A51, введя в первые две цифры 1 и 2, а остальные заполнить с помощью автозаполнения.
    Подсказка
  4. Перейти на лист «Отчеты по дням», установить курсор в ячейку A16 и, с зажатой клавишей Ctrl, схватить за маркер заполнения пронумеровать еще 15 ячеек.
  5. Инфо-справка: зажатая клавиша Ctrl позволяет продолжать ряд, даже ясли за основу взята одна ячейка.

  6. Сохранить документ под именем «Задание 3.1 выполнено».
Посмотреть решение


Задание 2. Копирование и перемещение данных на листе

  1. Открыть документ из предыдущего задания «Задание 3.1 выполнено».
  2. На листе «Отчеты по дням» выделить диапазон таблицы B2:H16 и скопировать его на место B17:H31 перетягиванием мышки с удержанием клавиши Ctrl.
    Подсказка
  3. Установить курсор в ячейку A2 и выделить всю таблицу с помощью клавиатуры, используя сочетание: Ctrl+Shift+End.
  4. Скопировать выделенный диапазон (Ctrl+C), установить курсор в ячейку A32 и вставить скопированный диапазон (Ctrl+V).
  5. Сохранить документ под именем «Задание 3.2 выполнено».
Посмотреть решение

Задание 3. Использование мгновенного заполнения (Flash Fill)

  1. Открыть документ из предыдущего задания «Задание 3.2 выполнено».
  2. На листе «Отчеты по дням» очистить от данных диапазон C2:C31.
  3. В ячейки C2:C4 ввести цифры: 1, 2, 2 (последние цифры обозначения групп).
  4. Установить курсор в ячейку C4 и выполнить команду мгновенного заполнения (Flash Fill).
    Подсказка
  5. Как можно заметить, ячейки C15, C30, C45, C60 иначе были обработаны мгновенным заполнением, т.к. напротив них обозначение группы не содержало числа в конце.
  6. Сохранить документ под именем «Задание 3.3 выполнено».
Посмотреть решение


Задание 4. Закрепление областей при просмотре документа

  1. Открыть документ из предыдущего задания «Задание 3.3 выполнено».
  2. На листе «Отчеты по дням» вызвать контекстное меню на любой ячейке таблицы, выбрать пункт: Таблица/ Преобразовать в диапазон.
  3. Пояснение: таблицы и преимущества, которые они дают, будут рассматриваться позже в рамках данного курса.

  4. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Закрепить верхнюю строку». Прокрутить лист вниз и убедиться, что первая строка всегда находится на экране.
  5. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Закрепить первый столбец». Прокрутить лист вправо и убедиться, что первый столбце всегда на экране. Прокрутить лист вниз и убедиться, что первая строка уже не зафиксирована.
  6. На вкладке «Вид» в группе «Окно» выбрать команду: «Закрепить области/ Снять закрепление областей». Установить курсор в ячейку C2 и выбрать команду: «Закрепить области/ Закрепить области».
  7. Убедиться, что при прокручивании листа вниз зафиксированной остается первая строка, а при прокручивании вправо – первые два столбца.
  8. Сохранить документ под именем «Задание 3.4 выполнено».
Посмотреть решение

Проверьте свои знания тестом:

Полезные ссылки по теме:

Работа мгновенного заполнения