Как сделать чтобы ячейка не менялась в excel

Как сделать чтобы ячейка не менялась в excel?

Дата: 15 ноября 2018 Категория: Excel Поделиться, добавить в закладки или статью

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

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

Включено отображение формул

В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в этой статье. Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы. Если она включена – кликните, по ней, чтобы отключить.

Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+`.

Формула воспринимается программой, как текст

Если Excel считает, что в ячейке текстовая строка, он не будет вычислять значение, а просто выведет на экран содержимое. Причин тому может быть несколько:

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

    Измените формат на Числовой, как сказано здесь.
  • Нет знака «=» вначале формулы. Как я рассказывал в статье о правилах написания формул, любые вычисления начинаются со знака «равно».

    Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу.
  • Пробел перед «=». Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.

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

    Удалите лишние кавычки, нажмите Enter, чтобы пересчитать.

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

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

Поделиться, добавить в закладки или статью

Источник:
http://word-office.ru/kak-sdelat-chtoby-yacheyka-ne-menyalas-v-excel.html

«Excel». Как сделать, чтобы ячейки в формуле не менялись при копировании?

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

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

Лучше всего разобрать на примере.

Допустим в ячейке у Вас формула

если Вы растяните ее вниз (или скопируете и вставите вниз — в следующую ячейку)

то формула преобразуется и станет вот такой:

Однако, если Вам нужно, чтобы ко всем ячейкам из столбца A (последовательно) прибавлялось некое значение из ячейки B2 и при копировании эта ячейка не смещалась, то Вам следует немного изменить первоначальную формулу написав ее так:

Сделайте так и ячейка с «долларами» (то есть $B$2 в нашем случае) не будет меняться.

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

Использовать F4 надо так: мышкой выделяете (в формуле) нужный адрес (в нашем примере — это B2) и нажимаете 1 раз F4 — сразу и перед буквой и перед цифрой появится значок доллара $ (он, кстати, в Excel означает, т.н. абсолютный адрес — то есть такой адрес, который не будет изменяться при копировании формулы)

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

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

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

Первый самый простой.

Пример. Вот данные, которые получены в столбце «D» суммированием данных из столбцов «В» и «С», разделив результат на данные из столбца «J», чтобы преобразовать в евро.

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

1) И вот какое решение существует — преобразовать относительные ссылки в абсолютные.

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

2) Тогда можно попробовать другой способ — «дезактивировать» формулы, то есть сделать так, чтобы в документе «Excel» не воспринимались формулы — «формулами», а воспринимались, словно это обычный текст.

Тогда знак «=» на какое-то время следует заменить любым другим символом, например, решеткой — «#» или парой амперсандов — «&&».

Воспользуемся горячими клавишами.

3) Еще способ — воспользоваться копированием с применением блокнота, то есть из документа копировать в блокнот, оттуда переносить данные в нужный диапазон.

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

Дальше надо скопировать диапазон из Exel в блокнот —

4) Четвертый способ подходит тем, кто постоянно выполняет копирование, перенося каждый раз. Можно воспользоваться макросом — здесь все проще всего, все задано заранее, надо только создать макрос.

Чтобы работать с макросами, надо перейти на вкладку «Разработчик» или применить сочетание горячих клавишь Alt+F8.

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

В авторском ролике Николай Павлов, специалист по Exel, расскажет еще более подробно и наглядно.

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

Самое простейшее — сделать так, чтобы «Excel» вместо формулы видел текст. Для этого знак «=» надо заменить любым знаком или символом. Например, на «$» или «#». Чтобы это сделать, используют горячие клавиши. Выделили диапазон, нажали сочетание горячих клавиш, чтобы вызвать окно «найти и заменить», там вместо «=» подставили другой символ. Сделали копирование, а потом точно так же обратно вместо символа вернули знак равенства.

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

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

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

Источник:
http://www.bolshoyvopros.ru/questions/1371527-excel-kak-sdelat-chtoby-jachejki-v-formule-ne-menjalis-pri-kopirovanii.html

Защита ячеек в Excel от изменения, редактирования и ввода ошибочных данных

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

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

Читайте также  Как в Excel сделать заливку чередующихся строк с помощью условного форматирования - Трюки и приемы в Microsoft Excel

Защита выделенного диапазона от изменения

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

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

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

В следующем диалоговом окне переходим на вкладку «Защита» и снимаем галочку с пункта «Защищаемая ячейка» . Нажмите «ОК» .

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

После этого поставим ограничения для изменений. Для примера давайте запретим редактирование блоков, которые находятся в диапазоне B2:D7 . Выделяем указанный диапазон, кликаем по нему правой кнопкой мыши и выбираем из меню «Формат ячеек» . Дальше перейдите на вкладку «Защита» и поставьте галочку в поле «Защищаемая…» . Нажмите «ОК» .

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

После этого, любой пользователь сможет работать с информацией на странице. В примере введены пятерки в Е4 . Но при попытке изменить текст или числа в диапазоне В2:D7 , появится сообщение, что ячейки защищены.

Ставим пароль

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

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

Сделаем так, чтобы другие пользователи могли редактировать всё на листе, кроме диапазона B2:D7 . А Вы, зная пароль, могли редактировать и блоки в B2:D7 .

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

Теперь нужно выделить диапазон, для которого будет установлен пароль, в примере это B2:D7 . Потом опять зайдите «Формат ячеек» и поставьте галочку в поле «Защищаемая…» .

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

Затем переходим на вкладку «Рецензирование» и нажимаем кнопочку «Разрешить изменение диапазонов» . Откроется соответствующее диалоговое окно. Нажмите в нем кнопочку «Создать» .

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

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

Теперь нужно установить пароль для листа. На вкладке «Рецензирование» нажимаем кнопочку «Защитить лист» . Введите пароль и отметьте галочками, что можно делать пользователям. Нажмите «ОК» и подтвердите пароль.

Проверяем, как работает защита ячеек. В Е5 введем шестерки. Если попробовать удалить значение из D5 , появится окно с запросом пароля. Введя пароль, можно будет изменить значение в ячейке.

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

Защищаем блоки от неверных данных

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

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

Выделяем нужный диапазон ячеек таблицы – С3:С7 , переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .

В следующем диалоговом окне на вкладке «Параметры» в поле «Тип…» выберите из списка «Целое число» . В поле «Минимум» введем «1» , в поле «Максимум» – «11» .

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

На вкладке «Сообщение об ошибке» введем сообщение, которое будет появляться, если пользователь попробует ввести неправильную информацию. Нажмите «ОК» .

Теперь если выделить что-то из диапазона С3:С7 , рядом будет высвечиваться подсказка. В примере при попытке написать в С6 «15» , появилось сообщение об ошибке, с тем текстом, который мы вводили.

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

Источник:
http://comp-profi.com/zashita-yacheek-v-excel-ot-izmeneniya-redaktirovaniya-i-vvoda-oshibochnyh-dannyh/

Автоматическое преобразование формата ячейки в EXCEL при вводе ТЕКСТовых данных (Часть 1)

25 апреля 2013 г.

При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты или числа, то EXCEL производит соответствующее преобразование и форматирование. Преобразование производится «на лету» после нажатия клавиши ENTER . Часто текстовые данные действительно имеют формат дат (1-05, 2.12) или чисел (000123456), но на самом деле ими не являются. В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

При вводе текстовых данных определенного вида, например: 1-05, 2.12, 1 e 2, 00012456 , EXCEL автоматически меняет формат ячейки и преобразовывает текст в другой, как ему кажется, подходящий тип данных. Это приводит к тому, что 1-05 и 2.12 будут преобразованы в даты ( 01.май и 02.дек ), а 1 e 2 и 000123456 в числа (в 1,00 E +02 , т.е. 100 и в 123456 ).

Допустим, мы вводим текст 1-05 (пусть это будет артикул товара) и хотим, чтобы EXCEL сохранил в ячейке именно текст, а не что-либо другое.

Что же происходит при вводе значения в ячейку? Если ячейка имеет формат Общий (формат ячейки по умолчанию), EXCEL преобразует текст 1-05 в дату 01.май : формат ячейки изменяется с Общий на формат Дата (точнее все форматы /ДД.МММ). Если мы вернем ячейке формат Общий , то в ячейке увидим число 40299 (если сейчас 2010 год), что соответствует 01.05.2010 . Вернуть исходное значение 1-05 простым преобразованием формата уже не представляется возможным — EXCEL интерпретировал 1-05 , как дату 01.05.2010 и вставил в ячейку соответствующее ей число 40299 .

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

Есть и другой способ. Чтобы EXCEL автоматически при вводе текстовых данных назначал ячейкам текстовый формат, перед вводимым текстом можно ввести знак апострофа (‘), т.е. в нашем случае необходимо ввести ‘1-05 (давайте введем его на новом листе в ячейку А1 ). Почему именно апостроф? Есть две причины: этот символ не отображается в ячейке (если он введен первым) и он игнорируется формулами. Например, после ввода формулы =ЛЕВСИМВ(A1;4) получим 1-05 , а не ‘1-0 , как вроде следовало бы ожидать.

Использование апострофа — не единственный способ отмены автоматического преобразования формата при вводе значений. Можно вместо апострофа ввести перед словом пробел или после слова точку. Например, если нужно ввести 2.3 (что превращается 02.мар ), то вводим 2.3. (после 3 стоит точка).

Другой пример об автоматическом преобразовании формата читайте в Части 2 .

СОВЕТ: Неправильный формат значения – это частый тип ошибки, к тому же, который трудно найти. Подсказкой может служить выравнивание значения в ячейке: если значение выровнено по правой стороне, то это число, а если по левой, то текст. Если ранее было сделано другое выравнивание в ячейке, то через Формат ячеек / Вкладка Выравнивание / По горизонтали установите параметр « По Значению ».

Читайте также  Как сделать текст в одну строку в excel?

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

Источник:
http://excel2.ru/articles/avtomaticheskoe-preobrazovanie-formata-yacheyki-v-ms-excel-pri-vvode-tekstovyh-dannyh-chast-1

Как закрепить ячейку в формуле Excel

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

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

Что такое ссылка Excel

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

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

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

Для того, чтобы сохранить адреса и колонки, и ряда, необходимо выполнить следующие шаги:

  1. Нажать по ячейке, содержащей формулу.
  2. Нажать по строке формул по той ячейке, которая нам нужна.
  3. Нажать F4.

Как следствие, ссылка ячейки изменится на абсолютную. Ее можно будет узнать по характерному знаку доллара. Например, если нажать на ячейку B2, а потом нажать на F4, то ссылка обретет следующий вид: $B$2.

1 2

Что означает знак доллара перед частью адреса на ячейку?

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

Этот способ почти такой же самый, как и прошлый, только нажать нужно F4 два раза. например, если у нас была ячейка B2, то после этого она станет B$2. Простыми словами, таким способом у нас получилось зафиксировать строку. При этом буква столбца будет изменяться.

3

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

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

4

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

5

Закрепление ячеек для большого диапазона

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

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

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

  1. Ячейки.
  2. Макросы.
  3. Функции разных типов.
  4. Ссылки и массивы.

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

  1. Выделить диапазон.
  2. Открыть вкладку VBA-Excel, которая появится после установки.
  3. Открыть меню «Функции», где располагается опция «Закрепить формулы».

6

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

    7

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

    Можно, конечно, в данном примере попробовать протянуть с помощью маркера автозаполнения формулу вниз, но в таком случае ячейки будут автоматически изменены. Так, в ячейке D3 будет другая формула, где цифры будут заменены, соответственно, на 3. Далее по схеме – D4 – формула обретет вид =B4*C4, D5 – аналогично, но с цифрой 5 и так далее.

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

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

    Вот, как оно выглядит в нашем примере.

    8

    Если мы аналогично предыдущему варианту попробуем прописать формулу, то потерпим поражение. Точно так же формула изменится на соответствующую. В нашем примере она будет такой: =E3*B8 . Отсюда мы можем увидетЬ. что первая часть формулы превратилась в E3, и мы ставим перед собой эту задачу, а вот изменение второй части формулы на B8 нам ни к чему. Поэтому нам нужно превратить ссылку на абсолютную. Можно сделать это и без нажатия на клавишу F4, просто поставив знак доллара.

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

    Сама формула будет выглядеть следующим образом:

    Внимание! Мы указали два знака доллара. Таким образом мы показываем программе, что нужно фиксировать как столбец, так и строку.

    Ссылки на ячейку в макросах

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

    Для начала нужно понять, что ключевое понятие макроса – объекты, которые могут содержать в себе другие объекты. За электронную книгу (то есть, документ) отвечает объект Workbooks. В его состав входит объект Sheets, который являет собой совокупность всех листов открытого документа.

    Соответственно, ячейки – это объект Cells. Он содержит все ячейки определенного листа.

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

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

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Cells(5, 3)

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Cells(5, “C”)

    Также доступ к ячейке можно получить с помощью объекта Range. Вообще, он предназначен для того, чтобы давать ссылку на диапазон (элементы которого, к слову, также могут быть абсолютными или относительными), но можно дать просто название ячейки, в таком же формате, как в документе Excel.

    В этом случае строчка будет выглядеть следующим образом.

    Workbooks(“Книга2.xlsm”).Sheets(“Лист2”).Range(“C5”)

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

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

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

    Источник:
    http://office-guru.ru/excel/formuly-funkcii/kak-zakrepit-yachejku-v-formule-excel.html

    12 наиболее распространённых проблем с Excel и способы их решения

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

    Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

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

    Вы не даёте заголовки столбцам таблиц

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

    Пустые столбцы и строки внутри ваших таблиц

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

    На одном листе располагается несколько таблиц

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

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

    Данные одного типа искусственно располагаются в разных столбцах

    Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

    Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.

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

    Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

    Рекомендуемый формат таблицы выглядит так:

    Разнесение информации по разным листам книги «для удобства»

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

    Информация в комментариях

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

    Бардак с форматированием

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

    1. Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
    2. Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
    3. Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.

    Объединение ячеек

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

    Объединение текста и чисел в одной ячейке

    Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

    Числа в виде текста в ячейке

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

    Если ваша таблица будет презентоваться через LCD проектор

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

    Страничный режим листа в Excel

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

    Ещё больше полезной информации про Excel можно узнать на сайте Дениса.

    Источник:
    http://lifehacker.ru/hit-parad-problem-s-excel/