rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Сортировка по произвольному списку значений

| Категория: Приемы и советы, Работа с табличными массивами |

6

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

Отсортируем в данной таблице записи по отделам в нужной нам последовательности:

sort1.png

  1. Выделить любую ячейку таблицы.
    Если исходный диапазон содержит пустые строки и/или столбцы, то необходимо выделить всю таблицу вместе с заголовками.
  2. На вкладке Данные [Data], в группе Сортировка и фильтр [Sort&Filter], выбрать Сортировка [Sort].
  3. Выбрать столбец для сортировки.
    Для сортировки по дням недели, названиям месяцев или произвольному списку значений в списке поля Порядок выбрать Настраиваемый список.
    Либо выбрать существующий список, либо, выбрав НОВЫЙ СПИСОК и ввести элементы списка, разделяя их между собой через запятую или через Enter.

sort3.png

Можно будет выбрать порядок сортировки:

sort4.png

И результат не заставит себя ждать:

sort5.png

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

  1. Выбрать Файл [File], Параметры [Option].
  2. В разделе Дополнительно [Advanced] в группе Общие [General] нажать на кнопку Изменить списки [Edit custom Lists].
  3. Поставить курсор в поле Импорт списка из ячеек [Import list from cells], выделить ячейки на листе и нажать кнопку Импорт [Import].

sort6.png

  1. Закрыть все окна, нажатиями на кнопки ОК.

Ограничение перемещений по листу. Ввод данных в определенные ячейки

| Категория: Приемы и советы, Работа с данными ячеек |

6

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

Эту задачу легко решить, используя защиту ячеек листа.

  • Первым действием, необходимо настроить атрибуты ячеек:
    Выделить ячейки.
    Щелкнуть правой кнопкой мыши по выделению и выбрать Формат ячеек [Format cells] или нажать клавиши Ctrl+1.
    Открыть вкладку Защита [Protection].
    Снять атрибут Защищаемая ячейка [Locked] с ячеек, которые после установки защиты листа должны изменяться.

protection1.png

  • Вторым действием установить защиту ячеек листа:
    На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Защитить лист [Protect Sheet].
    Флажок Защитить лист и содержимое защищаемых ячеек [Protect worksheet and contents of locked cells] должен быть установлен.
    В списке Разрешить всем пользователям этого листа [Allow all users of this worksheet to] снять флажок Выделение заблокированных ячеек [Select Locked Cells].
    Установить пароль в поле Пароль для отключения защиты листа [Password to unprotect sheet], иначе защита легко будет снята другим пользователем.
    Пароль может содержать только буквы az, цифры 09 и специальные знаки, например !, @, #, $, %, ^, &, *, (, ), +, =

protection2.png

Калибровочная диаграмма Термометр

| Категория: Диграммы, Приемы и советы |

5

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

termometr1.png

1. ПОДГОТОВКА ИСХОДНЫХ ДАННЫХ:

  • Создание элементов выбора ФЛАЖОК.
    На вкладке Разработчик [Developer] раскрыть список команды Вставить [Insert] и выбрать в группе Элементы управления формы [Form Controls] элемент Флажок [Check Box (Form Control)].
    Щелкнуть левой кнопкой мыши на листе с диаграммой в месте расположения элемента.
    Настроить параметры элемента – щелкнуть правой кнопкой мыши по элементу, выбрать Формат Объекта [Format Control]. На вкладке Элемент управления [Control] задать Связь с ячейкой [Cell link] – указать ячейку, в которую размещается значение ЛОЖЬ или ИСТИНА.
    Визуально убрать значение ИСТИНА или ЛОЖЬ в связанных ячейках – выделить ячейки, в диалоговом окне Формат ячеек [Format Cells] на вкладке Число [Number], выбрать числовой формат (все форматы) [Custom], в поле Тип [Type] ввести ;;; и нажать ОК.
  • Подсчитать общее количество заданий, которые необходимо выполнить с помощью функции СЧЁТЗ [COUNTA].
  • Подсчитать количество выполненных заданий с помощью функции СЧЁТЕСЛИ [COUNTIF].
  • Вычислить процент выполненных заданий – отношение выполненных заданий к общему числу заданий. Применить к результату процентный формат.

termometr3.png

2. ПОСТРОЕНИЕ ДИАГРАММЫ:

  • Выделить ячейку с процентом выполнения заданий.
  • На вкладке Вставка [Insert] выбрать Гистограмма [Column] и тип Гистограмма с группировкой [Clustered Column].

3. НАСТРОЙКА ВНЕШНЕГО ВИДА ДИАГРАММЫ:

  • Удалить данные горизонтальной оси – щелкнуть по оси и нажать Delete.
  • Убрать боковой зазор – щелкнуть правой кнопкой мыши по ряду и выбрать Формат ряда данных [Format Data Series], в группе Параметры ряда [Series Options] поставить Боковой зазор [Gap Width] – Без зазора (0%) [No Gap].
  • Изменить цвет заливки области построения диаграммы.
  • Добавить подписи данных – щелкнуть правой кнопкой мыши по ряду и выбрать Добавить подписи данных [Add Data Labels].