Холи Р., Холи Д. Excel. Трюки – скачать книгу бесплатно Скачать книгу

Поиск книг на сайте  |  Каталог книг в формате pdf, djvu, fb2  |  Читайте нас вЧитайте нас в twitter!
Не нашли нужную книгу? Закажите
Подпишитесь на бесплатную рассылку новых книг

Скачать книгу Холи Р., Холи Д. Excel. Трюки

Холи Р., Холи Д. Excel. Трюки

Холи Р., Холи Д. Excel. Трюки: 100 профессиональных примеров. –СПб.: Питер, 2005. –287 с.: ил. ISBN 5-469-00384-1.

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

Зачем нужны трюки Excel? Вам понравятся эти новые возможности!

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

Есть много способов воспользоваться преимуществами Excel и значительно расширить возможности создания сложных электронных таблиц, и чтобы познакомиться с ними, не нужно тратить годы на обучение. Эти средства, трюки, – быстрые и хитрые решения проблем или более искусные приемы – были созданы пользователями Excel, которые хотели найти простые решения сложных задач. Трюки, о которых рассказывает эта книга, разработаны, чтобы показать возможности Excel и заставить их работать как надо.
Изучив эти трюки, вы получите преимущество в двух важных аспектах. Во-первых, вы сможете применять эти трюки при построении и улучшении ваших электронных таблиц. Во-вторых, изучив трюки и, возможно, немного кода Visual Basic for Applications (VBA), вы сможете настроить их в точном соответствии с вашими требованиями.

Трюк №5: Как скрыть лист Excel, чтобы его невозможно было отобразить

Иногда требуется место для информации, которую пользователи не смогут прочитать или изменить. Встройте тайное место в рабочую книгу, место, где вы сможете хранить данные, формулы и прочие мелочи, используемые, но невидимые на ваших листах.
При настройке новой книги полезно резервировать один лист для хранения информации, которую пользователи не увидят, например, для вычисления формул, проверки данных, списков, полезных переменных и специальных значений, важных данных и т. п. Хотя этот лист можно скрыть, выбрав команду Формат => Лист => Скрыть (Format => Sheet => Hide), лучше гарантировать, что пользователи не смогут отобразить лист, выбрав команду Формат => Лист => Отобразить (Format => Sheet => Unhide).

Конечно, можно просто защитить лист. Однако он все так же будет полностью виден – важные данные, страшные формулы и все остальное. Кроме того, вы не сможете защитить от панели инструментов Формы (Forms) ячейку, связанную с любым доступным элементом управления.
Вместо этого мы поиграем со свойством рабочего листа Visible, присвоив ему значение xlVeryHidden. В VBE (Сервис => Макрос => Редактор Visual Basic (Tools => Macro => Visual Basic Editor) или Alt/Option+F11) откройте окно Project Explorer командой View => Project Explorer. Найдите там имя своей книги и раскройте ее иерархию, щелкнув знак «плюс» слева от имени книги. Откройте папку Microsoft Excel Objects, чтобы увидеть все листы книги.

В Project Explorer выберите лист, который хотите скрыть, и откройте его свойства, выбрав команду View => Properties Window (или нажав клавишу F4). Удостоверьтесь, что выбрана вкладка Alphabetic и взгляните на свойство Visible в самом низу. Щелкните справа поле со значением свойства Visible и выберите последнее значение, 2 – xlSheetVeryHidden (рис. 1.9). Нажмите сочетание клавиш Alt/Apple+Q, чтобы сохранить изменения и вернуться в Excel. Этот лист не будет виден в окне Excel и не появится, если воспользоваться командой Формат => Лист => Отобразить (Format => Sheet => Unhide).

После того, как вы выбрали 2 – xlSheetVeryHidden в окне свойств Properties, может показаться, что этот выбор не имел никакого воздействия. Это обман зрения, который иногда происходит в Excel. Если этот лист не перечислен в списке Формат => Лист => Отобразить (Format => Sheet => Unhide), значит, желаемый эффект достигнут.
Чтобы добиться обратного результата, выполните те же шаги, но в этот раз выберите свойство – 1 – xlSheet Visible.

Советы по форматированию таблиц Excel с помощью полезных трюков

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

Всегда полезно оставлять, по меньшей мере, три пустых строки над таблицей (минимум три, лучше больше). Их можно будет использовать для определения условий в таких средствах Excel, как Расширенный фильтр (Advanced Filter) и функции работы с базами данных.
Обычно пользователи небрежно работают с данными в ячейках. По умолчанию числа в Excel выравниваются по правому краю, а текст – по левому, и для этого есть основательные причины. Если вы начнете изменять такое форматирование, то не сможете с первого взгляда определять, является ли содержимое ячейки текстом или числом. Очень часто люди ссылаются на какие-то ячейки, считая их содержимое числами, но на самом деле в них хранится текст. Если вы измените выравнивание по умолчанию, останется только в задумчивости чесать голову, глядя на содержимое ячеек. Возможно, исключением из этого правила являются заголовки.

Форматируйте ячейки как текст, только когда это действительно необходимо. Все введенные в ячейки данные, отформатированные как текст, становятся текстовыми, даже если вы хотели оставить их числами или датами. Что еще хуже, любая ячейка с формулой, которая ссылается на ячейку, отформатированную как текст, также станет текстовой. В общем случае вы вряд ли захотите отформатировать как текст ячейки с формулами.
Объединенные ячейки также могут быть причиной проблем. База знаний Microsoft содержит множество часто встречающихся проблем с объединенными ячейками. В качестве хорошей альтернативы используйте параметр По центру выделения (Centeracrossselection) в группе Погоризонтали (Horizontal) на вкладке Выравнивание (Alignment) диалогового окна Формат ячеек (Format Cells).

Советы по применению формул – полезные трюки

Еще одна ошибка, которую пользователи часто делают в формулах Excel, – это ссылки на целые столбцы. Это приводит к тому, что Excel начинает просматривать тысячи, если не миллионы ячеек, которые в противном случае были бы попросту пропущены.
Предположим, например, у вас есть таблица данных, начинающаяся с ячейки А1 и заканчивающаяся Н1000. Вы хотите применить одну или несколько формул поиска Excel для выбора необходимой информации. Так как ваша таблица может со временем увеличиться (по мере добавления новых данных), в формуле вы ссылаетесь на всю таблицу, включая все строки. Другими словами, эта ссылка может выглядеть приблизительно как А:Н или, возможно, А1:Н65536. Такой вариант используется для автоматического учета в формулах новых добавленных в таблицу данных.

Это чрезвычайно плохая привычка, и практически всегда следует избавляться от нее. Можно исключить необходимость постоянного обновления ссылок в формулах для включения новых добавляемых в таблицу данных с помощью динамических именованных диапазонов, которые рассматриваются в разделе «Создание увеличивающихся и уменьшающихся диапазонов» [Трюк №42].

Еще одна распространенная проблема, связанная с плохо спроектированными электронными таблицами, – это болезненно низкая скорость пересчета. Многие полагают, что эту проблему может решить режим вычисления вручную (Manual) – Сервис => Параметры => Вычисления (Tools => Options => Calculations).

Однако в общем случае это очень плохой совет, грозящий потенциальными бедами. Электронная таблица существует за счет формул и вычислений и доставляемых ими результатов. Если вы работаете с электронной таблицей в ручном режиме вычисления, рано или поздно в таблице появится необновленная информация. Формулы будут отражать старые, а не обновленные значения, так как в ручном режиме вычисления необходимо запускать пересчет, нажимая клавишу F9. Но об этом так легко забыть! Попробуем провести аналогию. Если бы тормоза на вашей машине были неисправны и, создавая излишнее трение, замедляли бы машину, то отключили бы вы педаль тормоза, положившись исключительно на ручной тормоз, вместо того чтобы устранить неисправность? Большинству из нас такая мысль даже не пришла бы в голову, однако множество пользователей, ни минуты не сомневаясь, переключают свои электронные таблицы в ручной режим вычисления. Если ситуация требует, чтобы таблица работала в ручном режиме вычисления, – у вас проблема с дизайном. Отнеситесь к этому серьезно, не решайте ее поверхностным или временным способом.

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

Множество альтернатив формулам массивов можно найти среди функций баз данных Excel. Об этом рассказывается в разделе «Суммирование и подсчет ячеек» [Трюк №66]. Несколько очень хороших примеров использования этих формул на больших таблицах данных и возврата результатов на основе нескольких критериев можно найти в справке Excel. Часто пользователи упускают еще одну возможность – сводную таблицу, которую мы рассмотрим в главе 4. Хотя, впервые встретив сводные таблицы, вы можете быть обескуражены, мы рекомендуем ближе познакомиться с этой замечательной возможностью Excel. Овладев искусством работы со сводными таблицами, вы будете удивляться, как раньше обходились без них.

Трюк №18 – Поиск формул при помощи условного форматирования

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

Код VBA в этой пользовательской функции (также называемой функцией, определенной пользователем) позволяет идентифицировать ячейки, содержащие формулы, не щелкая и не изучая все 10 000 ячеек.
Чтобы превратиться в хитрого охотника на формулы, начните с перехода в редактор Visual Basic – Сервис => Макрос => Редактор Visual Basic (Tools => Macro => Visual Basic Editor) – и выберите команду Insert => Module. Введите следующую функцию:

Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

Закройте окно (нажмите сочетание клавиш Alt/Apple+Q или кнопку закрытия в строке заголовка окна). Теперь эта функция доступна из любой ячейки на любом листе в этой книге; для этого нужно ввести формулу =IsFormula($A$l). Добавить эту функцию можно также, выбрав команду Вставка => Функция (Insert =>  Function), для параметра Категория (Category) выберите вариант Определенные пользователем (User-Defined) и в списке функций выберите IsFormula.
Формула возвращает ИСТИНА (TRUE), если в соответствующей ячейке содержится формула, и ЛОЖЬ (FALSE), если это не так. Этот булевский результат можно использовать совместно с условным форматированием, чтобы автоматически выделить все формулы с применением нужного форматирования.

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

Теперь, когда ячейки выделены, причем ячейка А1 должна быть активной, выберите команду Формат => Условное форматирование (Format => Conditional Formatting). В поле условий выберите Формула (Formula Is) и в поле справа от него введите =IsFormuIa(Al). Щелкните кнопку Формат (Format) и выберите нужное форматирование, которое будет применяться для идентификации ячеек с формулами. Щелкните на кнопке ОК, затем еще раз щелкните на кнопке ОК.
Иногда при вводе формул в окне условного форматирования Excel пытается добавить кавычки вокруг формул после того, как вы щелкаете на кнопке ОК. Это означает, что Excel распознал то, что вы ввели, как текст, а не как формулу. Если это произошло, вернитесь в окно Условное форматирование (Conditional Formatting), удалите кавычки и щелкните на кнопке ОК.

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

Не читаются скачанные книги в форматах djvu, pdf, rar, fb2? Не получается скачать книгу с depositfiles? Прочтите подсказки

Скачать электронную книгу в формате pdf djvu Скачать бесплатно книгу Холи Р., Холи Д. Excel. Трюки

Читать фрагмент, купить и скачать в магазине электронных книг Купить  и  скачать  книгу Читать фрагмент, купить и скачать книгу fb2, epub, на андроид в магазине электронных книг ЛитРес

Скачайте похожую бесплатную книгу:
Гладкий А.А., Чиртик А.А. Excel. Трюки и эффекты

Читайте спиcок всех книг онлайн для бесплатного скачивания без регистрации: Формулы Excel

Каталог книг по темам для бесплатного скачивания в электронных форматах

Наш сайт регулярно обновляется, и Вы можете получать новинки – электронные книги, которые на нём размещаются.
Подпишитесь на обзор книжек, и он будет приходить на Вашу электронную почту.
Вы всегда сможете легко отказаться от этой бесплатной рассылки. --> Читать последний выпуск книжной рассылки
подписаться на новые книги:
 
Я
Ищу
в возрасте от до

 
Следите за книжными новинками в Twitter
Рейтинг@Mail.ru Яндекс.Метрика +Freabooks