Вопросы и ответы по электронным таблицам

Автоматизация вычисления промежуточных итогов

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

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

Для решения этой типичной задачи подсчета промежуточных итогов существует несколько путей. Можно использовать сводные таблицы Excel, но эффективнее всего применить функцию подстановки значений в формулы (аналогичные функции есть во всех популярных табличных процессорах, появившихся после 1-2-3 версии 1A) в сочетании с функцией вычисления среднего в базе данных, которая в Excel называется DAVERAGE ("ДСРЗНАЧ"), а в 1-2-3 и Quattro Pro - @DAVG).

Таблица подстановки показывает, как отражаются изменения в ячейках таблицы на результатах вычисления по одной или нескольким формулам. Когда ячейка содержит критерий для привязки к базе данных (ограничивает диапазон используемых значений несколькими записями), результат вычислений каждый раз относится к новому набору данных. В Excel таблица подстановки представляет собой формулу, обрабатывающую массив и автоматически вычисляющую результат заново в случае изменения данных. В 1-2-3 и Quattro Pro для пересчета результата необходимо дать соответствующую команду.

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

1. Введите необходимые данные в диапазоне A1:D12 (в столбце A записаны имена игроков, в столбце B - названия соответствующих команд, столбцы C и D показывают, сколько очков набрал игрок в первый и второй раз).

2. Введите метки для ячейки ввода (F1) и для таблицы подстановки (F4:F7).

3. Вызовите меню Insert·Name· Define ("Вставка·Имя·Присвоить"), задайте диапазоны для критерия - F1:F2 и для базы данных - =offset($A$1,0,0,counta($A:$A),4).

4. В ячейке G4 введите формулу =daverage(database,"Score 1",criteria), а в ячейке H4 - =daverage(database,"Score 2",criteria). Сформатируйте ячейку G4 таким образом, чтобы результат вычисления формулы заменялся на метку "Score 1" ("Счет 1" - число очков, набранных в первый раз). Для этого выделите ячейку, дайте команду Format·Cells·Custom ("Формат·Ячейки·Все форматы"), введите строку "Score 1" (включая кавычки) и нажмите OK. Аналогично сформатируйте ячейку H4.

5. Создайте таблицу данных: выделите диапазон F4:H7, дайте команду Data·Table ("Данные·Таблица подстановки"), в поле Column Input Cell ("Подставлять значения по столбцам в") укажите F2, нажмите OK.

Формула в ячейке G4 вычисляет среднее значение данных в столбце C, соответствующих критерию в ячейке F2. Формула в H4 делает то же самое для столбца D. Таблица данных (F4:H7) подставляет значения из диапазона F5:F7 в ячейку F2. В результате диапазон G5:G7 содержит средние значения параметра Score 1 для каждой из трех команд, а диапазон H5:H7 - средние значения параметра Score 2 для этих же команд.

В Quattro Pro использовать имя динамически меняющегося диапазона нельзя, поэтому придется присвоить имя database диапазону A1..D12. В ячейке G4 введите формулу @davg(database,2,criteria). Число 2 соответствует смещению столбца C, содержащего значения параметра Score 1. В ячейке H4 введите формулу @davg(database,3,criteria). Здесь число 3 соответствует смещению столбца D, содержащего значения параметра Score 2. Сформатируйте ячейки так, чтобы они показывали метки Score 1 и Score 2: по очереди выделите ячейки щелчком правой кнопки мыши, дайте команду Block Properties ("Свойства блока"), перейдите к закладке Numeric Format ("Числовой формат"), выберите опцию User Defined ("Определяется пользователем") и в поле Formats Defined ("Определенные форматы") введите либо T"Score 1", либо T"Score 2" (включая одиночные кавычки). Чтобы создать таблицу данных, выделите диапазон F4:H7, дайте команду Tools·Numeric Tools·What-If ("Инструменты·Числовые инструменты·Что, если"). В поле Input Cell ("Рабочая ячейка") укажите F2, нажмите кнопку Generate ("Создать"), затем Close ("Закрыть").

В 1-2-3 следует присвоить имя database диапазону A1..D12, в ячейке G4 ввести формулу @davg(database,"Score 1",criteria), а в ячейке H4 - формулу @davg(database, "Score 2",criteria). Чтобы создать таблицу данных, нужно выделить диапазон F4..H7, дать команду Range·Analyze·What-If Table ("Диапазон·Анализ·Таблица "что, если""). Число переменных укажите равным 1, в поле Input Cell 1 ("Рабочая ячейка 1") укажите F2 и нажмите OK.

Перемещение курсора при вводе данных в Excel

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

Хороший вопрос. А ответ подсказал другой наш читатель, Чак Пейсек из г. Парсонс (шт. Канзас). Нужно вызвать меню Tools·Options ("Сервис·Параметры"), перейти к закладке Edit ("Правка") и снять пометку с опции Move Selection after Enter ("Переход к другой ячейке после ввода").

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

Вы с братом работаете с Windows 95, а значит, каждый из вас может настроить для себя индивидуальную конфигурацию. В Панели управления Windows 95 вызовите утилиту Passwords ("Пароли"), перейдите к закладке User Profiles ("Конфигурации"), включите опцию Users can customise their preferences ("Каждый пользователь может иметь свою систему настроек") и нажмите OK. После этого при запуске Windows система будет запрашивать у вас пароль и идентификатор пользователя. Пусть каждый из вас создаст собственную конфигурацию системы, в частности, по-своему настроит Excel с помощью меню Tools·Options. Поскольку эта программа специально предназначена для работы в Windows 95, то в каждой конфигурации произведенные настройки станут значениями по умолчанию.

Печать скрытых листов в 1-2-3

У меня есть документ 1-2-3, содержащий три листа. Чтобы не дать посторонним возможность менять формулы и данные, я сделал два листа скрытыми, да к тому же защитил файл паролем. Но оказалось, что распечатать весь документ я могу, только если сделаю скрытые листы видимыми. Нельзя ли распечатывать данные со скрытых листов?

В 1-2-3 нет стандартного средства, чтобы распечатать отчет, содержащийся на скрытых листах, поэтому я написал макрос (его можно найти в файле 5HSS2.WK4 в службе PC World Online), который решает эту задачу. Макрос снимает парольную защиту, раскрывает скрытые листы и распечатывает по одному диапазону с каждого из них. Затем он снова делает листы скрытыми и восстанавливает парольную защиту. Кроме того, в макросе есть средство обработки ошибок, обеспечивающее безопасность в случае сбоя. Сам макрос необходимо поместить на скрытом листе, чтобы никто не смог узнать пароль.

Чтобы увидеть макрос в действии, создайте документ из четырех листов. Двойным щелчком активизируйте лист D и назовите его Macros. Затем введите команды, как показано на рис. 2. С помощью меню Range·Name ("Диапазон·Имя") присвойте ячейке B1 имя p, а ячейке B9 - имя p_err. На листе B создайте имя firstrange, а на листе C - secondrange. Перейдите к листу A и выберите инструмент "кнопка", чтобы создать кнопку для вызова макроса. В диалоговом окне Assign to Button ("Связывание макроса с кнопкой"), в поле Assign macro from ("Местоположение макроса") выберите опцию Range ("Диапазон"). В списке Existing named ranges ("Именованные диапазоны") укажите имя p.

Для запуска макроса перейдите к листу A и нажмите кнопку или клавиши +P. Убедившись, что макрос работает, активизируйте его лист и в списке стилей выберите команду Hide ("Скрытый"), нажмите кнопку Sheet ("Лист"), а затем OK. Лист будет спрятан.

Вот как работают команды макроса.

{onerror p_err} указывает, что в случае сбоя (или нажатия пользователем клавиш +) во время выполнения макроса следует продолжить работу, начиная с команды в ячейке B9, чтобы снова спрятать конфиденциальные данные.

{file-unseal "password"} снимает парольную защиту с файла, позволяя макросу раскрыть спрятанные листы и распечатать их. Вместо password вы можете подставить свой собственный пароль.

{select A:A1} делает лист A текущим.

{windowsoff} делает выполнение оставшихся команд макроса незаметным для пользователя.

{show-sheets B:A1..C:A1} делает листы видимыми.

{select firstrange} выделяет первый диапазон с листа B.

{select-append secondrange} добавляет к выделению второй диапазон с листа C.

{print selection} выводит на печать выделенные диапазоны.

{hide-sheets B:A1..C:A1} снова делает листы B и C скрытыми.

{file-seal "password"} восстанавливает защиту файла, используя исходный пароль.

{select A:A1} показывает лист A.