Расширенные возможности Excel

методист Малышева М.И.

 Занятие 1.  

Оглавление

Тема 1. Меню и вкладки в Excel........................................................................................................................... 1

Тема 2. Понимание книг и рабочих листов, строка состояния. Добавление и удаление строк и столбцов (за компьютером)..................................................................................................................................................... 1

Тема 3. Автоматическое заполнение ячеек листа данными................................................................................ 2

Автозаполнение ячеек числами....................................................................................................................... 2

Автозаполнение формулами........................................................................................................................... 3

Автозаполнение другими значениями............................................................................................................. 4

Добавление собственных списков................................................................................................................... 5

Тема 4. Создание раскрывающегося списка........................................................................................................ 7

Тема 5. Применение проверки данных к ячейкам.............................................................................................. 8

26 апреля 2021 года «Яндекс» объявил о запуске сервиса «Документы». Это онлайн-сервис для создания и совместного редактирования файлов — аналог облачного офисного пакета Google Docs.

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

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

Excel.

Тема 1. Меню и вкладки в Excel

Первое занятие мы начнём со знакомства с меню программы. Многие из вас уже знакомы с Excel, но  мы  уверены, что по каждой теме вы узнаете что-то новое и откроете для себя новые возможности по работе с программой Excel. 

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

Откроем таблицу "1_количество классов.xlsx ".

Есть определенные термины Excel, которые мы должны постоянно использовать. В Excel файл – это книга. Книга — это файл. Каждая рабочая книга состоит как минимум из одного рабочего листа и в них производится основная работа в данной программе. Листы можно добавлять, удалять, копировать, переименовывать, менять местами, менять цвет ярлыков (практика).

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

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

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

-----------------------------------------------------------------------------------------------------

Ctrl + стрелка вправо/влево/вверх/вниз - переходим в конец таблицы вправо/влево/вверх/вниз 

--------------------------------------------------------------------------------------------------------

Ctrl + Home/End - перемещение в начало/конец таблицы

-------------------------------------------------------------------------------

Shift + стрелка вправо/влево/вверх/вниз - выделение по одной ячейке вправо/влево/вверх/вниз

----------------------------------------------------------------------------------------------------------

Ctrl + Shift + стрелка вправо/влево/вверх/вниз - выделение диапазона заполненных ячеек вправо/влево/вверх/вниз

-----------------------------------------------------------------------------------------------------------

Ctrl + Shift + Home/End - выделение всей  таблицы, начиная с активной ячейки Щелчок мышкой в левом верхнем углу листа по треугольнику выделит весь лист.

----------------------------------------------------------------------------------------------------

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

За Окном имени находится строка формул. Здесь отображается содержимое той ячейки, которая является активной. Если содержимое ячейки получено с помощью формулы, то в строке формул мы увидим эту формулу. В этой строке формул мы можем редактировать формулу или содержимое ячейки. Также мы можем увидеть формулу в ячейке, если щёлкнем по ней двойным щелчком мыши. (практика)

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

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

И после этих кнопок мы видим масштаб, который можно менять так же с помощью Ctrl+ колёсико (скролл) мыши. (практика)

Добавление и удаление строк и столбцов в таблице (практика)

Вставить строку: щёлкаем правой кнопкой мыши на номере строки (выделив её), перед которой нам надо вставить строку и выбираем команду Вставить (или на ленте Главная кнопки Вставить и Удалить)

Удалить строку: щёлкаем правой кнопкой мыши на номере строки, которую надо удалить и выбираем команду Удалить

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

Формулы в таблице (Автосумма) практика

Форматирование таблицы (См. образец на втором листе.)

(практика) Цвет, рамки, шрифт, границы, выравнивание. См. образец на втором листе.

Тема 3. Автоматическое заполнение ячеек листа данными. 

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

-----------------------------------------------------

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

Ctrl+Z - отменить последние действия (или стрелка Отменить на Панели быстрого доступа)

Ctrl+Y - вернуть последние действия (или стрелка Вернуть на Панели быстрого доступа)

----------------------------------------------------------

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

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

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

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

См. способы в файле Функция автозаполнения.xlsx

Автозаполнение формулами

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

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

Автозаполнение другими значениями

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

Точно так же, можно произвести автозаполнение по дням недели (понедельник, вторник, среда…) или по месяцам (январь, февраль, март…). (практика)

Добавление собственных списков

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

1.       Делаем переход во вкладку «Файл».

2.       Переходим в раздел «Параметры».

3.       Далее, перемещаемся в подраздел «Дополнительно».

4.       В блоке настроек «Общие» в центральной части окна жмем на кнопку «Изменить списки…».

5.       Открывается окно списков. В левой его части расположены уже имеющиеся списки. Для того, чтобы добавить новый список записываем нужные слова в поле «Элементы списка». Каждый элемент должен начинаться с новой строки. После того, как все слова записаны, жмем на кнопку «Добавить».

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

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

Тема 4. Создание раскрывающегося списка

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

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

1.       На новом листе введите данные, которые должны отображаться в раскрывающемся списке.

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

3.       На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

4.       На вкладке Параметры в поле Тип данных выберите пункт Список.

5.       Щелкните поле Источник и выделите диапазон списка.

6.       Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки. 7. Установите флажок Список допустимых значений

8.       Откройте вкладку Сообщение для ввода.

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

9.       Откройте вкладку Сообщение об ошибке.

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

10.   Не знаете, какой параметр выбрать в поле Вид?

▪  Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или "Предупреждение". Если выбрать вариант "Сообщение", сообщение будет отображаться со значком  , а если "Предупреждение" — со значком  .

▪  Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.

Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок "Microsoft Excel" и сообщение "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен".

Тема 5. Применение проверки данных к ячейкам

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

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

2.           Выберите Данные > Проверка данных.

3.           На вкладке Параметры в списке Тип данных выберите подходящий вариант:

•       Целое число, чтобы можно было ввести только целое число.

•       Десятичное число, чтобы можно было ввести только десятичное число.

•       Список, чтобы данные выбирались из раскрывающегося списка.

•       Дата, чтобы можно было ввести только дату.

•       Время, чтобы можно было ввести только время.

•       Длина текста, чтобы ограничить длину текста.

•       Другой, чтобы задать настраиваемую формулу.

4.           В списке Значение выберите условие.

5.           Задайте остальные обязательные значения с учетом параметров Тип данных и Значение.

6.           Откройте вкладку Подсказка по вводу и настройте сообщение, которое будут видеть пользователи при вводе данных.

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

8.           Откройте вкладку Сообщение об ошибке, чтобы настроить сообщение об ошибке, и выберите Вид.

9.           Нажмите ОК.

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

Ctrl + F3 - диспетчер имён ячеек (можно удалить любое имя)