F.A.Q. - Ланиста

Довольно часто при составлении таблиц встречается такая проблема:

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

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

Выделите всю таблицу вместе с заголовками строк и столбцов и жмите «копировать».
Затем щелкните мышью вне копируемого диапазона и выполните команды «Правка - Специальная вставка» ( В Excel - 2007 для вызова «специальной вставки» нажмите на текст «вставить» чуть ниже кнопки «вставить» и выберите соответствующий пункт)
В появившемся окне установите птичку «транспонировать» и нажмите ОК.

Вам останется только удалить первоначальный результат.

Скрываем листы

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

Делается это так.

Сначала удерживая клавишу CTRL выделите листы, которые надо скрыть. Затем выбираем «Формат – Лист – Скрыть»

Что бы отобразить лист «Формат – Лист – Отобразить»

В Excel-2007 и того проще. Щелкаем правой кнопкой мыши по названию листа и выбираем «Скрыть».

Защита не для всех

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

Для этого проделайте следующее:

Выделите ячейки, которые можно будет редактировать и войдите в формат ячеек;
На вкладке «защита» снимите птичку с позиции «Защищаемая ячейка»;
А теперь защитите весь лист «Сервис – Защита - Защитить лист» и устанавливайте пароль (в Excel - 2007 вкладка «Рецензирование» -«Защитить лист»).

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

Сквозная шапка

Данная функция пригодится тем, кто пользуется многостраничными документами. С ее помощью можно расселить шапку на все страницы документа. Причем если в дальнейшем будут удалены, или добавлены строки шапка не сползёт, а останется на своём законном месте.

Это незаменимая функция при работы с очень длинными таблицами.

Делается это так: «Файл» - «Параметры страницы» - закладка «Лист». Здесь выбираем «сквозные строки», или «сквозные столбцы».

Теперь при печати выделенный вами диапазон будет виден на всех страницах.

В Excel-2007 выбираем вкладку "Разметка страницы" и нажимаем на "параметры страницы" ( в нижней части второго сектор слева). А дальше как и в версии 2003 года.

Невидимый апостроф

Попробуйте ввести в Excel число 0005. Программа его не поймет и автоматически преобразует в 5. Ведь действительно числа 0005 в природе не существует.

Выйти из подобной ситуации можно, преобразовав формат ячейки из «общего» в «текстовый». Но есть способ быстрее и удобнее.

Необходимо перед числом поставить значок апострофа ‘ Да именно того, который делит клавишу «Э», в английской раскладке клавиатуре.

Символ будет виден только в строке формул. В самой рабочей области и при печати его видно не будет.

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

Сегодняшняя дата

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

Для это вводим в любую ячейку следующую формулу

=СЕГОДНЯ()

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

=СЕГОДНЯ()+1 - завтра

=СЕГОДНЯ()-1 - вчера

Существует похожая формула для ввода одновременно и даты и времени.

=ТДАТА

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

Ну а если вам не надо, чтобы дата пересматривалась, а вы хотите чтобы в ячейке вечно хранилась дата ввода данных, то нажимайте CTRL+Ж.

А для ввода текущего времени используйте CTRL+SHIFT+Ж

Для пользователей Excel - 2007 и некоторых других версий офиса раскладка следующая:

Ctrl+Shift+4 - Для вставки даты

Ctrl+Shift+6 - Для вставки времени

Функции на строке состояния

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

Поэтому в Excel предусмотрено суммирование ячеек только на основе их выделения. Результат появляется в строке состояния программе в правом углу ( Напомню, что строка состояния расположена в самом низу программы)

Сразу же после того, как вы выделите ячейки с числами, в строке состояния появится надпись «СУММА=……», где ….. и есть результат складывания чисел.

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

Причем не имеет значения сколько столбцов или строк выделено. А также имеется ли среди них пустые ячейки.

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

Также доступны функции: «Среднее», «Количество значений», «Количество чисел», «Максимум», «Минимум». Установите птичку рядом с функции и она поселится в данном секторе строки состояния.

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

Быстрый ввод

Как сделать так, чтобы вводимые в одну ячейку данные сразу копировались на весь столбец, или какой-то диапазон?

У экселя есть ответ и на этот вопрос.

Сначала надо выделить диапазон ячеек. В любую из ячеек ввести данные. А затем удерживая клавишу CTRL, нажать ENTER.

Выбор из списка

При вводе какого-либо текста эксель анализирует расположенные выше ячейки и если подобное слово встречается выше предлагает внести его без набора букв. Это очень удобно.

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

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

Или выпадающий список можно вызвать ALT+"стрелка вниз".

А если вы хотите повторить находящиеся в соседней верхней ячейке значение, то используйте CTRL+d. При этом вставится не только содержимое ячейки, но и ее формат.

Диаграмма за 5 секунд

Существует самый простой способ создания диаграммы. Для этого надо лишь выделить таблицу вместе с наименованием столбцов и строк и нажать кнопку F11.

Excel самостоятельно создаст гистограмму за пару секунд (если вы конечно не меняли параметры диаграмм по умолчанию). Затем можно отредактировать такую диаграмму точно также, как и сделанную мастером диаграмм.

Домашний документ

В Internet Explorer, да и других браузерах есть такое понятие «домашняя страница». Т.е. домашняя интернет страница загружается вместе с запуском браузера. Подобная возможность есть и у пользователей Excel.

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

Для Excel 2003 путь такой:

С:\Program files\Microsoft Office\Office11\XLStart\

А у версии 2007 года:

С:\Program files\Microsoft Office\Office12\XLStart\

Т.е в зависимости от версии меняется цифровой код папки «office».

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

Сохранить рабочую область в Excel-2007 можно на вкладке «вид» в секторе «окно».

Именованный диапазон

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

Но можно сделать всё проще. Для этого выделите ячейки, которым вы хотите присвоить название и в окне «имя» (оно расположено слева от строки формул) впишите название. И нажимайте ENTER.

Все! Именованный диапазон создан.

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

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

Расширенная прогрессия

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

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

После этого появится контекстное меню, где надо выбрать последний пункт «прогрессия»

А в появившемся окне можно выбрать вид прогрессии (арифметическая, геометрическая) шаг, предельное значение и т.д.

Выравниваем автофигуры

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

А оказывается есть простое решение. Для этого надо рисовать автофигуру при нажатой клавише ALT.

Вот и всё. И быстро, и красиво, и нервишки не потрепали.

Таким же макаром можно привязать к границам рисунки и другие объекты.

Выделение формул

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

Для такого выделения, нажимаем F5 в появившемся окне –нажимаем «выделить», а в следующем окне галочку устанавливаем на нужный пункт.

Вобщем очень полезная вещица, жаль что создатели экселя закопали её так глубоко.

Объёмная ячейка

Этот секрет не даст вам практической пользы. Но он позволит сделать ваш документ привлекательным и профессиональным.

Сейчас мы при помощи игры с цветами добьёмся эффекта объёмности ячейки. Итак, заходим в формат ячейки на вкладку «границы». Выбираем вторую по толщине линию, цвет указываем черный и рисуем нижнюю и правую границы ячейки.

Затем меняем цвет на белый и рисуем верхнюю и левую границы. Теперь перескакиваем на вкладку «вид» и устанавливаем серый шрифт.

Нажимаем «Ок» и любуемся результатом.

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

Настройка панелей инструментов

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

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

Но панель можно пополнить другими командами. Для этого щелкните правой кнопкой мыши по любой панели инструментов и выберите «настройка». Затем открывайте вкладку «команды» и выбирайте самые частоиспользуемые команды. Для удобства они разбиты на категории.

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

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

Назначить макрос этой кнопке можно дважды кликнув по ней.

Прячем макрос

Можно засекретить пребывание макроса в списке макросов. Для этого надо открыть книгу содержащую макрос. Переходим «Сервис – Макрос – Макросы»( в Excel-2007 вкладка "вид" - сектор "макросы"). Затем выбираем макрос, который надо скрыть, и нажимаем на «изменить».

Вот мы и попали в режим Visual Basic. Но не пугайтесь, вам нужно будет добавить всего одно слово в код.

Этим нужным словом будет «Private». Его нужно вписать прямо перед названием макроса в первой строчке открывшегося окна. Если макрос имел название «макрос1», то теперь первая строчка будет выглядеть так: «Private Sub Макрос1()».

Теперь нажимаем на кнопку «сохранить» и переходим в обычный режим эксель. И видим макрос чудесным образом исчез из списка. Однако он вполне боеспособен. Это можно проверить при помощи «горячих клавиш» для запуска макроса.

Что бы вернуть макрос на место нажмите ALT+F11. И в правом верхнем окне дважды нажмите по Module1, если у вас только один макрос, или Module с порядковым номером вашего макроса и удалите слово Private.

Извлечение корня

Для того, чтобы возвести число в любую степень в экселе имеется значок ^, (он располагается на одной кнопке с шестёркой).

Таким образом, возведение числа 7 в третью степень выглядит так:

=17^3

Для извлечения корня мы будем использовать тот же самый приём, так как корень n-ной степени соответствует возведению числа в 1/n степень.

Т.е квадратный корень из 49 вычисляется так

=49^(1/2) или 49^0,5

а корень третьей степени из 8

=8^(1/3)

Подобные операции можно проделать и с помощью функций:

=КОРЕНЬ(аргумент1), где аргумент1 - число из которого извлекается квадратный корень;
=СТЕПЕНЬ (арумент1;аргумент2), где аргумент1 - число возводимое в степень, а аргумент2 - степень

Что бы в тексте вписать знак степени надо выделить знак степени и в формате ячейки изменить шрифт на "надстрочный". А для записи скажем формулы Н20 выделим двойку и указываем для неё "подстрочный шрифт" в формате ячейки.

Размещаем данные на формате А4

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

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

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

А что бы изменить масштаб, который в конечном итоге повлияет на размер документа при печати необходимо зайти в раздел меню «файл» и выбрать раздел «параметры страницы». Далее во вкладке «страница» имеется раздел «масштаб». Первоначально там установлена такая запись: «установить 100% от натуральной величины».

Но вы же можете изменять проценты по своему усмотрению. Соответственно при проценте меньше 100, размер документа будет уменьшен, а больше 100 – увеличен.

Конечно, не стоит слишком уж уменьшать масштаб, все это выльется в нечитаемость текста. Оптимальный размер более 70%.

Также вы можете подогнать текст под определенное количество листов. При помощи «разместить не более чем на...» в этом случае масштаб будет подобран автоматически.

В Excel - 2007 «параметры страницы» находится на вкладке «разметка страницы» во втором секторе справа.

Римские цифры

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

Для ввода римских цифр существует специальная формула.

=РИМСКОЕ(….), где … нужное число.

Единственное неудобство заключается в том, что для этой формулы требуется свободная ячейка, без текста.

Ввод дробных чисел

Пользуетесь ли вы вводом дробей?

А доставляет ли вам удовольствие этот процесс?

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

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

Сейчас я вам покажу, как заранее оповестить excel, что сейчас в него будет введена дробь, даже при «стандартном» формате ячейки. Для этого перед вводом дроби впишите 0 (ноль), поставьте пробел и «вбивайте» дробь. После этого ноль исчезнет, а эксел автоматически изменит формат ячейки на «дробный».

Выделение диапазона

Существует несколько способов быстро выделить диапазон:

При помощи клавиши Shift Сначала установите активную ячейку на правый верхний угол диапазона, который надо выделить. Затем удерживая Shift нажмите на нижний левый угол диапазона и произойдет выделение. Аналогичным способом работает и кнопка F8. Но ее удерживать не надо. Установите активную ячейку, нажмите на F8 и нажимайте на крайнюю нижнюю ячейку.
При помощи клавиши CTRL Данная клавиша позволяет выделить несвязанные диапазоны. Надо во время выделения всё время держать эту кнопку, а мышью выделять объекты.
При помощи сочетания клавиш CTRL+SHIFT+* можно мгновенно выделить диапазон в котором в данный момент находится активная ячейка.

Концом диапазона excel будет считать пустую строку и столбец. Этот способ выделения очень удобен, если на листе имеются несколько таблиц.

Разбивка текста

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

Операцию по расчленению столбца надо начать с того, что, выделив нужный столбец, пройти «данные» - «текст по столбцам…». ( В Excel-2007 вкладка «данные» сектор «работа с данными»)

В появившемся окне укажите формат данных. Как правило, это «с разделителями» ведь наверняка между данными которые вы хотите разделить имеется пробел, или знак препинания. Нажимаем «далее».

На втором шаге выбираем, что именно находится между словами, обычно, как я уже замечал это пробел. Ставим галочку и жмём далее.

На третьем шаге, если не надо указать формат данных столбца, нажимаем «готово».

А чтобы проделать обратную операцию надо воспользоваться знаком склейки -&. К примеру в ячейке А1 находится "Иванов", в B1 - "Иван", в C1 -"Иванович". В ячейке А4 составим вот такую формулу:

=A1&B1&C1

А если хотим, что бы между фрагментами были пробелы, то формулу запишем таким вот образом:

=A1& " "&B1&" "&C1

Ограничение ввода

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

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

Для этого выделяем ячейки ( можно целиком строку(и), столбец(ы)), в меню выбираем «Данные – Проверка».( В Excel-2007 вкладка «данные» сектор «работа с данными».) В появившемся окне в графе «тип данных» выбираем «длина текста», в графе «значение» - «равно», а «длину» указываем –«6» (для индекса).

Во вкладке «сообщение об ошибке» можно дать индивидуальное название и сообщение об этой ошибке. И нажимаем ОК.

Теперь при попытке ввести в указанные вами ячейки значения не равные по длине 6 символам, появится сообщение об ошибке. Причём ввести данные другого размера не получится. Если всё же ограничение по длине у вас не принципиально, то во вкладке «сообщение об ошибке» выберите пункт «предупреждение», а не «остановка» как по умолчанию.

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

Если данные уже внесены, выделить среди них недействительные можно следующим образом:

Выделяем нужный диапазон и заходим «Данные – Проверка», выбрав нужное нам условие (Например, значения должны быть больше 130)
Выбираем «Сервис – Зависимости формул – Панель зависимости»
В появившейся панельке выбираем «Обвести неверные данные»

( В Excel-2007 вместо двух последних пунктов выбираем «Обвести неверные данные», которая находится вместе с проверкой на вкладке «данные» сектор «работа с данными».)

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

Управление форматом

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

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

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

Теперь вставляемая ячейка примет формат её новых соседей.

Условное форматирование

Довольно часто числа в формулах принимают недопустимые или критические значения. Например, количество предметов получается отрицательным.

В подобных случаях можно привлечь внимание к подобным числам. Для этого имеется очень полезная функция «условное форматирование».

Найти её можно в разделе меню «формат».

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

Например, в бухгалтерском балансе актив и пассив должны быть одинаковыми. Поэтому для контроля ставиться формула. Актив минус пассив. Естественно, что число должно быть равно нулю, иначе в балансе ошибка.

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

В Excel - 2007 «условное форматирование» находится на вкладке «главная», сектор «стили». Выбираем «условное форматирование - правила выделения ячеек - .... »

Копирование и перемещение мышью

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

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

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

Формулы поменяли свою прописку но, тем не менее, ссылаются на старые ячейки.

Подобным способом вы можете переносить обычные данные. А если при переносе будете удерживать CTRL, то данные скопируются.

Пароль с шифром

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

Для этого сначала совершаем обычные действия для сохранения файла. Это «Сервис – Параметры», вкладка «Безопасность» ( аналогично «Файл – Сохранить как – Сервис – Общие параметры»)

Затем ставим пароль для открытия и нажимаем на кнопку «Дополнительно» и выбираем способ шифрования, кроме первых 2-х. Также лучше не выбирать шифр, где стойкость ключа по умолчанию меньше 128.

Новая версия экселя этой возможности не имеет.

Быстрый переход к листу

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

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

Панель инструментов в первоначальное состояние

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

Для этого щелкаем правой кнопкой мыши по любой панели и выбираем «настройка». На вкладке «панели инструментов» выбираем нужную панель и нажимаем кнопку «Сброс». Теперь на панели останутся только те кнопки, имеющиеся по умолчанию.

CTRL+END (определить весь диапазон)

Существует самый простой способ определить весь диапазон на листе excel, где имеются какие-либо данные.

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

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

Подбор параметра при помощи диаграммы

С помощью диаграммы можно необычным способом запустить инструмент "Подбор параметра".

Представим такую ситуацию. В ячейке А1 находится число 5, в ячейке В1 – 7, а в ячейке С1 формула = А1+В1.

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

Теперь у нас возникла необходимость изменить конечный показатель скажем с 12, до 10. Это мы сможем сделать прямо на диаграмме. Для этого выделяем столбец двойным нажатием мыши и изменяем его размер до необходимого.

После этого будет запущен подбор параметра, где вы должны будете выбрать какое из двух слагаемых следует изменить.

Фамилия шефа

Очень часто, практически в каждом документе приходится вписывать фамилию вашего шефа.

Ввод фамилии можно существенно упростить. Заходим «Сервис – Параметры автозамены», открываем вкладку «Автозамена».

Теперь в окошке «заменять» впишем инициалы начальника, например А.С. а в окне «на» инициалы вместе с фамилией, т.е «А.С. Пушкин». Нажимайте «добавить», затем «Ок». Теперь, как только вы введете Первые буквы имени и отчества фамилия будет внесена автоматически.

CTRL+SHIFT+«стрелка»

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

Предположим мы внесли множество данных в столбец А и остановились на ячейке А654. И теперь ходим быстро выделить данные, которые расположены выше активной ячейки. Для этого нажимаем CTRL+SHIFT+«стрелка вверх».

Аналогичным способом можно использовать и стрелки вниз, вправо и влево.

Необычный условный формат

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

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

Для этого выделяем всю первую строку и в условном формате выбираем условие по формуле. Вписываем следующую формулу =$А1=15 (важно чтобы ссылка на столбец была абсолютной, а на строку относительной). Выбираем формат, окраска желтым цветом и жмем ОК.

Теперь выделенную строку при помощи маркера заполнения ( когда выделена вся строка он находится в нижнем левом углу ячейки а1) тянем вниз. Вот и все.

Пробуем внести в столбец A число 15 и вся строка окрашивается в желтый цвет. Исправляем и она бесцветная. Если внести 15 в столбец В, то ничего не произойдет.

Фиксированный десятичный формат

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

Но если вы хотите чтобы вводились, к примеру, только числа с двумя знаками после запятой гораздо удобнее зайти в раздел меню «Сервис», выбрать там команду «Параметры» и открыть закладку «Правка».

Затем ставим галочку напротив «Фиксированный десятичный формат при вводе» и установим нужное нам число знаков. 

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

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

Скрываем ленту в Excel 2007

Лента в Excel - 2007 занимает довольно обширное пространство и порой препятствует полноценному обзору информации на листе. В таких ситуациях ленту можно скрыть. Стандартная процедура данного действия следующая.

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

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

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

Перемещение внутри ячейки

Для того, что бы быстро перемещаться внутри одной ячейки от слова к слову, а не от буквы к буквы, как это происходит обычно надо нажимать клавиши «стрелка влево» («стрелка вправо») вместе с клавишей CTRL.

Перемещение пиктограмм

В более ранних версиях Excel лента отсутствует, зато вместо неё имеется меню и панели инструментов. С помощью данного секрета вы сможете быстро перемещать и удалять пиктограммы, расположенные на панелях инструментов.

Надо удерживая, ALT переместить иконку с командой в нужное место. А если вы ее перетяните в сторону от панелей инструментов, то пиктограмма будет удалена.

Быстрый вызов формата ячеек

Существует специальные горячие клавиши для вызова формата ячеек.

Для этого надо нажать CTRL+1

Выделение по словам

Для того что бы производить выделение внутри ячейки по словам, а не по символам используйте словосочетание CTRL+SHIFT+ «стрелка вправо» («стрелка влево»)

Два знака равенства

Существует частный вариант функции ЕСЛИ, который гораздо удобнее использовать необычным способом.

Предположим, требуется сравнить два числа, расположенные в ячейках А1 и С1. Если они равны, то отображается слово «Истина», если нет «Ложь».

Естественно данное задание можно реализовать при помощи уже упомянутой ЕСЛИ. Но более элегантный способ выглядит так:

=А1=С1

Перечеркивание

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

Поэтому стоит запомнить сочетание клавиш, которое избавит вас от посещения формата ячейки.

CTRL+5

Стили

Вас не устраивает размер и тип шрифта принятый по умолчанию?

Не нравится то, что Excel привязывает текст к нижней границе ячейке, а не к середине?

Вы хотите настроить на свой лад эти и еще множество других функций?

Тогда вам нужно обратить внимание на инструмент «Стиль». Его дислокация - раздел меню «формат».

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

Переход к меню

Если вы хотите попасть в меню программы при помощи клавиатуры, а не мышью, то делается это нажатием на клавишу ALT.

Просмотр файла без его открытия

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

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

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

Итак, в разделе меню «файл» выбираем «свойства», и на вкладке «документ» устанавливаем галочку «создать рисунок для предварительного просмотра». Это надо сделать во всех документах и в дальнейшем во вновь создаваемых файлах.

( В Excel -2007 «Кнопка Office – Подготовить – Свойства – Свойства Документа – Дополнительные свойства»)

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

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

Выделение отрицательных чисел

Можно довольно просто сделать так, чтобы все вводимые отрицательные числа отображались красным шрифтом. Делается это при помощи числового формата.

Итак, выделяем лист и открываем «формат ячеек». На вкладке «число» выбираем числовой формат и в поле «отрицательные числа» указываем вид отображения значений с минусами.

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

Выделение справа и слева

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

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

Для выделения справа от курсора SHIFT + END
Для выделения слева от курсора SHIFT +HOME

Если курсор находится в самом начале, или конце текста, то будет выделено содержимое всей ячейки.

Функция ПИ

Иногда в процессе работы приходится в вычисления вставлять число Пи. Делать это приходится вручную, и как правило с округлением до 2-х знаков после запятой.

Но существует функция, способная вставить значение Пи с точностью до 9 знаков. Выглядит формула вот так: =Пи()
Как видете она не требует никаких дополнительных аргументов. Также ее можно вставлять в другие форулы.

Например =А1+Пи()

Секрет с автозаполнением

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

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

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

И так для каждого последующего столбца.

Пора домой

Клавиша НОМЕ перемещает табличный курсор на первый столбец той строки, где в данный момент он находится.

А если вы нажмете одновременно CTRL+HOME тогда табличный курсор окажется на ячейке А1, где бы он не находился.

Дополнение к сортировке

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

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

Удаление примечаний

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

Сделать это просто можно при помощи инструмента – «Выделить». Итак, нажимаем Ф5, затем кнопку «Выделить», устанавливаем переключатель на «примечания» ( он в принципе там уже стоит) и нажимаем ОК.

Теперь достаточно удалить примечание один раз и все они исчезнут из вашего листа.

Условное форматирование с множеством условий

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

Предположим, нам надо выделять ячейки в столбе D, если в них содержатся числа 5,10,15,20 и 25.

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

=ИЛИ(D1=5;D1=10;D1=15;D1=20;D1=25)

( ссылка на первую ячейку столбца, т.е. D1 должна быть относительной)

В другую сторону

Что происходит, когда мы нажимаем на клавишу Enter? Да, табличный курсор перемещается на ячейку ниже. В одном из выпусков мы уже говорили, как установить другое направление для курсора.

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

Если же у вас направление установлено вправо, то при одновременном использовании шифта и энтера ячейка сместится влево.

Экономим место в гистограмме

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

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

Здесь можно увеличить показатель «перекрытие», в результате чего произойдет наложение столбцов каждой группы. А уменьшив данные параметра «ширина зазора» можно уменьшить поле между группами данных.

В результате данных процедур гистограмма будет выглядеть гораздо крупнее.

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

Элементы управления в Excel-2007

Все, кто использовали в Excel-2003 «поле со списком», «счетчик», «кнопку» и другие элементы на панели «формы» и «элементы управления», наверняка были огорчены их отсутствием в новой версии экселя. Я сам, признаюсь, потратил много времени на их поиск.

Однако все наши элементы в экселе 2007 года розлива также присутствуют, для них даже предусмотрена специальная вкладка –«разработчик». Но данная вкладка по умолчанию отключена. Чтобы ее отобразить нажмите на кнопку «Office», затем на «параметры excel» и на вкладке «основные» поставьте галочку «показывать вкладку разработчик на ленте».

После этого появится новая вкладка, где вы сможете найти элементы управления и формы.

Вставка из списка

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

Для этого выделим диапазон, где в последствии будут вставляться данные и проходим «Данные-Проверка» ( в excel-2007 вкладка «данные», группа – «работа с данными»). Затем «тип данных» укажем «список», а источник – ссылку на значения для подстановки.

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

Вставка дат из WORDa

При вставке разного типа дат из ворда, эксель их преобразут в единый формат. Например даты в формате 29-09-08 29.09.08 29/09/08 в excel будут представлены в виде 29.09.2008.

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

Отодвинетесь, пожалуйста

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

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

Дополнительная шкала

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

Причем создавать ничего не потребуется эксель всё сделает автоматически. Вам надо перейти к формату второй по величине группы рядов. Для этого один раз нажмите на любой столбец этой группы и в контекстном меню выберите «формат».

Затем перейдите на вкладку «ось» и установите переключатель на «по вспомогательной оси». Теперь с правой стороны появится новая шкала, а все столбцы, кроме гигантов увеличатся в размерах и будут соответствовать показаниям правой шкалы. А самый большой ряд надо будет сравнивать с левой шкалой.

Удаление скрытых строк и столбцов

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

Я передлагаю вам вот такое решение.

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

После этого отобразите скрытые ячейки. Теперь их легко найти нак они не закрашены. Удалите их а затем можете убрать желтый фон.

Отодвинетесь, пожалуйста

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

Дополнительная шкала

В таких случаях вам поможет дополнительная шкала. Причем создавать ничего не потребуется эксель всё сделает автоматически. Вам надо перейти к формату второй по величине группы рядов. Для этого один раз нажмите на любой столбец этой группы и в контекстном меню выберите «формат».

Затем перейдите на вкладку «ось» и установите переключатель на «по вспомогательной оси». Теперь с правой стороны появится новая шкала, а все столбцы, кроме гигантов увеличатся в размерах и будут соответствовать показаниям правой шкалы. А самый большой ряд надо будет сравнивать с левой шкалой.

Удаление скрытых строк и столбцов

Предлагаю вам вот такое решение.
Нажмите на F5, затем на кнопку «выделить». Установите переключатель на «только видимые ячейки». (произойдет выделение всего листа кроме скрытых строк и столбцов). Пока лист выделен окрастьте ячейки, скажем желтым цветом.

После этого отобразите скрытые ячейки. Теперь их легко найти нак они не закрашены. Удалите их а затем можете убрать желтый фон.

Полное скрытие листа

В экселе можно скрыть лист от любопытных глаз. Напомню, в excel-2003 это проделывается «формат-лист-скрыть», а в excel-2007 при помощи контекстного меню листа.

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

Такой скрытый лист без пароля уже не отобразишь.

Суммирование данных из разных листов

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

Например, чтобы посчитать сумму в ячейках А1, на листах «лист1», «лист2»… «лист7» надо составить формулу:

=СУММ(лист1:лист7!A1)

Подобным образом можно составить формулу с другими функциями. Но есть одно замечание. Наименование первого и последнего листа в формуле надо указывать так как они расположены от крайнего слева до крайнего справа. Т.е. если у вас между листом1 и листом7 будет лист8, то он также будет посчитан.

«Поиск дубликатов»

Найти в большом списке дубликаты можно при помощи условного форматирования. В экселе -2007 это делается очень просто, а вот в предыдущих версиях надо попотеть. Поэтому мы рассмотрим процедуру поиска совпадений для excel-2003.

Сначала выделяем столбец с данными и переходим «формат — условное форматирование». Условие ставим по формуле, а не по значению.

И укажем вот такую формулу:

=СЧЁТЕСЛИ($A:$A;A1)>1

где А1 — первая ячейка столба (обязательно ссылка должна быть относительной), где будет вестись поиск, $A:$A — весь диапазон для поиска ( у нас это столбец А).

Теперь нажимаем на кнопку «формат» и выбираем какое-нибудь форматирование, скажем желтая заливка. Теперь все ячейки, с повторами будут выделены.

Построчный ввод данных

Если вы вводите данные в таблицу построчно, то этот самый ввод можно существенно упростить.

Предположим, вы заносите данные сначала в А1, затем B1 и так далее до H1. После чего переходите на А2 и вбиваете до Н2 и так далее.

Так вот, вводя данные, переход от ячейки к ячейке в подобных случаях выполняйте клавишей TAB. А как только дойдете до конца строки нажимайте на ENTER. И табличный курсор окажется под той ячейкой, с которой вы и начинали ввод.

Не правда ли, удобно?

Выделяем максимальные значения

При помощи условного формата можно быстро выделить максимальное значение диапазона.

Для этого сначала выделим диапазон, а затем установим условное форматирование по формуле. Формула для диапазона А1:А15 будет такая:

=А1=МАКС(А1:А15)

С помощью формул можно выделить и минимальное и среднее значение и так далее.

Копируем лист целиком

Можно легко переместить, или скопировать лист на другую книгу, или в новую.

Для этого в контекстном меню листа выбираем переместить/скопировать и в появившемся окне в верхней строке «в книгу» выбираем любую открытую книгу для того, чтобы переместить в нее лист.

СОЗДАНИЕ АВТОМАТИЧЕСКОЙ НУМЕРАЦИИ СТРОК В ТАБЛИЦАХ EXCEL

Опишем несколько вариантов создания такой нумерации. Далее предполагается, что нумерацию надо создать в столбце А начиная с ячейки А2 (т.е. в ячейке А2 будет номер 1), тогда как данные записываются в соседних столбцах В, С и т.д. Первую строку таблицы отводим под заголовки.
Наиболее просто такая нумерация создается тогда, когда данные полностью, без пробелов, заполняют все строки таблицы и нумеруются все ее строки. В таком случае можно предложить два варианта нумерации, на основе условного форматирования и на основе формул.

=================================

Вариант 1 на основе условного форматирования
=================================

Заполняем столбец А номерами. Проще всего это сделать с помощью диалогового окна Прогрессия. В ячейку А2 записываем число 1, затем выполняем команду
в Excel 2003: Правка-Заполнить-Прогрессия,
в Excel 2007: Главная-Редактирование-Заполнить-Прогрессия.
В окне Прогрессия установите переключатель по столбцам, в поле Предельное значение введите предельный номер вашей нумерации и щелкните на кнопке ОК.

Нумерация создана. Теперь с помощью условного форматирования скроем те номера, которые превышают количество значений в столбце В. Для этого надо сначала подсчитать это количество. В любую пустую ячейку введите формулу =СЧЁТЗ(B:B). Эта формула подсчитывает количество непустых ячеек в столбце В. Я ввел эту формулу в ячейку А1 (чтобы она была под рукой) — потом скрою формулу и ее значение.

Далее выделяем столбец А (для этого достаточно щелкнуть на заголовке столбца там, где стоит буква А) и создаем формул условного форматирования.

В Excel 2003 выполните команду Формат-Условное форматирование. В открывшемся диалоговом окне Условное форматирование в первом раскрывающемся списке выберите опцию формула и в соседнее поле введите формулу =A2>$A$1. (Вместо моей ячейки $A$1 укажите вашу ячейку, где подсчитывается количество непустых ячеек в столбце В.) Затем щелкните на кнопке Формат. Откроется окно Формат ячеек.

В Excel 2007 выполните команду Главная-Стили-Условное форматирование-Создать правило. В открывшемся диалоговом окне Создание правила форматирования выберите тип правила Использовать формулу для определения форматируемых ячеек. Окно Создание правила форматирования немного изменится и в нижней части этого окна в поле ввода введите формулу =A2>$A$1. (Вместо моей ячейки $A$1 укажите вашу ячейку, где подсчитывается количество непустых ячеек в столбце В.) Затем щелкните на кнопке Формат. Откроется окно Формат ячеек.

Теперь надо установить формат для тех ячеек с номерами, которые пока не должны отображаться. Это может быть формат «белые цифры на белом фоне» или другой подобный формат. В моей книге на листе Вариант1 я сделал формат не «белые цифры на белом фоне», а «светло серые цифры на белом фоне», чтобы неиспользуемые номера были немного видны.

Чтобы скрыть содержимое ячейки А1 я открыл для этой ячейки окно Формат ячеек и на вкладке Число в списке Числовые форматы выбрал опцию (все форматы). Затем в поле Тип ввел формат ;;; (три точки с запятыми без пробелов) и щелкнул на кнопке ОК. Этот формат скрывает любое содержимое ячейки, будь то числа или текст, но его можно увидеть в строке формул, если выделена данная ячейка.

=========================
Вариант 2 на основе формул
=========================

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

=ЕСЛИ(ЕПУСТО(B3);"";A2+1)

которая вводится в ячейку А3 и затем копируется вниз по столбцу А.
Эта формула работает просто — функция ЕПУСТО проверяет, пуста ли ячейка B3, и если пуста, то в ячейку А3 заносится пустая строка "". Если ячейка B3 не пуста, то в ячейку А3 заносится значение A2+1, т.е. число 2.

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

Так вот, первый секрет.

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

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

Второй секрет более универсален.

Напоминаю, что в ячейке А2 записано число 1, а в ячейке А3 — формула.
Выделяем ячейку А3 и копируем ее, можно с помощью комбинации клавиш Ctrl+C. Далее нажимаем клавишу F5, которая открывает окно Переход. В этом окне указываем ячейку, которая должна содержать наибольший номер, например, ячейка А1001, и щелкаем на кнопке ОК. Мы автоматически переходим в ячейку А1001. Далее нажимаем комбинацию клавиш Ctrl+Shift+Home. В результате будет выделен весь столбец от ячейки А1 и до ячейки А1001. Теперь нажимаем клавишу Enter для вставки в выделенный диапазон ранее скопированной формулы. Нажимаем клавиши Ctrl+Home для перехода в ячейку А1 и видим значения ошибок в ячейках А1, А2, А3 и т.д. Но не пугайтесь, это легко поправимо. Удаляем содержимое ячейки А1, а в ячейку А2 снова записываем число 1. Все.)
Приведенная формула имеет тот недостаток, что она требует, чтобы в столбце В данные вводились без пропусков. Если в таблице данные разделены пустыми строками (как в книге Примеры на листе Вариант3), тогда надо применить чуть более сложную формулу для создания нумерации строк с данными.

==============
Вариант 3
==============

Вот эта формула, которая записывается в ячейку А2:

=ЕСЛИ(ЕПУСТО(B2));"";СЧЁТ($A$1:A1)+1)

Она будет работать и тогда, когда в столбце В есть пропуски данных, правильно нумеруя только строки с данными, что показано в книге Примеры на листе Вариант3.

Эта формула работает точно также, как и предыдущая. Однако теперь вставляемый номер строки определяется исходя не из номера предыдущей строки, а непосредственно вычисляется. Функция СЧЁТ($A$1:A1) вычисляет, сколько номеров было использовано ранее, и затем к этому числу прибавляет 1.

==================
Вариант 4
==================

Иногда надо нумеровать не строки подряд, а группы строк. При этом предполагается, что группы строк отделены друг от друга пустыми строками. Такая структура показана в книге Примеры на листе Вариант4. В этом случае для создания автоматической нумерации в ячейку А2 записывается 1, а в ячейку А3 формула

=ЕСЛИ(И(НЕ(ЕПУСТО(B3));ЕПУСТО(B2));СЧЁТ($A$1:A1)+1;"")

Эта формула копируется вниз по столбцу А.
В этой формуле функция И(НЕ(ЕПУСТО(B3));ЕПУСТО(B2)) будет возвращать значение ИСТИНА только тогда, когда соседняя ячейка в столбце В непустая, а ячейка сверху в том же столбце — пустая. В этом случае в ячейку столбца А вставляется номер. Иначе туда вставляется пустая строка.

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

С уважением, Александр Минько

Добавил: Александр Минько (XAPOH)

Условный формат для нуля

Если вы захотите при помощи условного формата выделить ячейки, содержащие 0 (ноль), то столкнётесь с проблемой. Дело в том, что эксель все пустые ячейки считает равными 0 и соответственно применит к ним условное форматирование.

Но есть способ исправить данную несправедливость. Для этого надо создать два условия, причем оба из них будут по формуле.

Первое условие: =А1=«» — формат не задан
Второе условие: =А1=0 — указываете формат для нулей

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

Перемещение ячейки

Чтобы перейти к ячейке находящейся справа надо нажать на клавишу TAB, а для того чтобы перейти на ячейку слева надо нажать TAB+SHIFT

Размеры ячейки

Точные размеры ячейки можно установить не только в разделе меню «формат» (формат — строка -высота, или формат -столбец — ширина), но и в контекстном меню, которое появится если щелкнуть правой кнопкой мыши по названиям строк, или столбцов.

Удобное копирование листа

Если лист используется как шаблон для создания других листов, то для этого существует команда «Переместить/скопировать лист» в контекстном меню ярлычка листа. Но есть способ проделать эту операцию куда проще.

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

Скрытие ошибок

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

Для этого выделим лист и укажем условие по формуле =ЕОШИБКА(А1)
А в формате укажем белый цвет шрифта.

Теперь все названия ошибок будут не видны.

Управление масштабом

Изменять масштаб можно при помощи соответствующего окна на панели инструментов.

А можно просто вращать в нужном направлении колесико мыши, но при этом удерживая CTRL.

Спасибо за советы

Бабаян Армен Юрьевич bukhara@bk.ru
Александр Минько, автор множества книг по Excel
конференция «Ваш вопрос по EXCEL»