Оглавление в экселе. Формула номера листа и страницы в Excel. Номер страницы на VBA

Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:

Видео

ГИПЕРССЫЛКА (HYPERLINK) Shift и/или Ctrl

Откройте Диспетчер Имен на вкладке Оглавление . В поле Диапазон (Reference) введите вот такую формулу:

Теперь в переменной Оглавление ИНДЕКС (INDEX)

ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND) Диспетчер имен с вкладки Оглавление и изменим его формулу:

ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА())

Для скрытия ошибок #ССЫЛКА (#REF) ИНДЕКС ЕСЛИОШИБКА (IFERROR)

Способ 3. Макрос

Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню SheetList

Ссылки по теме

• Что такое макрос, как его создать, куда копировать текст макроса, как запустить макрос?
• Автоматическое создание оглавления книги одной кнопкой (надстройка PLEX)
• Отправка писем с помощью функции ГИПЕРССЫЛКА
• Быстрый переход между листами книги Excel

Способ 1. Создаваемые вручную гиперссылки

Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка — Гиперссылка (Insert — Hyperlink) . В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:

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

Способ 2. Динамическое оглавление с помощью формул

Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление . В поле Диапазон (Reference) введите вот такую формулу:

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX) , которая «выдергивает» элементы из массива по их номеру:

Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.

Теперь наш список листов будет выглядеть существенно лучше:

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

Для скрытия ошибок #ССЫЛКА (#REF) , которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR) , которая перехватывает любые ошибки и заменяет их на пустую строку («»):

И, наконец, для добавления к именам листов «живых» гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА(HYPERLINK) , которая будет формировать адрес для перехода из имени листа:

Способ 3. Макрос

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

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

Способ мой. Мой вариант

Программа Microsoft Excel удобна для составления таблиц и произведения расчетов. Рабочая область – это множество ячеек, которые можно заполнять данными. Впоследствии – форматировать, использовать для построения графиков, диаграмм, сводных отчетов.

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

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

Как в Экселе подтянуть данные с другой листа? Онлайн-энциклопедия Полусказка

Для скрытия ошибок #ССЫЛКА (#REF) , которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR) , которая перехватывает любые ошибки и заменяет их на пустую строку («»):

Работа с формулами

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

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

Программа понимает стандартные математические операторы:

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

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

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

Постоянные и абсолютные ссылки

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

Относительные ссылки помогают «растянуть» одну формулу на любое количество столбцов и строк. Как это работает на практике:

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

Абсолютный адрес обозначается знаком «$». Форматы отличаются:

Работу абсолютной ссылки рассмотрим на примере расчета доли каждого товара в общей стоимости:

1. Сначала посчитать общую стоимость. Это делается несколькими способами: обычным сложением всех значений или автосуммой (выделить столбец с одной пустой ячейкой и на вкладке «Главная» справа выбрать опцию «Сумма», либо вкладка «Формулы» – «Автосумма»).

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

Как сделать формулу в Excel: пошаговая инструкция

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

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

+ Все виды действий с электронными таблицами (создание, редактирование, выполнение вычислений); построение графиков и диаграмм на основе данных из таблиц; работа с книгами и т.д.

— Редактирование таблиц; вывод данных из таблиц на печать; правка графической информации

9. К встроенным функциям табличных процессоров относятся:

тест 10. Какие типы диаграмм позволяют строить табличные процессоры?

— Гистограмма, график, локальное пересечение, аналитическая

11. Математические функции табличных процессоров используются для:

— Расчета ежемесячных платежей по кредиту, ставок дисконтирования и капитализации

12. Документ табличного процессора Excel по умолчанию называется:

13. Табличный процессор обрабатывает следующие типы данных:

— Матричный, Временной, Математический, Текстовый, Денежный

— Банковский, Целочисленный, Дробный, Текстовый, Графический

14. Статистические функции табличных процессоров используются для:

— Проверки равенства двух чисел; расчета величины амортизации актива за заданный период

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

— Расчета кортежа из куба; перевода из градусов в радианы

15. Какова структура рабочего листа табличного процессора?

+ Строки и столбцы, пересечения которых образуют ячейки

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

17. Финансовые функции табличных процессоров используются для:

— Вычисления произведения аргументов; определения факториала числа

— Определения ключевого показателя эффективности; построения логических выражений

+ Расчетов дохода по казначейскому векселю и фактической годовой процентной ставки

18. Табличные процессоры относятся к какому программному обеспечению?

19. В виде чего нельзя отобразить данные в электронной таблице?

тест_20. Дан фрагмент электронной таблицы с числами и формулами.

Чему равно значение в ячейке Е3, скопированное после проведения вычислений в ячейке Е1?

21. Расширение файлов, созданных в Microsoft Excel – это:

22. Координата в электронной таблице – это адрес:

23. Какие типы фильтров существуют в табличном процессоре Excel?

24. Наиболее наглядно будет выглядеть представление средних зарплат представителей разных профессий в виде:

25. 30 ячеек электронной таблицы содержится в диапазоне:

26. Выберите абсолютный адрес ячейки из табличного процессора Excel:

27. Скопированные или перемещенные абсолютные ссылки в электронной таблице:

— Преобразуются в соответствии с новым положением формулы

— Преобразуются в соответствии с новым видом формулы

— С формулой, в которой содержится относительная ссылка

29. Отличием электронной таблицы от обычной является:

+ Автоматический пересчет задаваемых формулами данных в случае изменения исходных

— Представление связей между взаимосвязанными обрабатываемыми данными

тест-30. Совокупность клеток, которые образуют в электронной таблице прямоугольник – это:

31. В табличном процессоре Excel столбцы:

32. Символ «=» в табличных процессорах означает:

33. Какого элемента структуры электронной таблицы не существует?

34. Числовое выражение 15,7Е+4 из электронной таблицы означает число:

36. Подтверждение ввода в ячейку осуществляется нажатием клавиши:

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

38. Для чего используется функция Excel СЧЕТ3?

тест*40. Укажите верную запись формулы:

41. Маркер автозаполнения появляется, когда курсор устанавливают:

43. Можно ли убрать сетку в электронной таблицу Excel?

44. Если при выполнении расчетов в ячейке появилась группа символов #########, то это означает, что:

+ Ширина ячейки меньше, чем длина полученного результата

— Полученное значение является иррациональным числом

45. В электронной таблице выделен диапазон ячеек A1:B3. Сколько ячеек выделено?

Примечание. Символ # является джокерным для числа, т.е., говорит о месте отображения чисел, введенных в ячейку. Аналогично символ @ является джокерным для текста, т.е., говорит о месте отображения текста, введенного в ячейку.

Как быстро заменить в экселе определенные буквы. Изменение наименования столбцов с числового на буквенное

Ссылка на лист получается такой, как в ячейке А2. Но в строке формул можно подкорректировать название, например, как в ячейке А3 – убрали восклицательный знак и адрес ячейки. Если листы подписаны по-другому, то в списке будут эти названия листов. Смотрите ячейку А4.

Как ввести в формулу ссылку на ячейку другой книги?

Вы выберите ячейку или ячейки, для которых нужно создать внешнюю ссылку. Введите = (знак равенства). Перейдите в книгу-источник и щелкните книгу, содержаную ячейки, которые нужно связать. Нажмите F3,выберите имя, на которое будет ссылаться ссылка, и нажмите ввод.

Автоматический перенос данных из одной таблицы в другую в программе Excel.

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

Тесты по excel с ответами

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
3. Щелкните ярлычок листа, на который нужно сослаться.
4. Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

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

MS Excel: как скрыть нулевые значения

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

2. Становимся на ячейку « B3 ». Здесь записано выражение для расчета объема закупок по контрагенту « ТОВ «УкрСнаб» » за « 13.08.2012 ».

3. Щелкаем левой кнопкой в строке формул (или нажимаем клавишу « F2 »). Станет доступно содержимой ячейки « B3 ».

4. Выделяем текст формулы без знака « = » и нажимаем комбинацию « Ctrl+C » (копируем его в буфер обмена). Этот текст мы сейчас вставим внутрь функции « ЕСЛИ() ».

5. Нажимаем « Ecs » — выходим из режима редактирования ячейки.

6. В ячейку « B3 » вводим текст « =ЕСЛИ( ». Тем самым мы приступили к вводу формулы.

7. Нажимаем « Ctrl+V » (вставляем содержимое из буфера обмена). В данный момент у нас должно получиться выражение: « =ЕСЛИ( СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)* (БД!$D$2:$D$65536=$A2)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)) ». Полужирным начертанием показан результат добавления в формулу содержимого буфера обмена. Пока ничего не нажимаем, продолжаем вводить формулу!

9. Вводим текст « «»; ». Это и будет второй параметр функции « ЕСЛИ() ». Переходим к третьему параметру. Здесь все просто. В качестве этого параметра будет исходное выражение для определения объема закупок в ячейке « B3 ». А такое выражение у нас есть, и находится оно в буфере обмена. Так что дальнейшие наши действия четко определены.

10. Не покидая режим редактирования формулы, нажимаем « Ctrl+V ». Выражение в « B3 » станет таким: « =ЕСЛИ(СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)* (БД!$F$2:$F$65536)*(БД!$G$2:$G$65536))=0 ; «» ;СУММПРОИЗВ((БД!$C$2:$C$65536=B$1)*(БД!$D$2:$D$65536=$A10)*(БД!$F$2:$F$65536)*(БД!$G$2:$G$65536)) ) » (изменения показаны полужирным).

11. Нажимаем « Enter » (завершаем редактирование формулы).

12. Копируем содержимое ячейки « B3 » на всю таблицу. Результат показан на рис. 3. Как и следовало ожидать, нулевых значений в отчете нет. Вместо них вставлен символ « «» » (пустая строка).

Важно! Такой прием нужно применять осторожно. После замены нулей на пустую строку некоторые функции Excel могут работать неправильно.

Преимущество описанного подхода: при помощи формул можно выборочно скрыть нулевые значения в любой(!) группе ячеек рабочего листа. Кроме того, вместо пустой строки можно использовать пробел, дефис, комбинации « -«- »,« Х » — все что угодно.

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

Скрываем нулевые значения при помощи параметров Excel

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

Чтобы скрыть нулевые значения в программе Excel 2010, делаем так:

2. Вызываем меню « Файл → Параметры ». Откроется окно « Параметры Excel », как на рис. 4.

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

Важно! Параметр отображения нулевых значений является свойством листа документа. То есть он действует на все ячейки рабочего листа MS Excel . Этот параметр Excel сохраняет вместе с рабочей книгой.

Чтобы изменить параметр отображения нулевых значений в программе Excel 2003, делаем так:

2. Вызываем меню « Сервис → Параметры… ». Откроется одноименное окно « Параметры », как на рис. 5.

4. В группе « Параметры окна » отключаем флажок « нулевые значения » (рис. 5).

5. В окне « Параметры » нажимаем « ОК ». Программа Excel скроет все нулевые значения на текущем листе.

— параметр отображения нулевых значений просто изменить;

— изменение параметра действует сразу на все ячейки рабочего листа MS Excel .

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

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

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

1. Открываем документ. Переходим в ячейку « F1 » и вводим заголовок рабочей колонки (я назвал ее « Пр »).

3. В ячейку « F3 » вводим формулу « =СУММ(B3:E3) ». В нашем случае такая формула покажет строки, где есть только нулевые значения.

5. Выделяем все колонки документа (на рис. 6 это столбцы « A:F »).

6. Вызываем меню « Главная », щелкаем на иконке « Сортировка и фильтр » (она расположена в группе « Редактирование »). Откроется меню из шести пунктов, как на рис. 7.

7. Из этого меню выбираем « Фильтр ». В области заголовков таблицы появятся значки выбора.

8. Щелкаем на таком значке в поле « Пр ». Откроется меню, как на рис. 8.

9. В нем последовательно выбираем « Числовые фильтры », затем « не равно… ». Откроется окно « Пользовательский автофильтр », как на рис. 9.

10. Здесь в область значения вводим « 0 », как показано на рис. 9.

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

В программе Excel 2003 описанная процедура выглядит так:

2. Выделяем все столбцы документа (на рис. 6 — это колонки « A:F »).

3. Вызываем меню « Данные → Фильтр → Автофильтр ». В области заголовков таблицы появятся значки выбора.

4. Щелкаем на значке в поле « Пр ». Откроется меню, как на рис. 10.

5. Из этого меню выбираем вариант « (Условие…) ». Откроется окно « Пользовательский автофильтр », как на рис. 9.

6. Щелкаем на значке выпадающего списка с названием « Пр ».

7. Из предложенных вариантов выбираем « не равно » (рис. 10).

8. В область значения вводим « 0 », как показано на рис. 9.

9. В окне « Пользовательский автофильтр » нажимаем « ОК ». Строки с нулевыми значениями станут на экране не видны.

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

— нельзя выборочно скрывать нули в произвольной группе ячеек таблицы;

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

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

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

Пользовательский формат числа в Excel

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX) , которая «выдергивает» элементы из массива по их номеру: