Как с большой буквы сделать слова в эксель


Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель





На этой странице представлена коллекция простых и изящных инструментов работы в Excel. То, что по-английски называется tips & tricks. В некоторых случаях я даю ссылку на первоисточник. Это означает только то, что именно там я впервые встретил описание метода.

Начиная с версии 2010 в Excel появилась гибкая функция ЧИСТРАБДНИ.МЕЖД, которая позволяет для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней.

Если вам нужно найти  (столбце), или значение первой непустой ячейки, можно воспользоваться одним малоизвестным свойством функции ИНДЕКС возвращать не одно значение, а массив.

В то время как функция ЕСЛИ и большинство подобных функцией легко конвертируют логические значения ИСТИНА/ЛОЖЬ в числовые 1/0, СУММПРОИЗВ не делает этого. Чтобы справиться с проблемами используйте , или поместите все критерии в один аргумент, используя умножение () для Булевого критерия И и сложение (+) – для ИЛИ.

Если вы не начинающий пользователь, вас может заинтересовать книга Билла Джелена . В частности, вы узнаете о Булевой алгебре, операции минус минус, функции ДВССЫЛ, таймере, ссылках R1C1, датах до 1900 г.

Подробно о, возможно, самой полезной функции Excel — ВПР. 

Всё о сводных таблицам см. . В частности, раскрыты следующие темы:

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

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

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

Уже стало традицией, что Microsoft от версии к версии что-то улучшает, а что-то ухудшает (по крайней мере, делает нечто не однозначное). Так, начиная с 2013 г. нельзя указать Excel’ю сформировать вторую сводную таблицу на основе нового кеша. Как обойти это ограничение, читайте в заметке 

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

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

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

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

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

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

Если вам встретились незнакомые единицы измерения, например, баррель, oz, акр, узел, фунт, пинта, легко переведет их в привычные метры, литры, граммы…

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

Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. По умолчанию Excel создает не обычные ссылки, типа =С4, а в виде формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вам это неудобно, или вы хотите иметь возможность копировать формулу вдоль столбца, .

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

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

Познакомьтесь с функцией . С ее помощью вы сможете находить данные в тех случаях, когда не справляется ВПР. Главное же достоинство СМЕЩ проявляется в создании динамических диапазонов ( я касался этой темы).

Стандартные средства Excel не позволяют создавать . Например, вы хотите, чтобы состав списка зависел от содержимого ячейки, находящейся слева от ячейки со списком. Создайте именованные массивы и сошлитесь на них с помощью функции непрямого действия ДВССЫЛ (INDIRECT).

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

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

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

Финансовые функции в Excel кажутся очень сложными. Чтобы их понимать, нужно разбираться в предметной области — что, собственно говоря, рассчитывают эти функции. Прочитайте заметку , и для вас приоткроется завеса над некоторыми финансовыми функциями, в частности: ПЛТ, ОСПЛТ, ПРПЛТ, ПС, КПЕР, СТАВКА.

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

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

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

Стандартные средства Excel в круговых диаграммах позволяют использовать только один набор данных. Если вы хотите сравнить , воспользуйтесь несложным трюком, предложенным Д. Холи и Р. Холи в книге «Excel 2007. Трюки».

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

Около двух лет тому назад мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Оказалось, что файл содержал объекты, и мне удалось их удалить с помощью . У этой истории совсем недавно появилось продолжение. Посетитель блога оставил комментарий к той заметке, указав что стандартный Excel-файл, начиная с версии 2007, является zip-архивом. Так что для удаления «паразитного» содержимого достаточно .

Если вам нужно подсчитать , можете использовать нехитрый VBA код или стандартные средства Excel на основе функции ПОДСТАВИТЬ()

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

Воспользуйтесь формулой массива.

Вы думаете, что для моделирования с помощью метода Монте-Карло Excel

ю требуется какая-нибудь надстройка? Совсем не обязательно... <a href="http://baguzin.ru/wp/?p=3467">Простейшие модели можно реализовать с помощью обычных таблиц и функции СЛЧИС</a>. Правда, будьте осторожны, так как эта функция пересчитывается при каждом изменении на листе, и если вы захотите реализовать 100 000 сценариев, то Excel может "загрустить"... Но все же, для решения задач моделирования методом Монте-Карло лучше воспользоваться специализированной надстройкой, например, Crystal Ball. Читайте <a href="http://baguzin.ru/wp/?p=3485">Моделирование методом Монте-Карло в Crystal Ball для Excel</a> и <a href="http://baguzin.ru/wp/?p=3529">Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel</a> Для вычисления среднего значения диапазона данных в Excel есть функция СРЗНАЧ. Она игнорирует пустые ячейки, но, к сожалению, учитывает ячейки с нулевыми значениями. Используйте элегантную и простую <a href="http://baguzin.ru/wp/?p=3437">формулу массива для вычисления среднего, не учитывающего нулевые значения</a>. Откройте для себя мир формул массива. Ваши возможности в использовании Excel значительно расширятся. Если вы впервые знакомитесь с этой темой, начните с <a href="http://baguzin.ru/wp/?p=3248">Введения в формулы массива</a>, а затем изучите <a href="http://baguzin.ru/wp/?p=3311">Некоторые примеры использования формул массива</a>. Циклические ссылки не всегда являются проблемой файла Excel. <a href="http://baguzin.ru/wp/?p=3050">С помощью циклических ссылок можно находить корни уравнения, используя итерационные возможности.</a> Если вам нужно найти <a href="http://baguzin.ru/wp/?p=2936"><em>адрес ячейки</em>, содержащей максимальное (минимальное) значение в двумерном диапазоне</a>, можно воспользоваться не очень простой, но весьма изящной формулой массива. Если при открытии Excel-файла появляется <a href="http://baguzin.ru/wp/?p=2904">предупреждение о циклической ссылке</a>, существует простой способ найти ячейку, ответственную за это "безобразие". Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0! <a href="http://baguzin.ru/wp/?p=2780">Формула массива</a> позволяет преодолеть это ограничение. <a href="http://baguzin.ru/wp/?p=362">Ранее</a> я описал, как транспонировать столбцы в строки с сохранением связи новой области со старой с помощью функции ДВССЫЛ. А недавно обнаружил значительно более простой и изящный способ, <a href="http://baguzin.ru/wp/?p=2717">основанный на функции массива</a> {=ТРАНСП(массив)} Если данные разбросаны не вокруг среднего значения, а подчиняются линейному тренду, воспользуйтесь <a href="http://baguzin.ru/wp/?p=2695">формулой массива для определения стандартного отклонения</a> от линии тренда. Иногда возникает желание выделить часть столбиков гистограммы. Как правило, это делают путем изменения цвета заливки, последовательно выделяя те или иные столбики. А можно <a href="http://baguzin.ru/wp/?p=2609">подложить фон под часть столбиков</a>. Не все знают, что для расчета таблиц весьма полезными могут быть <a href="http://baguzin.ru/wp/?p=2251">смешанные ссылки</a>. Стандартные средства Excel предоставляют ограниченные возможности для отображения подписей данных на пузырьковых диаграммах. Воспользуйтесь <a href="http://baguzin.ru/wp/?p=2396">макросом</a>, и вы сможете вывести рядом с пузырями практически любые подписи. Умышленные или непреднамеренные действия способны исказить эффект, наблюдаемый на диаграммах. "Честные" графики должны показывать только то, что действительно содержится в данных. Эдвард Тафти предложил формулу для расчета <a href="http://baguzin.ru/wp/?p=2086">фактора лжи</a>, измеряющего искажения при визуализации данных. Некоторые пользователи любят украшать свои диаграммы массой дополнительных элементов, например, горизонтальными и вертикальными линиями сетки, подписями данных и т.п. Живой классик инфографики Эдвард Тафти, наоборот, <a href="http://baguzin.ru/wp/?p=2071">предлагает минимизировать количество элементов диаграммы.</a> В Excel нет стандартной функции суммирующей отдельно значения в четных и нечетных строках. Иногда возникает и более сложная задача - например, сложить значения в каждом пятом столбце. Рекомендую для этих целей <a href="http://baguzin.ru/wp/?p=1891">функции массива</a>. Проверьте мощь этих функций. Они не так сложны, как об этом принято думать. :) Для сокращения числа неточностей при вводе данных воспользуйтесь <a href="http://baguzin.ru/wp/?p=1775">раскрывающимся списком</a>, основанном на диапазоне ячеек. Вы можете полностью запретить ввод недопустимых данных, или только ограничиться сообщением об ошибках. Широко известна функция проверки правописания в Word или Outlook. Гораздо реже пользователи применяют <a href="http://baguzin.ru/wp/?p=1727">проверку формул в Excel.</a> В то же время такая проверка позволяет выявить довольно большое число ошибок. Иногда в ряде данных отсутствуют некоторые значения. Например, если данные собираются вручную, то можно элементарно забыть провести измерение. На графиках Excel <a href="http://baguzin.ru/wp/?p=1690">пропущенные данные</a> можно не отражать вовсе, можно отразить нулевое значение или среднее значение двух соседних с пропущенной точек. <strong>Затенение пространства между линиями графика. </strong>Если вы хотите закрасить пространство между двумя линиями графика, сделать это «в лоб» не получится. Однако реализовать задуманное вам поможет использование на одной диаграмме, как <a href="http://baguzin.ru/wp/?p=1410">линий, так и областей с накоплением</a>. <strong><a href="http://baguzin.ru/wp/?p=1376">Изменение настраиваемого вычисления для поля в отчете сводных таблиц.</a> </strong>Вместо привычной суммы, сводная способна выводить динамику (от ячейки к ячейке), сумму накопительным итогом, долю по строке или столбцу и много другое. <strong>Заменить цифры в подписях осей ординат на пользовательское содержание</strong> вы можете с помощью <a href="http://baguzin.ru/wp/?p=1293">дополнительного ряда данных в виде точечной диаграммы</a>. Если на графике вам необходимо разместить контрольные (опорные) горизонтальные линии, например, среднее значение и сигма-окрестность, добавьте дополнительные ряды данных и <a href="http://baguzin.ru/wp/?p=1215">специальным образом отформатируйте</a> линии на графике. Если же контрольную (опорную) вертикальную линию вам нужно добавить на гистограмму, воспользуйтесь малоизвестным приемом на основе <a href="http://baguzin.ru/wp/?p=1237">специальной вставки.</a> Воспользуйтесь встроенными возможностями Excel для построения <strong><a href="http://baguzin.ru/wp/?p=1170">графика нормального распределения</a>.</strong> Если вы используете <strong>гистограммы с накоплением</strong>, то знаете, что стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу. Изящный <a href="http://baguzin.ru/wp/?p=1153">трюк </a>позволяет преодолеть эту проблему. Если диаграмма слишком загромождена обилием данных можно воспользоваться <strong>элементами управления</strong>, например, <a href="http://baguzin.ru/wp/?p=1127">полосой прокрутки</a>, чтобы последовательно отражать на диаграмме одну часть данных за другой. <strong>Пузырьковые диаграммы </strong>позволяют наглядно представить зависимость <strong>трех </strong>переменных. Могут использоваться при <a href="http://baguzin.ru/wp/?p=1077">построении матриц типа BCG или в инфографике</a>. <strong>Преобразование массива в столбец или строку. </strong>Если необходимо <a href="http://baguzin.ru/wp/?p=909">преобразовать массив в столбец или строку</a>, можно воспользоваться функцией Индекс. <strong>Вывести примечания на лист. </strong>Если вы хотите вывести на текущий лист текст всех примечаний, можете воспользоваться <a href="http://baguzin.ru/wp/?p=851">макросом.</a> Будьте осторожны, чтобы не затереть содержимое ячеек, расположенных справа от ячеек с примечаниями. <strong>Создать сводную на основе данных с нескольких листов. </strong>Если вы столкнулись с необходимостью создать сводную таблицу на основе данных, размещенных на нескольких листах одной книги (или разных книг), вас ждет разочарование. Excel создаст сводную, но ее возможности будут существенно ограничены по сравнению со стандартными методами. Как обойти эти ограничения см. <a href="http://baguzin.ru/wp/?p=803">здесь</a>. <strong>Скрыть / показать ленту. </strong>Чтобы скрыть ленту дважды щелкнете по любой вкладке ленты. Чтобы временно отобразить ленту, щелкните нужную вкладку. Чтобы вернуть ленту, дважды щелкните на нужной вкладке. <strong>Прекратить торможение. </strong>Если Excel тормозит, и никаких видимых причин к этому нет, проверьте нет ли на листе паразитных объектов. Пройдите по меню: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты. Если таких объектов много и Excel виснет, примените <a href="http://baguzin.ru/wp/?p=743#more-743">макрос</a>. <strong>Используйте сводную таблицу на основе другой сводной.</strong> Стандартные средства Excel это не позволяют сделать. Но с помощью небольшой <a href="http://baguzin.ru/wp/?p=724">хитрости </a>можно обойти это ограничение. <strong>Вызвать окно «Формат ячеек» </strong>при помощи клавиатуры (это быстрее, чем любой иной метод): нажмите CTRL+1 <strong>Использовать мастер сводных таблиц.</strong> В Excel2007 разработчики Microsoft почему-то решили отказаться от мастера сводных таблиц и упрятали его так далеко, что сразу и не найдешь. Для использования мастера сводных таблиц выведите его на панель быстрого доступа. Подробности <a href="http://baguzin.ru/wp/?p=708#more-708">здесь</a>. <strong>Используйте функцию СТРОКА для создания заполнителя. </strong>Иногда возникает задача создать список, содержащий структурированную последовательность, например, для секторов, рядов и мест в театре / стадионе или для стеллажей на складе. Подробности <a href="http://baguzin.ru/wp/?p=505">здесь</a>. <strong>Преобразуйте данные в числа. </strong>При импорте из внешних источников данные нередко выглядят как числа, но числами не являются. Их нельзя суммировать, и вообще с точки зрения Excel это текст. Воспользуйтесь одной из двух возможностей, описанных <a href="http://baguzin.ru/wp/?p=460">здесь</a>, чтобы сделать числа настоящими. <strong>Вручную измените цену делений на оси времени. </strong>Возможно, вы уже сталкивались с тем, что шкала времени на графиках иногда отражается «криво». Excel автоматически создает цену деления равную, например, 7 мин 12 сек. Креативненько, неправда ли!? :) Установите цену деления <a href="http://baguzin.ru/wp/?p=435">сами</a>, не доверяя столь ответственную работу мозгам Excel я.

Используйте функцию ДВССЫЛ для транспонирования строк в столбцы с сохранением формул. Иногда возникает задача переноса данных из столбцов в строки (или наоборот). Если при этом вам нужно не просто транспонировать значения, а еще и сохранить ссылки, то попробуйте применить функцию .

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

Научите функцию ДЕНЬНЕД возвращать буквенное значение. Не знаю, как вас, а меня нервирует, что функция ДЕНЬНЕД возвращает число… например, 4. Куда как приятнее – «четверг». Эту проблему может легко решить небольшой или Excel.

Создать именованный динамический диапазон. Если вы работаете с данными, объем которых изменяется с течением времени (увеличивается число строк и/или столбцов), то очень удобно присвоить диапазону имя, а размер диапазона определить с помощью функции СМЕЩ. Подробности .

Суммируйте значения в ячейках определенного цвета. Довольно часто пользователи «раскрашивают» ячейки в разные цвета. Если потом возникает необходимость просуммировать значения в выделенных ячейках, то, к сожалению, у Excel`я нет такой стандартной функции. Воспользуйтесь VBA. Вы также можете подсчитать , содержащих текст определенного цвета.

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

Применяйте анализ чувствительности при построении моделей. Например, при подготовке инвестиционного проекта результатом моделирования явилась внутренняя норма доходности – IRR. Как поведет она себя при том или ином изменении исходных посылок? Подробности .

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

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

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


Источник: http://baguzin.ru/wp/?page_id=718





Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Как с большой буквы сделать слова в эксель

Читать далее: