Большинство советов в этой рубрике верно для любых версий Excel, начиная, по крайней мере, с 5.0 для Windows. А в тех случаях, когда это не так, в советах указывается явным образом номер версии Excel, для которой он предназначен. Однако для более поздних версий такие советы, как правило, тоже будут верны. Таким образом, если в совете упоминается Excel97 (он же Excel8.0), то этот совет не будет работать в Excel95 (он же Excel7.0) и более ранних версиях, но, скорее всего, будет работать в Excel2000 (он же Excel9.0) и более поздних версиях. Названия элементов интерфейса Excel (пунктов меню, вкладок, опций и т.д.) приводятся обычно, как они указаны в русской версии Excel97, в других версиях эти названия могут немного отличаться, но, я думаю, вы без труда найдёте нужные.
(37) Когда вы выполняете операции поиска и замены в Excel, то очень просто случайно щёлкнуть по кнопке "Найти следующий" ("Find Next") лишний раз или несколько. Если такое вдруг произошло, удерживайте клавишу <Shift> и щёлкайте по кнопке "Найти следующий" до тех пор, пока вы не вернётесь туда, куда нужно.
(25) После того, как в ячейке набран текст или цифры, мы жмём <Enter> и курсор сдвигается на нижнюю ячейку. Это не всегда удобно — если нужно заполнять строки таблицы, например. В этом случае удобнее, чтобы курсор сдвигался вправо (или влево, если вы заполняете строки справа налево :-)
Всё это подвластно вашему контролю. В меню "Сервис" – "Параметры" есть вкладка "Правка", где как раз находится пункт, отвечающий за то, в направлении какой ячейки будет переходить курсор после ввода. Можно выбирать из четырёх вариантов: "вниз" (по умолчанию), "вверх", "вправо" или "влево".
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 4.
(30) Достаточно часто требуется ввести в ячейку текст, содержащий несколько абзацев. Однако при нажатии клавиши <Enter> осуществляется переход в следующую ячейку; при импорте текста, содержащего несколько абзацев, каждый абзац помещается в отдельную ячейку. Для ввода в одну ячейку нескольких абзацев можно пользоваться комбинацией клавиш <Alt>+<Enter>. При вводе русского текста должна использоваться левая клавиша <Alt>, для английского текста допускается использование как левой, так и правой клавиши.
Статья R10286 сборника технических статей Microsoft
(30) При записи в ячейку документа Excel 97 числа, содержащего более 15 знаков, последние цифры заменяются нулями. Данная проблема возникает из-за того, что в Excel 97 максимальная точность представления числа 15 знаков. При записи в ячейку двадцатизначного числа происходит округление до 15 знаков и представление числа в экспоненциальной форме. Увеличить точность представления чисел в Excel 97 нельзя. Однако если не предполагается участие этих чисел в математических операциях, то возможно использование текстового формата, для их хранения. Такое решение подходит, например, для хранения номеров банковских счетов.
Статья R10561 сборника технических статей Microsoft
(30) MS Excel 2000 производит автоматическую замену на гиперссылки текста, начинающегося со следующих обозначений сетевых ресурсов:
http://
www.
ftp://
mailto:
file://
news:
user@company
Если надо отменить данную автоматическую замену, то используйте в качестве первого символа текста, вводимого в ячейку, символ апострофа ('). В этом случае, Excel воспринимает содержимое ячейки как данные текстового типа и не производит их преобразование. Символ апострофа на экране и на печати не отображается.
Статья R10960 сборника технических статей Microsoft
(25) Чтобы перетащить ячейку или группу ячеек, нужно её (их) выделить и за ободочек перетащить в нужное место. Нажатая при этом клавиша <Ctrl> позволяет копировать ячейки, а клавиша <Alt> позволяет перенести ячейку на самое крайнее на экране поле (скроллинг блокируется). Кроме того, с нажатым <Alt> можно перенести ячейку на другой лист документа — для этого нужно затащить ячейку на название листа и выбрать на появившемся новом листе нужную ячейку для вставки.
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 22.
(25) Чрезвычайно эстетичный способ одновременного заполнения множества ячеек Excel одной и той же информацией. Нужно сначала их все выделить (удерживайте <Ctrl> для выделения отдельных групп ячеек), затем, не снимая выделение, ввести нужную информацию (она появится в одной из ячеек) и, по-прежнему не убирая выделение, нажать <Ctrl>+<Enter>. Красота, да и только.
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 21.
(25) Для того, чтобы проверить грамматику одновременно во всех листах документа или заполнить определённые ячейки всех листов одной и той же информацией, нужно выделить все листы, щёлкнув правой кнопкой мыши по их заголовкам внизу экрана и выбрав соответствующий пункт меню. После группировки листов, что бы вы ни сделали с любой ячейкой на любом листе, — то же самое произойдёт с одноимёнными ячейками всех остальных листов документа (после окончания операций не забудьте разгруппировать листы, просто нажав на заголовок какого-нибудь из них).
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 22.
(33) Если мы выделим ячейки, то увидим чёрный квадратик в правом нижнем углу выделения (при выделении целиком столбца квадратик будет в правом верхнем углу, а при выделении строки — в левом нижнем), которым очень удобно пользоваться для целого ряда операций. Потянув мышкой за этот квадратик, можно заполнить смежные ячейки соответствующими значениями. Причём, если в выделенных ячейках содержался текст, то он будет копироваться в соответствующие смежные ячейки, а если был ряд чисел, то он будет продолжен с тем же шагом (например, если в выделенных ячейках было 1,3,5, то при движении мышкой вниз или вправо получим 7,9,11..., а при движении вверх или влево -1,-3,-5...), либо, если шаг ряда не постоянный, он будет продолжен из расчёта линейной аппроксимации (1,3,8 — вниз или вправо: 11,14.5,18..., вверх или влево: -3,-6.5,-10...). Если вам надо, чтобы ряд чисел просто копировался, то удерживайте нажатой клавишу <Ctrl> (отпускайте её после кнопки мыши). Если же удерживать клавишу <Shift>, то можно вставлять ячейки рядом с выделенными или удалять выделенные, захватывая их самих.
(33) Если была выделена только одна ячейка и в ней содержалось число, то поведение Excel обратное: без удержания клавиши <Ctrl> — простое копирование, с <Ctrl> — увеличение (уменьшение) на 1 значения в каждой следующей ячейке.
(37) Когда в одном столбце вводятся какие-то значения аргумента, а в других столбцах вводятся формулы и "размножаются" для всех введённых аргументов, то в Excel 2000/2002 наблюдается такая особенность: если ввести ещё одно значение аргумента под последним значением, то значения формул вычислятся автоматически, как если бы мы их дальше "размножили". Для этого эффекта количество заполненных строк в столбцах должно быть не менее 4. Это может быть удобно, если вы забыли или решили ввести ещё одно значение аргумента и подсчитать для него значения функций.
(33) Когда вы работаете в Excel, данные образуют смысловые группы, напоминающие прямоугольники. Обычно, сверху/снизу, справа/слева они отделены пустыми столбцами и строками от других данных. Эти прямоугольники образуют текущую область. Чтобы выделить эту текущую область, поставьте курсор в любую ячейку в этой области и нажмите <Ctrl>+<Shift>+<8>. Одно нажатие и нужная область выделена!
Степа (mesmit AT mail.ru) mso.by.ru
(34) При нажатии <Ctrl>+<Shift>+<стрелка> будут выделены ячейки, начиная от текущей, в соответствующем стрелке направлении по следующим правилам:
– если ячейка, соседняя в данном направлении с текущей, является непустой, то будут выделены все непустые ячейки до первой пустой в этом направлении;
– если соседняя ячейка пустая, то вместе с текущей ячейкой будут выделены все пустые ячейки до первой непустой и эта непустая ячейка, а если непустых не встретится, то выделение будет до конца строки или столбца.
При нажатии <Ctrl>+<Shift>+<End> будет выделена прямоугольная область, одной вершиной которой является текущая ячейка, а противоположной по диагонали вершиной является ячейка на пересечении последней непустой строки и последнего непустого столбца. Таким образом, мы можем, например, поставив курсор в самую первую ячейку, нажатием трёх клавиш выделить область, захватывающую все непустые ячейки на листе.
При нажатии <Ctrl>+<Shift>+<Home> выделяется прямоугольная область от текущей ячейки до начала листа.
(37) Когда не все ячейки подряд в строках и столбцах заполнены, то удобно перемещаться по ним с помощью клавиш <Ctrl>+<стрелка>. Тогда переход осуществляется по заполненным ячейкам, перед (или после) которых имеются пустые ячейки. Если же в направлении нажатой стрелки все ячейки пустые или все заполненные, то переход произойдёт на самую первую или последнюю (в этой строке или столбце).
(29) Иногда возникает необходимость повернуть таблицу на 90 градусов. Т.е. нужно, чтобы строки стали столбцами, а столбцы — строками. Не делать же это и в самом деле вручную! Тем более что таблица может быть совсем и не мелкой. И в этом случае Excel способен быстро решить вашу проблему. Выделите таблицу и скопируйте её. Теперь воспользуйтесь пунктом меню специальная вставка и поставьте флажок "транспонировать". Вот и всё.
Алексей Плащинский (a-pl AT mail.ru) // Компьютерные вести. 2000. № 30.
(43) Одной из наиболее часто используемых является функция автосуммирования, при вставке которой в ячейку получаем сумму числовых значений ячеек, расположенных над данной ячейкой (вверх с первой непустой до первой пустой) или слева от неё (если выше непустых ячеек вообще нет или выше сразу следует хотя бы одна пустая ячейка, зато слева соседняя является непустой). Обычно для вставки автосуммы щёлкают мышкой по её значку на панели инструментов, но быстрее нажать клавиши <Alt>+<=>, а затем <Enter> для подтверждения.
(29) Чтобы увидеть введённые формулы, не нужно бегать курсором по ячейкам и смотреть на содержимое каждой. Есть более простой способ вывести все формулы на экран — сочетание клавиш <Ctrl>+<`>.
Алексей Плащинский (a-pl AT mail.ru) // Компьютерные вести. 2000. № 30.
(29) Для удобного ввода функций, особенно тех, где много аргументов, в Excel 97 можно сразу после набора имени функции нажать комбинацию клавиш <Ctrl>+<Shift>+<A>. После этого в строке ввода формулы появятся наименования всех аргументов данной функции c указанием их типа: число, строка и т.д. Например, для суммирования диапазона ячеек нужно ввести "=СУММ", а после нажатия вышеуказанной комбинации клавиш название формулы преобразуется в строку вида: "=СУММ(число1;число2;...)". Теперь достаточно отредактировать полученную формулу, заменив наименования аргументов на их требуемые значения.
Юрий Левин (yl AT mail.bn.by) // Компьютерные вести. 2000. № 46.
(25) Чтобы подогнать ширину столбца под содержимое ячеек, достаточно дважды щёлкнуть по правой границе заголовка нужного столбца. Эту операцию можно совершить с неограниченным количеством столбцов одновременно, выделив их все.
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 21.
(25) При работе с большими таблицами иногда чувствуешь себя заблудившимся в документе: заголовки колонок остались далеко сверху, а заголовки строк — далеко в стороне (как бы "за кадром"). К счастью, в Excel есть функция "заморозки" заголовков. Для её активизации нужно выделить ячейку и выбрать пункт "Закрепить области" в меню "Окно". После этого слева и сверху от выделенной ячейки все строки и столбцы будут "заморожены" — они будут постоянно присутствовать на экране, в то время как по остальным ячейкам можно свободно осуществлять навигацию.
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 22.
(39) Иногда бывает нужно получить значение формулы, которое бы в дальнейшем автоматически не пересчитывалось. Например, если мы хотим вставить в документ дату и время его создания при помощи функции ТДАТА. Чтобы функция не пересчитывалась при общем пересчёте листа (в том числе в момент его открытия), можно сделать следующее: скопировать эту ячейку и вставить обратно с помощью специальной вставки, указав, что вставлять будем только значение.
(25) Если вы хотите, чтобы некоторые ячейки Excel не печатались, а на их месте была девственная пустота — это очень просто сделать. Не нужно использовать инструмент "Скрыть" из меню "Окно", так как в этом случае легко самим забыть, что именно вы скрыли. Попробуйте просто сделать шрифт в этих ячейках белым: "Формат" – "Ячейки...", затем на вкладке "Шрифт" в списке "Цвет" выберите "Белый" — в результате эти ячейки на печати абсолютно не будут просматриваться.
Анатолий Ализар (alizar AT softhome.net) // Компьютерные вести. 2000. № 19.
(9) При задании форматов ячеек можно учесть, в частности, что окончания слов в единственном и множественных числах различны. Условие в формате задаётся с помощью квадратных скобок. Например, формат
[>=5] 0" дней"; [>=2] 0" дня"; 0" день"
означает:
– если число в ячейке больше или равно 5, печатать число и текст "дней";
– если число меньше 5, но больше или равно 2, печатать число и текст "дня";
– в остальных случаях печатать число и текст "день".
Николь Н., Альбрехт Р. Электронные таблицы Excel 5.0. М.: ЭКОМ, 1996.
(26) Если у вас имеются таблицы, например, платежей в разные дни в рублях, а вам требуется пересчитывать их в доллары по курсу на день платежа, то вы можете воспользоваться следующим подходом. Допустим, в столбце A у вас расположены даты платежей, в столбцах B и D — платежи в рублях за данный день. А в столбцах C и E вы хотите поместить соответствующие величины в долларах по курсу на день платежа. Тогда вы можете создать лист под названием Курс, в столбце A задать даты (с первой строки и подряд без пропусков!), начиная с интересующей (например, с 1.01.99), в столбце B задать курс. И теперь вам будет достаточно ввести в ячейке C1 формулу
=B1/ИНДЕКС(Курс!$B:$B;$A1-ДАТАЗНАЧ("31.12.98");1)
А затем копировать её в те ячейки, где должны быть показаны платежи в долларах. Например, при копировании этой формулы в ячейку E3 в формуле автоматически будет преобразовано B1 в D3 и $A1 в $A3. В результате платёж в рублях из ячейки D3 будет поделён на курс, взятый с листа Курс из столбца B и строки с номером, определённым как разница между датой платежа из ячейки A3 и 31.12.98, что и соответствует нужной дате, если курсы заданы с 01.01.99.
Второй и лучший вариант решения данной задачи — использовать формулу
=B1/ПРОСМОТР($A1;Курс!$A:$A;Курс!$B:$B)
Тогда даты с курсами могут иметь пропуски, так как если функция ПРОСМОТР не может найти заданного значения (дата из ячейки $A1) в ячейках поиска (столбец A с датами листа Курс), то используется максимальное значение из меньших, чем искомое значение.
В английской версии Excel соответствующие функции называются INDEX, DATAVALUE и LOOKUP.
Данный подход легко использовать при решении аналогичных задач.
(29) Если вам приходится создавать однотипные формы документов, то следует один раз создать шаблон документа, а потом уже создавать новые книги Excel на основе данного шаблона. Для этого надо сделать образец книги, в меню "Файл" выбрать команду "Сохранить как", в списке "Тип файла" выбрать "Шаблон (*.xlt)", указать желаемое имя шаблона и нажать кнопку "Сохранить". После этого можно будет открывать этот шаблон, как обычную книгу, заполнять необходимыми данными, а сохраняться новая книга будет как файл с расширением .xls, не изменяя тем самым ваш шаблон.
Если вы хотите, чтобы ваш шаблон использовался для каждого нового документа, то следует файлу шаблона дать имя Книга.xlt и поместить в подкаталог XLStart каталога, где расположен Excel. Такой шаблон книги называется стандартным. Можно также сделать стандартный шаблон листа, который будет определять вид создаваемых новых листов. Для этого надо проделать всё аналогично, только образец должен содержать лишь один лист и имя необходимо дать Лист.xlt.
В английской версии Excel имена стандартных книги и листа — Book.xlt и Sheet.xlt.
(34) Когда вы сохраняете книгу в Excel, то личные данные, такие как имя пользователя, название организации, сохраняются вместе с книгой. Если вы не желаете, чтобы они фигурировали там, то в Excel 2002 можно выполнить следующие действия. В меню "Сервис" выберите пункт "Параметры", в открывшемся окне перейдите на вкладку "Безопасность" и установите флажок "удалять личные сведения из файла при его сохранении".
