Рабочая область Excel

Я почти все время работаю с пятью одними и теми же таблицами Excel. Этот набор приходится загружать много раз в день. Существует ли простая возможность (без написания макросов) загружать их все сразу?

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

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

Ни малейшего шанса опечаткам

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

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

Очень важно также и то, что Excel самостоятельно может проверить, что вводимое вами число является цифрой, которая будет участвовать в вычислениях (например суммировании) и из общего итога не «выпадет» ни одна цифра, как на приведенном внизу экране, где вместо цифры 15 в первой строке данных стоит последовательность латинской буквы I и цифры 5. И хотя на первый взгляд все нормально и I5 выглядит похоже на 15, но в итоговое суммирование это «псевдопятнадцать» не попало. Другая причина «выпадения» из вычислений — это установленный формат ячейки Текстовый, при этом число в ячейке считается простым текстом.

Как увидеть больше

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

Можно выделить все листы, если щелкнуть правой кнопкой мыши на ярлычке любого листа и выбрать во всплывающем меню пункт Выделить все листы. Если теперь в меню Файл выбрать Предварительный просмотр, то в него будут включены все выбранные листы так, как они будут печататься.

Графики текстом

Возможности построения графиков в Microsoft Excel вне конкуренции. Тем не менее, довольно часто нужно просто визуально сравнивать несколько контролируемых величин. Для этого вполне хватит и обычных «текстовых» средств Excel. Воспользуйтесь текстовой функцией ПОВТОР, которой передаются два параметра: текстовая строка и количество ее повторов. Если в качестве первого параметра использовать, например, латинский символ I, а в качестве второго — контролируемую цифру, то мы получим строку, длина которой (в символах) равна второму параметру функции. В результате получается картинка, аналогичная приведенной на рисунке. В качестве символа-повторителя можно также использовать, например, символы-значки шрифта Wingding.

Быстрая покраска

Обычно процесс создания таблицы в Excel происходит в три этапа: 1 — черновой набросок, 2 — его отладка, 3 — окончательное оформление. На первых двух этапах оформлением никто не занимается, и лишь значительно позже, уже на третьем этапе, обнаруживается необходимость представить абсолютно одинаково (цвет, формат цифр, выравнивание) содержимое нескольких ячеек.

Для этого сначала оформляют одну ячейку в качестве образца, а затем это оформление «размножается» с помощью инструмента Формат по образцу на другую ячейку. Одна незадача — после применения инструмента он деактивируется. Однако если при его выборе использовать двойной щелчок мыши, то инструмент останется активным до тех пор, пока вы не нажмете клавишу [Esc] или не щелкните на нем еще раз мышкой. Это позволяет одинаково «покрасить» много ячеек за один заход.

Аналогично работает двойной щелчок и в Word, и его можно использовать для массовой «покраски», например, заголовков.

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

Быстро вставим/удалим

Зачастую возникает необходимость вставки или удаления целых строк или столбцов. Обычно это делается через меню, либо сначала выделяется строка/столбец, а затем щелчком правой кнопки мыши вызывается контекстное меню, в котором выбирается соответствующий пункт меню — Добавить ячейки или Удалить.

Те же действия можно выполнить и не прибегая к помощи меню и мышки — с помощью специальных комбинаций клавиш. Для выделения строки нужно нажать [Ctrl]+[пробел], а столбца — [Shift]+[пробел]. После этого клавишами перемещения курсора можно расширить область выделения до нужных размеров и затем нажать [Ctrl]+[-] — для удаления выделенного диапазона или [Ctrl]+[+] — для добавления выделенного количества строк/столбцов.

Какие данные, такой и цвет

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

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

Спрячем данные

Спрятать от показа (и печати) ячейки с данными, которые используются для вспомогательных вычислений, можно либо установив цвет шрифта такой же, как и у заливки ячейки (обычно — белый), или использовав специальный формат для отображения данных — ;;; (три точки с запятой). Делается это через Формат|Ячейки|Число|(все форматы)|Тип:. При этом "невидимое" содержимое такой ячейки можно будет править в поле формул.

Русские и английские

При открытии файла Excel отображает имена встроенных функций на том языке, на котором работает сама программа (в английской версии суммирование — SYM, а в русской — СУММ). Если вам нужно узнать, как называется встроенная функция или макрос в другом языке, — откройте файл c:\Program Files\Microsoft Office\Office\1049\FUNCS.XLS. В нем вы найдете легко сортируемый список соответствий из 852 названий с возможностью поиска нужного имени.

Тащим на другой лист

Метод Drag & Drop настолько удобен, что многие им пользуются практически постоянно и очень огорчаются, когда какие-либо действия нельзя выполнить "перетаскиванием". Например, Excel не позволяет просто так перетаскивать ячейки с одного листа на другой — вместо этого она начинает прокрутку текущего листа, предполагая, что вам просто нужно перетащить ячейки за пределы отображаемой на экране части текущего листа. Если ячейки нужно перетащить на другой лист — нажмите во время перетаскивания клавишу [Alt]. При этом не будет прокрутки текущего листа и можно будет спокойно выбрать ярлычок листа, нужного для "сброса" ячеек.

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

Рабочая область Excel

Я почти все время работаю с пятью одними и теми же таблицами Excel. Этот набор приходится загружать по многу раз в день. Существует ли простая возможность (без написания макросов) загружать их все сразу?

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

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

Как увидеть больше

В Microsoft Exсel не предусмотрен просмотр книги целиком, т. е. всех ее листов. Предварительный просмотр показывает только текущий лист. Это неудобство можно легко обойти, если бы можно было выделить те листы, которые будут печататься. Сделать это можно, если щелкнуть левой клавишей мыши на ярлычке листа при нажатой клавише [Ctrl]. Это «добавит» его к текущему выделению листов. Выбранные вами листы отмечаются белым цветом.

Можно выделить все листы, если щелкнуть правой кнопкой мыши на ярлычке любого листа и выбрать во всплывающем меню пункт Выделить все листы. Если теперь в меню Файл выбрать Предварительный просмотр, то в него будут включены все выбранные листы так, как они будут печататься.

Учет выходных и праздничных дней

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

Предположим, что у нас есть две ячейки: В2, которая содержит дату начала работы, и В3 c ее общей продолжительностью. В В4 нам нужно вычислить дату, когда работа будет закончена. Для этого достаточно, чтобы ячейки В2 и В4 имели формат даты (меню Формат|Ячейки закладка Число поле Дата). В ячейке В4 записывается формула =В2+В3, и мы получаем искомый результат.

Для того чтобы исключить из расчетов нерабочие дни, нужно воспользоваться другой формулой =РАБДЕНЬ(Нач.дата, Дни, Выходные). Но для этого нужно сначала подключить надстройку Пакет анализа, в которой располагаются дополнительные функции. Делается это через меню Сервис|Надстройки|Пакет анализа. Если теперь тe же вычисления проделать с ячейками С2:С4 и формулой =РАБДЕНЬ(C2,C3,E2:E4), то результат, полученный в ячейке С4, будет отличаться от содержимого В4, поскольку в расчете уже участвуют не только обычные календарные выходные дни, но также и заданные пользователем в ячейках E2:E4 свои выходные дни. Когда Нач.дата содержит день окончания работ, а нужно определить дату начала — в поле Дни задают отрицательное значение.

Если же у вас есть дата начала и конца работ, а требуется определить количество рабочих дней между ними, нужно использовать другую формулу: =ЧИСТРАБДНИ(C2,C4,E2:E4), учитывающую как день начала работ, так и день их окончания.

Таскать ячейки можно по-разному

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

Если «взять» ячейку за ее рамку, то все ее содержимое можно перетащить на новое место. Если при этом будет нажата клавиша [Ctrl], то содержимое ячейки будет скопировано в «месте сброса». Если же перетаскивать ячейку (или выделенный диапазон) с нажатой правой кнопкой мыши, то перед тем как «сбросить» содержимое на новое место, Excel сначала переспросит, что именно вы намерены сбрасывать.

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

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

Формулы в Надписях

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

Чтобы сделать это, разместите сначала текстовый блок в нужном месте листа (кнопка Надпись на панели инструментов, которая появляется при нажатии кнопки Рисование на панели Стандартная) и придайте ей необходимые размеры. Затем щелкните мышкой внутри надписи, установив в нее курсор для ввода текста, и нажмите клавишу [F2] или просто щелкните мышкой в поле ввода формул. Теперь вы можете ввести формулу- ссылку на другую ячейку в таблице.

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

В Excel можно добавить свои особые функции

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

Например, можно подготовить в виде отдельной функции расчет процентов по кредиту. Допустим, что их величина изменяется в пределах от 10 % до 3 % в зависимости от суммы кредита. Программа на Visual Basic для такого расчета приведена на листинге справа. Чтобы ввести эту функцию в Excel, запустите встроенный редактор Visual Basic (Сервис|Макрос|Редактор Visual Basic) и в окне Проект — VBA Project щелкните правой кнопкой мыши на объекте VBAProject(Книга1) — вместо Книга1 должно быть имя вашего файла. Создайте новый программный модуль, выбрав в всплывающем меню пункт Вставить|Модуль. В результате появится окно редактора модулей, в которое нужно будет ввести текст функции Проценты.

Теперь вы можете использовать добавленную функцию в текущей книге Excel. Допустим, в ячейке A3 содержится значение суммы кредита. В ячейке B3 введите формулу =Проценты(A3). При расчете книги произойдет вызов функции Проценты, а в качестве параметра Сумма ей будет передано значение содержимого ячейки A3. Если в ячейке C3 записать =A3+B3, то в нее будет занесена сумма с процентами, которую нужно будет возвращать.

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

Однако создаваемые таким образом функции доступны для использования только в данной книге. Чтобы можно было использовать ваши функции не только в текущей, но и в любой другой книге Excel, выберите пункт Файл|Сохранить как, после чего укажите тип файла Надстройка Microsoft Excel (*.xla) и сохраните файл в каталоге XLStart (обычно это c:\Program Files\Microsoft Office\ Office\XLStart). Теперь при запуске Excel он будет автоматически загружаться, и подготовленные вами функции можно будет использовать в любых открытых книгах.

Function Проценты(Сумма)
Select Case Сумма
Case 0 To 500: Ставка = 10
Case 500 To 1000: Ставка = 7
Case 1000 To 10000: Ставка = 5
Case Else: Ставка = 3
End Select
Проценты = Сумма * Ставка / 100
End Function

Уникальный список

Если вы имеете дело с большими списками в Excel, в которых по несколько раз повторяются значения ячеек, иногда может оказаться полезным сформировать полный перечень всех встречающихся объектов без повторений. Например, если в книге выплат содержится информация о сотрудниках организации и их гонорарах за определенный промежуток времени, удобно получить общий список работников, получивших выплаты. Для облегчения этой процедуры воспользуйтесь пунктом меню Данные|Фильтр, предварительно выделив требуемый набор, и выберите в выпадающем меню пункт Расширенный фильтр. Установите переключатель группы Обработка в положение скопировать результат в другое место, задайте диапазон ячеек, в которые необходимо поместить результат, после чего отметьте опцию Только уникальные записи и нажмите на кнопку ОК.

В результате такой фильтрации в указанном месте появятся все уникальные объекты первоначального списка.

Кроме того, вы можете подсчитать количество вхождений каждого элемента в исходный список. Например, чтобы поместить общее число вхождений ячейки K7 в список ячеек с E7 по E25, необходимо в результирующую ячейку (например, L7), ввести формулу СЧЕТЕСЛИ(E7:E25,K7).

Быстрый запуск

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

Чтобы оптимизировать запуск Microsoft Excel, необходимо модифицировать параметры командной строки выполнения программы. Найдите на Рабочем столе или в меню Пуск ярлык для Excel, щелкните на нем правой кнопкой мыши и выберите пункт Свойства. В появившемся окне установите курсор на строку ввода напротив поля Объект на закладке Ярлык. Добавьте в конце строки /E, чтобы в результате она приняла приблизительно следующий вид: "С:\Program Files\Microsoft Office\ Office\EXCEL.EXE " /E. После сохранения изменений (кнопка Применить) запуск Excel будет производиться быстрее.

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

Группировка ячеек в Excel

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

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

Предположим, у нас есть таблица, в которой за названиями разделов следуют строки с описаниями входящих в раздел пунктов. Чтобы сгруппировать эти строки, выделите их нажатием на серые ячейки с номерами строк, а затем нажмите [Shift]+[Alt]+[а]. Excel добавит еще один столбец в области заголовков строк и объединит сгруппированные строки рамкой. В нижней части рамки находится кнопка со знаком «-». Щелкнув на ней мышью, можно скрыть сгруппированные строки, при этом знак «-» на кнопке превратится в «+». Аналогичным образом можно сгруппировать столбцы.

Для просмотра скрытых строк или столбцов необходимо нажать на кнопку «+». Ее присутствие ясно указывает на скрытые данные, что очень удобно при чтении документа. Функция группировки также доступна из меню Данные|Группа и структура.

Упрощение суммирования

При работе в Excel зачастую нужно выполнить суммирование значений всех ячеек, содержащихся в определенных строках или столбцах, причем нужно, чтобы при добавлении в пустые ячейки этих строк/ столбцов значений сумма автоматически перечитывалась, сгруппируйте строки/столбцы и присвойте им собственное имя. Для этого выделите необходимые строки/столбцы, удерживая нажатой кнопку Ctrl, и через меню Вставка|Имя|Присвоить введите имя группы, например One, нажмите кнопку Добавить а затем ОК. Далее в любой свободной ячейке документа, не входящей в определенную только что группу, введите формулу =СУММ(One).

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

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

Только формулы

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

Тем не менее, существует простой способ облегчить задачу контроля над вычислениями рабочего проекта. Для этого в Excel предусмотрена возможность переключения режимов работы с формулами и работы с результатами вычислений — достаточно нажимать на клавиатуре комбинацию клавиш [Ctrl]+[~]. Это же сочетание используется для обратного перехода.

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

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

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

Формат всех листов

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

Для выделения всех листов открытой книги достаточно щелкнуть правой кнопкой мыши на имени любого листа и во всплывающем меню выбрать пункт Выделить все листы. Если форматированию подлежат только избранные листы книги, отметить их можно щелчком левой кнопкой мыши, удерживая нажатой клавишу [Ctrl] или [Shift]. Чтобы снять маркировку со сгруппированных листов, достаточно щекнуть на любом из них правой кнопкой мыши и воспользоваться меню Разгруппировать листы.

Когда группировка листов подготовлена, можно приступать к форматированию всей группы. Например, можно изменить ширину столбцов. Для этого достаточно щелкнуть мышью на кнопке Выделить все ячейки (крайняя левая вверху), в меню Формат|Столбец|Ширина указать новое значение ширины и щелкнуть на кнопке ОК.

Запуск в безопасном режиме

Пользовательские панели инструментов, обилие автоматически загружающихся файлов в папке загрузки Excel, программные надстройки и различные «махинации» с ключами в системном реестре могут однажды привести к тому, что программа вообще перестанет запускаться. И что в этом случае делать, если необходимо срочно открыть важную таблицу? Не прибегать же к переустановке всего Microsoft Office.

Оказывается, не только операционные системы семейства Windows обладают возможностью загрузки в так называемом «безопасном режиме» с целью диагностики и локализации неисправностей. Эта возможность присутствует и в Excel.

Чтобы воспользоваться ею, необходимо произвести старт программы с ключом /s. Как вариант, выясните, в какую папку инсталлирован Microsoft Office, а затем воспользуйтесь меню Пуск|Выполнить, вставив в поле ввода строку «C:\Program Files\Microsoft Office\Office\excel.exe» /s (необходимо подставить свой путь к исполняемому файлу excel.exe). В результате будет загружена «чистая» конфигурация, соответствующая первому запуску Excel на вашем ПК.

Преобразование единиц измерения

Чтобы упростить задачу преобразования различных единиц измерения в вычислениях Excel, можно воспользоваться функцией ПРЕОБР(число;старые_единицы; новые_единицы). Для ее активизации используйте меню Сервис|Надстройки и в списке надстроек выберите пункт Пакет анализа.

В результате добавления этой надстройки новая функция появится в группе Инженерные стандартного Мастера функций. Полный список сокращений единиц измерения можно найти в справке Excel.

Обратно