Маленькие полезности Excel

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

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

Относительные и абсолютные ссылки

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

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

Абсолютная ссылка с закрепленной строкой и колонкой будет выглядеть так: $A$1. Символ располагается в английской раскладке под клавишей с цифрой 4. Но можно ускорить преобразование ссылки в абсолютную, если нажать клавишу F4.

Выделение больших блоков

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

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

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

Выделение несвязанных блоков

vydelenie-nesvyazannyh-blokovЕсли необходимо выделить один блок, то все просто — зажимаешь левую клавишу мыши, и — выделяешь.

А как быть, если необходимо выделить два несвязанных между собой блока?

Для этого:

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

Вставка таблиц

tabliza1Не все еще знают об удобном инструменте, который называется «Таблица».

Как ни странно, рабочая область Excel называется не таблица, а лист.

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

Например:

  • можно выводить итоги в нижней части таблицы по столбцам — среднее значение, сумма, максимум, минимум и другие;
  • можно заполнять все ячейки в столбце автоматически, если в одну из ячеек этого столбца вводится формула;
  • можно одним нажатием кнопки мыши изменить дизайн таблицы, выбрав подходящий в меню «Стили таблиц»;
  • заголовки таблиц не пропадают, если таблица прокручивается далеко вниз;
  • при использовании формул, можно использовать имена таблиц и заголовки столбцов. Это существенно облегчает ввод формул;
  • если в формуле используется имя таблицы или колонки, то при добавлении значений в эту таблицу или колонку формула не теряет актуальности, как это может произойти в случае, если в формуле используется простое выделение диапазона;
  • другое…

А вставить таблицу несложно:tabliza

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

Можно работать!

Использование имен

Любую ячейку или диапазон можно обозначить каким-либо именем. Это удобно с нескольких точек зрения:

  • Формула, в которой вместо адреса ячейки, типа А5, внесено имя, типа «Выручка» становится более понятной. Например, какая из двух формул лучше воспринимается: «=C19-D19» или «=Выручка-Расходы»?
  • поименованный диапазон в формуле никуда не переместиться, если мы эту формулу будем протягивать на другие ячейки. Это особенно полезно, когда формулы находятся на других листах книги.
  • при изменении вычисляемого поименованного диапазона, нам не придется изменять формулу. Достаточно будет войти в диспетчер имен, и сменить адреса крайних ячеек диапазона.

Назначить имя ячейке или диапазону проще простого:

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

При необходимости изменить имя или поименованный диапазон необходимо во вкладке «Формулы» открыть «Диспетчер имен».

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

Изменение нескольких листов одновременно

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

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

Теперь, если вы на активном листе будете вносить какие-либо изменения, они один-в-один скопируются на всех выделенных листах.

Создание шаблона Excel

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

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

Расширение этого формата xlt (в версиях позднее 2007 года — xltx, но у меня старая версия работает стабильнее).

Для создания шаблона создаем новый документ Excel, и, при его сохранении, выбираем пункт «Другие форматы»:

В открывшемся окне находим пункт «Тип файла» и выбираем «Шаблон Excel 97-2003»:

Шаблон готов.