Great question! I’ve got you covered with both R and Excel VBA solutions—since you’re new to VBA, I’ll break that approach down extra clearly so you can follow along easily.

You’re right that fillMergedCells = FALSE just repeats the original value, but we can add a few extra steps to split the value evenly across the unmerged cells. Here’s a step-by-step implementation using the openxlsx package:

1. Load your workbook and get merged cell details
   First, we’ll read the data without filling merged cells (so only the first cell in each merged range has the value, others are NA), then grab the merged cell ranges to calculate the split value.

   library(openxlsx)
   
   # Replace with your file path
   wb <- loadWorkbook("your_excel_file.xlsx")
   # Read the sheet - fillMergedCells = FALSE keeps NA in non-first merged cells
   df <- read.xlsx(wb, sheet = 1, fillMergedCells = FALSE)
   # Get all merged cell ranges on the sheet
   merged_ranges <- getMergedCells(wb, sheet = 1)
   

2. Loop through merged ranges to split values
   For each merged group, we’ll calculate the split value (original value ÷ number of cells in the range) and fill every cell in the range with that value:

   for (range in merged_ranges) {
     # Convert Excel-style range (e.g., "B2:B5") to row/column positions
     cell_coords <- strsplit(range, ":")[[1]]
     start <- convertFromExcelRef(cell_coords[1])
     end <- convertFromExcelRef(cell_coords[2])
     
     # Grab the original value (only exists in the first cell of the merged range)
     original_val <- df[start$row, start$col]
     # Calculate how many cells are in the merged range
     total_cells <- (end$row - start$row + 1) * (end$col - start$col + 1)
     # Compute the split value
     split_val <- original_val / total_cells
     
     # Fill all cells in the range with the split value
     df[start$row:end$row, start$col:end$col] <- split_val
   }
   
   # Your dataframe now has evenly split values instead of repeats/NA!
   

Note: This assumes your merged cells contain numeric values. If you have text-based merged cells, add a check like if(is.numeric(original_val)) to skip those.

Don’t stress about not knowing VBA—this macro is simple, and I’ll walk you through exactly how to use it:

1. Prepare your Excel file
   Open the workbook with the merged cells you want to process, and navigate to the correct sheet.

2. Open the VBA Editor
   Press Alt + F11 on your keyboard to open the VBA Editor window.

3. Insert a new module
   In the left-hand "Project Explorer" pane, right-click your workbook name > Insert > Module.

4. Paste the macro code
   Copy and paste this code into the blank module that opens:

   Sub SplitMergedCellsEqually()
       Dim mergedArea As Range
       Dim totalCells As Integer
       Dim originalValue As Double
       Dim splitValue As Double
       
       ' Loop through every merged cell group in the active sheet
       For Each mergedArea In ActiveSheet.UsedRange.MergeAreas
           ' Only process numeric values (skip text merged cells)
           If IsNumeric(mergedArea.Cells(1).Value) Then
               ' Count how many cells are in the merged group
               totalCells = mergedArea.Cells.Count
               ' Grab the original value from the first cell
               originalValue = mergedArea.Cells(1).Value
               ' Calculate the even split value
               splitValue = originalValue / totalCells
               
               ' Unmerge the cells
               mergedArea.UnMerge
               ' Fill all cells with the split value
               mergedArea.Value = splitValue
           End If
       Next mergedArea
       
       ' Popup to confirm completion
       MsgBox "Merged cells split and filled evenly!", vbInformation
   End Sub
   

5. Run the macro

   • Click anywhere inside the code you pasted, then press F5 to run it.
   • Or, go back to Excel, press Alt + F8, select SplitMergedCellsEqually, then click Run.

Pro tip: Always save a copy of your original Excel file before running macros—just to be safe!

内容的提问来源于stack exchange，提问作者geoffest