Elettracompany.com

Компьютерный справочник
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel не равно пусто

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа РедактированиеОчистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

  1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
  2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
    =ЕСЛИ( A1 =1;10;»»)
    =IF(A1=1,10,»»)
    в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
    Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

  1. создаем в книге новый стандартный модуль: Alt+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

Excel говорит мне, что мои пустые ячейки не пустые

поэтому в excel я пытаюсь избавиться от пустых ячеек между моими ячейками, в которых есть информация, используя F5 для поиска пустых ячеек, затем Ctrl + — для их удаления и сдвига ячеек вверх. Но когда я пытаюсь это сделать, он говорит мне, что «клеток не найдено».

Я заметил, что если я выбираю свои «пустые» ячейки, Excel все еще считает их: что странно. Но если я нажму Delete на эти выбранные ячейки, счетчик исчезнет, а затем я могу перейти F5, пробелы, Ctrl + — и Сдвиньте клетки вверх, и это сработает.

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

заранее спасибо за вашу помощь! 🙂

15 ответов

откровение: некоторые пустые ячейки на самом деле не пустой! Как я покажу, ячейки могут иметь пробелы, новые строки и true empty:

чтобы найти эти ячейки быстро вы можете сделать несколько вещей.

  1. на =CODE(A1) формула вернет значение#! если клетка действительно пуста, в противном случае вернется. Это число -номер ASCII в =CHAR(32) .
  2. если вы выделите ячейку и щелкните в строке формул и используйте курсор, чтобы выбрать все.

удаление этих:

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

  1. пресс ctrl + h чтобы открыть find и replace.
  2. введите одно место в найти, оставить заменить на пустой и убедитесь, что у вас есть матч всей ячейки содержание галочкой в опциях.
  3. пресс заменить все.

если у вас newlines это сложнее и требует VBA:

    щелкните правой кнопкой мыши на вкладке листа > Просмотр кода.

затем введите следующий код. Помните Chr(10) новая строка заменяет это только по мере необходимости, например » » & Char(10) это пробел и новая строка:

Теперь запустите ваш код нажатие Ф5 .

после файл поставляется: выберите диапазон интересов для повышения производительности, затем выполните следующие действия:

простой способ выбрать и очистить эти пустые ячейки, чтобы сделать их пустыми:

  1. пресс ctrl + a или предварительно выберите свой диапазон
  2. пресс ctrl + f
  3. оставить найти пустой и выберите матч всего содержимого ячейки.
  4. нажмите найти все
  5. пресс ctrl + a выбрать все пустые ячейки нашли
  6. закрыть поиск диалоговое окно
  7. пресс backspace или удалить

Это сработало для меня:

  1. CTR-H для поиска и замены
  2. оставьте «найти что» пустой
  3. изменить «заменить на» на уникальный текст, то, что вы
    положительный не будет найден в другой ячейке (я использовал ‘xx’)
  4. клик «Заменить Все»
  5. скопируйте уникальный текст на Шаге 3, чтобы найти то, что’
  6. удалить уникальный текст в «заменить на»
  7. нажмите «заменить все»

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

мне удалось очистить их

  1. выбор столбцов / строк, которые я хотел очистить, а затем делать
  2. «найти» [без текста] и» заменить » [слово выбора].
  3. затем я сделал «найти» [слово выбора] и» заменить » на [нет текста].

он избавился от всех скрытых / фантомных персонажей в тех ячейки. Может, это сработает?

все, это довольно просто. Я пытался сделать то же самое, и это то, что сработало для меня в VBA

С уважением, Ананд Ланка

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

нашел другой способ. Установите автофильтр для все столбцы (важно или вы будете смещать данные), выбрав строку заголовка > вкладка «данные» > сортировка и фильтр — «фильтр». Используйте раскрывающийся список в первом столбце данных, снимите флажок «выбрать все» и выберите только «(пробелы) » > [OK]. Выделите строки (Теперь все вместе) > щелкните правой кнопкой мыши > «удалить строку». Вернитесь к раскрывающемуся меню > ‘Select all’. Престо:)

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

Я выбрал весь столбец, выбрал Find & Replace, нашел ячейки ни с чем и заменил на 0, затем снова запустил find и replace, найдя ячейки с 0 и заменив их на «».

Это решило мою проблему и позволило мне искать пустые ячейки (F5, специальные, пробелы) и удалять строки, которые были пусты. БУМ.

может не работать для каждого приложения, но это решило мою проблему.

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

Это работает с цифрами.

Если ваш диапазон O8:O20, то в соседнем пустом диапазоне (например, T8: T20) введите =O8/1 и заполните. Это даст вам результат #VALUE для «пустых» ячеек, и ваш исходный номер останется прежним.

затем с выбранным диапазоном T8: 20 (CTL -*, если это еще не так) нажмите F5 и выберите специальный. В специальном диалоге выберите ошибки и нажмите кнопку ОК. Это отменит выбор фактических номеров, оставив только выбранные ячейки #VALUE. Удалите их, и у вас будут фактические пустые ячейки. Скопируйте T8:T20 и вставьте обратно через O8: O20.

по существу, поскольку пустые ячейки не работают, вам нужно преобразовать «пустые» ячейки во что-то, что может зацепиться за специальное. Любое действие, которое будет преобразовано в #VALUE, будет работать, и другие типы «ошибок» также должны поддерживаться.

мой метод похож на предложение Курта выше о сохранении его как файла с разделителями табуляции и повторном импорте. Предполагается, что данные имеют только значения без формул. Это, вероятно, хорошее предположение, потому что проблема «плохих» пробелов вызвана путаницей между пробелами и нулями-обычно в данных, импортированных из какого-то другого места, — поэтому не должно быть никаких формул. Мой метод —разобрать на месте — очень похоже на сохранение в виде текстового файла и повторный импорт, но вы можете сделать это без закрытия и повторного открытия файла. Он находится в разделе Данные > текст в Столбцы > разделители > удалить все символы синтаксического анализа (также можно выбрать текст, если хотите) > готово. Это должно заставить Excel повторно распознавать ваши данные с нуля или из текста и распознавать пробелы как действительно пустые. Вы можете автоматизировать это в подпрограмме:

вы также можете включить эту прокомментированную строку, чтобы эта подпрограмма выполнялась «в фоновом режиме». Для этой подпрограммы улучшается производительность только немного (для других это действительно может помочь). Имя-F2Enter, потому что исходный ручной метод для исправления этой проблемы «пробелов» — заставить Excel распознать формулу, нажав F2 и Enter.

вот как я исправил эту проблему без какого-либо кодирования.

  1. выберите весь столбец, из которого я хотел удалить «пустые» ячейки.
  2. щелкните вкладку Условное форматирование вверху.
  3. Выберите «Новое Правило».
  4. нажать «форматировать только ячейки, которые содержат».
  5. изменить «между» На «равно».
  6. нажмите на поле рядом с полем» равно».
  7. нажмите одну из проблемных» пустых » ячеек.
  8. клик кнопка формат.
  9. выберите случайный цвет для заполнения коробки.
  10. нажмите «OK».
  11. Это должно изменить все проблемные» пустые » ячейки на цвет, который вы выбрали. Теперь щелкните правой кнопкой мыши одну из цветных ячеек и перейдите в раздел «сортировать» и «поместить выбранный цвет ячейки сверху».
  12. это поставит все проблемные ячейки в верхней части столбца, и теперь все ваши другие ячейки останутся в исходном порядке, в котором вы их поместите. Теперь вы можете выбрать все проблемные ячейки в одной группе и нажмите кнопку Удалить ячейку сверху, чтобы избавиться от них.

самым простым решением для меня было:

1) Выберите диапазон и скопируйте его (ctrl+c)

2)Создайте новый текстовый файл (в любом месте, он будет удален в ближайшее время), откройте текстовый файл, а затем вставьте в excel информацию (ctrl+v)

3) Теперь, когда информация в Excel находится в текстовом файле, выполните select all в текстовом файле (ctrl+a), а затем скопируйте (ctrl+c)

4) перейдите к началу исходного диапазона на шаге 1 и вставьте его старая информация из копии на Шаге 3.

готово! Больше никаких фальшивых заготовок! (теперь вы можете удалить временный текстовый файл)

Goto — > Special — >blanks не любит объединенные ячейки. Попробовать unmerging клеток выше диапазона, в котором вы хотите выбрать то, болванками попробовать снова.

У меня была аналогичная проблема с получением формулы COUNTA для подсчета непустых ячеек, она считала все из них (даже пустые как непустые), я попытался =CODE (), но у них не было пробелов или новых строк.

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

Функция ЕСЛИ: производим действия в зависимости от условия

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

В примере ниже столбец F содержит даты завершения закупок шоколада.

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

Формула в ячейке F3:

Как работает эта формула?

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек, значит, пустых ячеек нет и можно производить вычисление. Если равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.

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

Давайте рассмотрим и другие варианты. В ячейке F6 записана большая формула —

Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае — производим вычисления.

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

Рассмотрим теперь более универсальные решения.

В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

И, наконец, еще одна формула Excel, которая позволит производить расчет только при наличии непустых ячеек.

Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.

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

Итак, перед нами уже знакомая формула

Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

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

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.

Найти первое непустое значение в строке

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).

Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: <ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА>.

Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: <0;0;1;0;0;0;0;0;0>.

Итак, фрагмент формулы 1-ЕПУСТО(С2:К2) возвращает массив <0;0;1;0;0;0;0;0;0>. Это немного странно, так как от такого фрагмента Excel ожидает, что вернется одно значение. Странно, но не смертельно. Функция ИНДЕКС также обычно возвращает одно значение. Но вот, что написано в Справке Excel: Если указать в качестве аргумента номер_строки или номер_столбца значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца.

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

Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1;) значение ноль, мы получим массив <0;0;1;0;0;0;0;0;0>.

Функция ПОИСКПОЗ выполняет поиск искомого значения в одномерном массиве и возвращает относительную позицию первого найденного совпадения. Формула =ПОИСКПОЗ(1,МАССИВ,0) просит Excel найти номер ячейки в МАССИВЕ, которая содержит первую встретившуюся единицу. Функция ПОИСКПОЗ определяет в каком столбце содержится первая непустая ячейка. Когда вы просите ПОИСКПОЗ найти первую 1 в массиве <0;0;1;0;0;0;0;0;0>, она возвращает 3.

Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1;<0;0;1;0;0;0;0;0;0>;0) и возвращает результат 3.

В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).

Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

Дополнительные сведения: если все ячейки пустые, то формула возвращает ошибку #Н/Д.

Альтернативные стратегии: когда вы из единицы вычитаете значение ЕПУСТО, вы преобразуете логические значения ИСТИНА/ЛОЖЬ в числовые 1/0. Вы могли бы пропустить этот шаг, но тогда вам придется искать ЛОЖЬ в качестве первого аргумента функция ПОИСКПОЗ: =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)).

Excel. Заполняем пустоту.

Продолжаю делиться с вами тем, что знаю и люблю.

Сегодня вашему вниманию предлагаю небольшой прием, совсем не сложный, но полезный.

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

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

Если вам знакома такая проблема, то дальше вам может быть интересно:)

Как заполнить пустоту

1. Выделяем всю таблицу

(например так: выделяем любую ячейку в рамках таблицы и нажимаем Ctrl+*)

2. Нажимаем F5 -> Выделить-> Пустые ячейки

4. Стрелочка вверх

6. Получившийся результат вставить значениями

Спасибо, что дочитали. Буду рада, если вам пригодится.

P.S. @eakwarp , вам в прошлый раз не понравилось, что пост про excel и я пообещала котика. Котика в доме не нашлось, держите собаку-Чубаку.

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

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

Тоже нихера не понял. хоть с экселем на ты, но не в засос.

Крутая фишка, с excel давно на «ты», а не знал.

Единственное надо не ctrl+*, а ctrl+shift+*.

А вообще, чтобы выделить таблицу целиком всегда пользовался ctrl+A. Удобнее гораздо.

Ctrl +shift +* это из-за того, что звездочка на восьмёрке. Подразумевалось так 🙂

А ctrl +a вообще интересное сочетание. От количества нажатий зависит результат. Но про него чаще слышали, чем про звёздочку.

но сочетание ctrl+num* совсем неудобное, имхо, поскольку требует обратить внимание на клавиатуру, чтобы найти пальцем звезду.

А ctrl+A делается на автомате, не отвлекаясь от основного действа. 🙂

Поэтому ctrl+A, S, D, Z, C, 1, space — самые любимые сочетания

Есть еще пара способов.
Первый — через power query, там есть функция «заполнить вниз» и «заполнить вверх». Второй — замена пустых ячеек на относительную формулу =R[-1]C (главное не забыть потом сохранить все замененное как значения)

Котик чубака — отлично!

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

НА этот случай я пользуюсь макросом, который сразу формулы переводит в значения

На самом деле, очень удобный прием. пользуюсь часто. вместо F5 нажимаю Ctrl+G.

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

Интересный лайфхак. Хотя только сейчас понял, что в эксель захожу только по праздникам. Либра и гугл доки тащат)

Поваренная книга Экселиста #0 — Пару слов об Экселе

Для начала — рад видеть, что моих любимых и обожаемых подписчиков стало 21.. Шутки про «За 2 дня на Пикабу у меня — очко» оставлю себе))) В первую лавочку пишу для вас, ну и отдельная вам благодарочка за то, что вам это интересно.

Так как я такой же логичный, как стол-жираф-48, начну с того, с чего надо было начать изначально — а зачем, в принципе, Эксель то нужен, и что с ним можно делать (кроме Зиночка_Счет_В_Экселе_Сделает). Тут уже предвижу холивар, прям чувствую, потому сразу подкину на вентилятор.
Основные задачи в классическом понимании это НЕ ХРАНЕНИЕ данных, а их обработка. То есть, если рассматривать классическую MVC-модель — это контроллер. Потому что для представления есть бумага и принтер.

То, что мы храним в Экселе данные — ну да, нам так проще. Но по факту любая СУБД Эксель уничтожит по скорости работы непосредственно с данными. Но, чтобы что-то похожее на обработку организовать на уровне работы с СУБД — придется уже городить несколько этажей SQL запросов.

Потому данные выгружают в Эксель, а там уже и «понеслась звезда по кочкам».

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

Отсюда, как мне кажется, возникает одна такая ошибочка — Эксель это не способ сделать «правильно», это способ сделать БЫСТРО и РАБОЧЕ. 90% расчетных файлов в экселе понимает разработчик и Майкл Джексон, ситуация ровно такая же, как и с ремонтом ( «вот пусть тот криворукий исправляет пол, без пола лампочку не повесить»).

Далее чуть объяснений и примеров.

В последнее время очень «модно и молодежно» вводить машинный анализ в прогнозирование возможного поведения клиента (про b2c сегмент говорим). То есть, допустим, на основе покупок клиента делать прогноз (не статистику собираем, а Вангу косплеим) совершения им следующей покупки (как даты, так и состава комплексной покупки) и о возможных similar к ней. И уже на основании этого делать какие-либо выводы и офферы разные предлагать (ну там сырный соус к покупке, ну вы поняли да?)) ).

Реализация этого в экселе занимает от часов до дня, реализация этого в CRM занимает от недель до «на третьи сутки после второго пришествия». Причем реализация на уровне CRM нифига не будет работать без прототипа, реализованного или на бумажке (в виде ТЗ) или в виде костылявых обработок в Экселе. Ведь программист, про кодера даже не будем, не обязан и не будет представлять себе вашу бизнес-логику, ему до нее как Ильичу до лампочки. Да и по поводу ТЗ крайне метко выразился мой препод в институте — то, что вы написали в ТЗ не будет работать так, как это вы написали, а будет так, как прочел (понял) разработчик. Потому мы делает что? Правильно — бьемся челом об сруб светлицы что-то невообразимо-кривое в Экселе, но при перемножении лося и порося получаем искомые 63,3%. Что и служит уже как и вашей (дядиной) прибыли, так и методом контроля разработки. То есть вы берете чистые данные, которые хранятся НЕ в Экселе, а в СУБД, и уже их вьювите и контролите.

Ну и, чтобы разбавить это «много-букофф-ниочем» — боевой пример.

Делаем простейшую экспертную систему в Экселе (без регистации и смс).

Найдите глазами столбец «% win». В зависимости от нее — строка перекрашивается в другой цвет, меняется шрифт, ну и чем значение больше — тем больше вероятность покупки. Цифры относительны, получены крайне простым способом — телефон в руки и обзвон разных групп клиентов. Вычитаем оттуда погрешность «на идиота» и вуаля.

А теперь понимаем как это сделать.

Конкретно этот пример — фитнес. Далее мы ищем данные. Конкретно в моем случае искалось так:

— Таблица с клиентами — CRM (оттуда выгрузка в эксель, листы 62_кк)

— СКУД — с сервера СКУД (да, в CRM она интегрирована криво + карты можно и «забыть» внести в саму CRM, потому что некогда админам) (лист «посещения_приведенные», и «пос_»)

— Статистика звонков — с телефонии (потому что оттуда она тупо информативнее, и мне не интересно мнение менеджера о клиенте. Если менеджер говорил более минуты — значит клиенту интересно, просто ему предлагали не то, что ему нужно). (лист 4)

— Дополнительные покупки — CRM (лист «Этот»)

— Экспертная оценка — эмпирически-добытые факты. (лист _tech)
(часть данных потер, ибо прайваси-все-дела + обработку специально взял аж с августа, с того времени их было еще версий 20)

Теперь включаем чем думать:

Клиент ходит постоянно -> его все устраивает

Клиент купил карту за сумму N -> сумма его устраивает (то есть предложи столько же или меньше, но не больше)

Клиент покупает что-либо еще -> деньги у клиента не кончились

Карта заканчивается в течение квартала -> купит сейчас, если пункты 1-3 соблюдены.

Клиент женщина 30+, на дворе август, клиент не ходит с июня -> предложить карту и детские занятия со скидкой

Клиенту не звонили с сервисными звонками в течение месяца и пункты 1-3 соблюдены -> звонить в ПЕРВУЮ очередь (не задрочен)

Клиент ходил, сейчас не ходит, возраст до 21 -> звонить и предлагать рассрочку, свалил на лето.

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

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

Коэфы задаем отдельно, даже цвета для выделения строк — тоже задаем через «техническую» вкладку.

И да, это тоже можно сделать по другому. А можно и не делать, ведь зачем нужно что-то делать, когда можно просто обсудить все в комментах, неправда ли?))

Читать еще:  Как скрыть неиспользуемые ячейки в excel
Ссылка на основную публикацию
Adblock
detector