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

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

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

Д.Холи, Р.Холи. Excel 2007. Трюки. 138 профессиональных приемов. – СПб.: Питер, 2008. – 368 с. [1]

Здесь я публикую только содержание книги, которое поможет вам определиться, представлена ли тема, которая вас волнует. Если ссылка на номере трюка отсутствует, найдите оригинал книги. Если ссылка есть, значит я уже осветил указанную тему. Так же вам может пригодиться ссылка на сайт авторов, с которого можно загрузить скрипты и электронные таблицы со всеми перечисленными трюками: http://www.ozgrid.com/BookExamples/excel-hack2-examples.htm

Глава 1. Упрощение рабочих книг и листов

1. Создание персональных представлений рабочих книг

2. Одновременный ввод данных в несколько рабочих листов

3. Запрет пользователям выполнять определенные действия

4. Запрет ненужных сообщений

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

6. Настройка диалогового окна шаблонов и рабочей книги по умолчанию

7. Создание указателя листов в книге

8. Ограничение диапазона прокрутки листа

9. Блокировка и защита ячеек, содержащих формулы

10. Поиск повторяющихся данных при помощи условного форматирования

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

12. Привязка пользовательских панелей инструментов к конкретной книге

13. Как перехитрить обработчик относительных ссылок

14. Удаление несуществующих ссылок на книги

15. Предотвращение «распухания» рабочей книги

16. Извлечение данных из поврежденной книги

Глава 2. Трюки со встроенными возможностями Excel

17. Проверка данных на основе списка на другом листе

18. Управление условным форматированием при помощи флажков

19. Поиск формул при помощи условного форматирования

20. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

21. Выделение строк или столбцов через один

22. Создание трехмерных эффектов в таблицах и ячейках

23. Включение и выключение условного форматирования и проверки данных при помощи флажка

24. Поддержка нескольких списков и поле переключателя

25. Создание списков проверки, меняющихся в зависимости от значения, выбранного в другом списке

26. Удаление ненужных символов при помощи замены

27. Преобразование чисел из текстового формата в настоящие числа

28. Выделение числовой части из текстовой строки

29. Настройка примечаний ячеек

30. Сортировка более чем по трем столбцам

31. Случайная сортировка

32. Манипулирование данными при помощи расширенного фильтра

33. Создание пользовательских числовых форматов (заметку с аналогичной проблематикой я написал по другим материалам)

34. Увеличение количества уровней отмены действий в Excel для Windows

35. Создание пользовательских списков

36. Выделение промежуточных итогов жирным шрифтом

37. Преобразование формул и функций Excel в значения

38. Автоматическое добавление данных в список проверки

39. Трюки с датой и временем в Excel

40. Группировка и структурирование на защищенном рабочем листе

41. Предотвращение появления пустых/отсутствующих полей в таблице

42. Составление сокращенных списков проверки данных

43. Добавление списка к маркеру заполнения

Глава 3. Трюки с именованием

44. Адресация данных по имени

45. Использование одинаковых имен для диапазонов на разных рабочих листов

46. Создание пользовательских функций при помощи имен

47. Создание диапазонов переменных размеров

48. Вложенные динамические диапазоны

49. Поиск именованных диапазонов на рабочем листе

Глава 4. Трюки со сводными таблицами

50. Сводные таблицы: сами по себе — трюк

51. Совместное использование сводных таблиц, но не их данных

52. Автоматизация создания сводных таблиц

53. Перемещение общих итогов в сводных таблицах

54. Эффективная вставка данных из другой рабочей книги

Глава 5. Трюки с диаграммами

55. Выделение одного сектора из круговой диаграммы

56. Создание в одной круговой диаграмме двух наборов секторов

57. Создание диаграмм, изменяющихся вместе с данными

58. Взаимодействие с диаграммами при помощи пользовательских элементов управления

59. Четыре быстрых способа обновления диаграмм: Использование строки формул

60. Трюк с созданием простой диаграммы-термометра

61. Создание гистограмм с переменной шириной и высотой

62. Создание диаграммы-спидометра

63. Привязка текстовых элементов диаграмм к ячейкам

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

65. Вставка стрелки направления в конец ряда

66. Добавление стрелки на конце горизонтальной оси (X)

67. Изменение ширины узких столбцов при использовании дат

68. Положение подписей оси

69. Диаграмма «торнадо»

70. График-«датчик»

71. Условное выделение подписей оси

72. Создание итогов на нормированной гистограмме

Глава 6. Трюки с формулами и функциями

73. Добавление описаний к формулам

74. Перемещение относительных формул без изменения ссылок

75. Сравнение двух диапазонов Excel

76. Заполнение всех пустых ячеек в списке

77. Как сделать так, чтобы формула инкрементировала ссылки на строки при копировании по столбцам

78. Преобразование дат в отформатированные даты Excel

79. Суммирование и подсчет ячеек с блокированием ОШИБОК

80. Как уменьшить влияние переменных функций на скорость пересчета

81. Учитываем только одно вхождение записи в список

82. Суммирование каждой второй, третьей или n-й строки или ячейки

83. Поиск n-го вхождения значения

84. Делаем функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ динамической

85. Добавление расширений даты

86. Преобразование чисел со знаком минуса справа в числовой формат Excel

87. Отображение отрицательных значений времени

88. Использование функции ВПР в нескольких таблицах

89. Отображение общего времени: дни, часы и минуты

90. Вычисление количества указанных дней недели в любом месяце

91. Построение мегаформул

92. Трюки с мегаформулами, которые ссылаются на другие рабочие книги

93. Заставим одну функцию баз данных Excel работать вместо нескольких функций

94. Извлечение указанных слов из текстовой строки

95. Подсчет слов в ячейке или диапазоне ячеек

96. Вставка названия рабочего листа в ячейку

97. Суммирование ячеек с множеством условий

98. Подсчет количества ячеек по нескольким критериям

99. Расчет скользящей шкалы налогов

100. Добавление/вычитание месяца из даты

101. Найти последний день месяца

102. Расчет возраста

103. Определение дня недели, связанного с датой

104. Вычисление текстового выражения

105. Поиск в ячейке

Глава 7. Трюки с макросами

106. Ускорение выполнения кода и исключение мерцания экрана

107. Запуск макроса в определенное время

108. Использование кодовых имен для ссылки на листы в рабочих книгах Excel

109. Простое назначение кнопок макросам

110. Создание всплывающего экрана для рабочей книги

111. Отображение сообщения «Please, wait»

112. Как помечать и сбрасывать пометку ячеек при выделении

113. Подсчет или суммирование ячеек с определенным цветом заливки

114. Добавление элемента управления Календарь в любую рабочую книгу Excel

115. Включение и снятие пароля для всех рабочих листов Excel одновременно

116. Получение имени и пути рабочей книги

117. Как обойти ограничение Excel на три критерия условного форматирования

118. Выполнение процедур на защищенных рабочих листах

119. Распространение макросов

120. Удаление строк, основываясь на условии

121. Отслеживание и отображение изменений в Excel

122. Добавление даты/времени в ячейку после ввода

123. Создание списка гиперссылок в книге

124. Расширенный поиск

125. Поиск чисел в интервале

126. Преобразование ссылок в формулах из относительных в абсолютные

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

128. Сокрытие и восстановление панелей инструментов в Excel

129. Сортировка рабочих листов

130. Защита рабочего листа паролем от просмотра

131. Изменение регистра текста

132. Принудительное преобразование всего текста или начальных букв слов в тексте в верхний регистр

133. Делаем код VBA нечувствительным к регистру ввода

134. Отображение критерия автофильтра

Глава 8. Трюки с Excel и другими приложениями

135. Импортирование данных из Access 2007

136. Получение данных из закрытых рабочих книг

137. Автоматизация работы Word из Excel

138. Автоматизация работы Outlook и Excel

[1] В 2005 г. в издательстве Питер вышла книга «Excel. Трюки. 100 профессиональных примеров».